Monitoring the size of Transaction Log files is one of the important tasks for a SQL Server DBA. I regularly monitor my database log files that it do not grow tremendously in size and potentially run out of space. Below script will give the list of Databases and their Transaction Log files size in MB in the descending order.
SELECT INSTANCE_NAME AS [Database],
(CNTR_VALUE/1000) AS Size_In_MB FROM MASTER.dbo.SYSPERFINFO
WHERE COUNTER_NAME LIKE '%Log File(s) Size (KB)%'
AND INSTANCE_NAME NOT IN ('_TOTAL','mssqlsystemresuorce')
ORDER BY Size_In_MB DESC
SELECT INSTANCE_NAME AS [Database],
(CNTR_VALUE/1000) AS Size_In_MB FROM MASTER.dbo.SYSPERFINFO
WHERE COUNTER_NAME LIKE '%Log File(s) Size (KB)%'
AND INSTANCE_NAME NOT IN ('_TOTAL','mssqlsystemresuorce')
ORDER BY Size_In_MB DESC
No comments:
Post a Comment