T-SQL to get SQL Transaction Log Sizes

When dealing with the slew of SharePoint MS SQL databases that can be found in a typical install, it’s a bit of a time suck to check transaction log sizes manually. I put together the following T-SQL script to quickly show which transaction logs out of all the logs in the particular SQL instance, are above 299 MB in size. Adjust that threshold to your tastes.

declare @LogSpace table
(
DatabaseName varchar(255),
[Log Size (MB)] float,
[Log Space Used (%)] float,
[Status] int)
insert into @LogSpace
execute('dbcc sqlperf(''LogSpace'')')
 
select * from @LogSpace 
where [Log Size (MB)] > 299 
order by [Log Size (MB)] desc 
--order by [Log Space Used (%)] desc 

Microsoft SQL Server, Transaction log

Leave a Reply

Your email address will not be published. Required fields are marked *