截断数据库日志作业

use master

declare @logname varchar(8000),@dbname varchar(8000)

declare @SqlTxt varchar(8000)

DECLARE bk_cursor CURSOR FOR
select t2.name as logname,t1.name as dbname from sysaltfiles t2 inner join sysdatabases t1 on t1.dbid=t2.dbid where t2.fileid=2 and t1.dbid>5  and t1.mode=0 

OPEN bk_cursor

FETCH NEXT FROM bk_cursor into @logname,@dbname

WHILE @@FETCH_STATUS = 0
BEGIN
         set @SqlTxt = 'use ' + @dbname + '; '
         set @SqlTxt = @SqlTxt + 'BACKUP LOG  ' + @dbname + ' with no_log; '
         set @SqlTxt = @SqlTxt + 'DBCC SHRINKFILE (' + rtrim(@logname) + ',500)'
         
         exec(@SqlTxt)
         FETCH NEXT FROM bk_cursor into @logname,@dbname
END


CLOSE bk_cursor
DEALLOCATE bk_cursor