Search This Blog

SQL SERVER MONITORING: SQL Transaction log Size


 Monitoring the size of Transaction Log files is one of those important tasks for a SQL Server Database Administrator. I monitor regularly in order to ensure that my database log files do not grow tremendously in size and potentially run out of space. The script in this article 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’,'mssqlsystemresource’)

ORDER BY Size_In_MB DESC




DBCC SqlPerf(logspace) ;



DBCC_SqlPerf3



declare @LogSpace table
(
 DB varchar(255),
 LogSizeMB int,
 PercentUsed float,
 Status int
);
insert into @LogSpace
execute('DBCC SqlPerf(logspace)');
SELECT *
 FROM @LogSpace
 ORDER By LogSizeMB desc;

DBCC_SqlPerf4