检查SQL Server事务日志大小

最近开始接手SQL Server数据库管理了，每天都要一个一个检查事务日志大小。于是请同事写了一个检查事务日志大小的SQL脚本，列出事务日志超过1G的数据库，顺便也学习学习，自从转入运维后就很少接触SQL了，现在又要拾起来了。

set nocount on

declare @name sysname
declare @SQL nvarchar ( 600 )

create table #databases(
DATABASE_ID int NOT NULL ,
size int NOT NULL )

declare c1 cursor for
select name from master.dbo.sysdatabases
where has_dbaccess(name) = 1

open c1
fetch c1 into @name

while @@fetch_status >= 0
begin
select @SQL = ' insertinto#databases
select ' + convert (sysname, db_id ( @name )) + ' ,sizefrom '
+ QuoteName ( @name ) + ' .dbo.sysfileswherefileid=2 '
execute ( @SQL )
fetch c1 into @name
end

deallocate c1

select
DATABASE_NAME = db_name (DATABASE_ID),
LOG_SIZE = cast ( round (( CEILING (size * 8 / 1024 ) + 1 ) / 1024 , 3 ) as varchar ( 20 )) + ' G ' -- (G)
from #databases
where db_name (DATABASE_ID) not in ( ' master ' , ' model ' , ' msdb ' , ' pubs ' , ' Northwind ' , ' tempdb ' )
and round (( CEILING (size * 8 / 1024 ) + 1 ) / 1024 , 3 ) >= 1
order by 1

drop table #databases