MsSQL数据库日志清除

MsSQL数据库使用久了后，日志空间也逐步变大，备份时也占用大量的空间，
此时建议在备份前先清除log，以下语句可以缩小所有数据库的日志空间

ALTER PROCEDURE [dbo].[SP_TruncateLog]
AS
BEGIN
    DECLARE @name nvarchar(250)
    DECLARE @sql nvarchar(2500)
DECLARE cur_database Cursor FOR
  select name from master..sysdatabases
open cur_database
fetch next from cur_database into @name
while (@@FETCH_STATUS <> -1)
  begin
        print @name + '...'
  set @sql = 'backup log [' + @name + '] with NO_LOG'
  exec sp_executesql @sql;
  set @sql = 'backup log [' + @name + '] with TRUNCATE_ONLY'
  exec sp_executesql @sql;
  set @sql = 'DBCC SHRINKDATABASE([' + @name + '])'
  exec sp_executesql @sql;
  fetch next from cur_database into @name
  end
close cur_database
DEALLOCATE cur_database
END

转载于:https://www.cnblogs.com/rapidhorse/archive/2008/02/08/1235140.html