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