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