SQL使用--Shrink所有數據庫的Log

2019-11-03 08:35:41

字體：大中小

來源：轉載

供稿：網友

數據處理是當前數據庫常見的應用。一些數據庫組成DATA mart從數據源里抽取關心的表進行聚合，將結果推送到算法中進行處理，從而高性能的回答用戶的查詢。

總所周知，Log文件是記錄數據庫操作的文件，對數據庫的完整性，一致性有著重要的意義。作為數據處理的一個常見后果是Log文件的超級龐大。雖然將數據庫的恢復模式設置成Simple可以提醒數據庫盡量使用已有的Log空間，而不是申請新的，后者將會導致文件的增長。但是對于活動的事務，如果一個事務中記錄的Log 行數很多，必然會導致Log文件的龐大。有時這種事務是不能避免的，因為至少一個SQL語句就是一個天然的事務。加入你的Update語句涉及到3千萬行數據，結果必然導致眾多的Log行被寫入，當Update結束的時候，log文件就會增加到200G。

問題是當事務結束后，log文件并不會因為事務已經提交而自動縮短。后果就是10幾個數據庫的log 文件都處在自己的最大值上，也許這需要幾個T的空間，但事實上，同一時刻只有一個數據庫在活動，也就是說500G就夠了。

下面的這個SQL可以自動縮短數據庫服務器上所有的Log文件。

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use [?]
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for

        --找出所有的Log文件，Log文件的status是0x40
        select fileid from sysfiles where  status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin

          --使用DBCC名字縮短Log文件
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end

        --DBCC shrink只能釋放標記為無效的Log區段，使用backup log可以完成這個標記
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --PRint @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'
--依次遍歷所有的數據庫，用數據庫名字替換@ssql中的？，并執行語句
exec sp_msforeachdb @ssql

上一篇：sqlserver2000定時自動備份的實現

下一篇：SQL Server之分布式事務