Which TLogs are causing large block change in SAN snap? The environment is SQL Server 2014 Enterprise Edition, NetApp storage managed by CommVault Simpana. We have 100 dbs setup on an instance, all TLogs are on the same Volume. 

The SAN Admin has setup TLog backups 4 times a day with a full snap of the TLog backup volume once a night.  The retention period is 14 days so at any time the TLog backup volume has 14 snaps of the TLogs.

Recently the TLog backup volume came close to running out of space because the full snaps were increasing at a rapid pace. We attributed this to end-of-year data processing, causing a lot of block change. This up-tick wasn't expected to last and it didn't.

The SAN Admin said he has no way of knowing which TLog files were responsible for the increase in block change and in turn the larger snaps. 

I know there is a disconnect between files and blocks, but I would think you could connect the dots through Simpana, NetApp or PowerShell to find out which TLogs were causing the large block change.

Any suggestions would be appreciated.

Thanks
Good news! You don't have to query Simpana, NetApp, or PowerShell to figure out where the log file sizes have been changing.

Just query the msdb.dbo.backupset table - it tracks database name, backup date/time, and backup size. You can trend this over time. I like charting this with Excel or SSRS to show volume trends, and then I can drill down into detail to see exactly which backup is causing it.

The below query displays full backup sizes in a pivot - the 0 column is current month, -1 is last month, -2 is two months ago, etc. Note that I'm filtering for file_type = D - that's full backups. You can switch out different filters for differential or log backups if you like.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE @startDate DATETIME; 
    SET @startDate = GETDATE(); 
    SELECT PVT.DatabaseName ,
        PVT.[0] ,
        PVT.[-1] ,
        PVT.[-2] ,
        PVT.[-3] ,
        PVT.[-4] ,
        PVT.[-5] ,
        PVT.[-6] ,
        PVT.[-7] ,
        PVT.[-8] ,
        PVT.[-9] ,
        PVT.[-10] ,
        PVT.[-11] ,
        PVT.[-12]
    FROM ( SELECT BS.database_name AS DatabaseName ,
                DATEDIFF(mm, @startDate, BS.backup_start_date) AS MonthsAgo ,
                CONVERT(NUMERIC(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
            FROM msdb.dbo.backupset AS BS 
                INNER JOIN msdb.dbo.backupfile AS BF
                    ON BS.backup_set_id = BF.backup_set_id
            WHERE NOT BS.database_name IN ( 'master', 'msdb', 'model', 'tempdb' )
                AND BF.[file_type] = 'D'
                AND BS.backup_start_date BETWEEN DATEADD(yy, -1, @startDate)
                                         AND     @startDate
            GROUP BY BS.database_name ,
                DATEDIFF(mm, @startDate, BS.backup_start_date)
         ) AS BCKSTAT PIVOT ( SUM(BCKSTAT.AvgSizeMB) FOR BCKSTAT.MonthsAgo IN ( [0], [-1], [-2], [-3], [-4], [-5], [-6], [-7], [-8], [-9], [-10], [-11], [-12] ) ) AS PVT
    ORDER BY PVT.DatabaseName;
GO