SQL使用--Shrink所有數據庫的Log
2024-07-21 02:44:54
供稿：網友
數據處理是當前數據庫常見的應用。一些數據庫組成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
