Disk-Space

龐大的 MSDB 數據庫

  • April 23, 2014

我有一個非常大的 MSDB 數據庫，並且一直在努力清除它並設置維護任務——這是我的前任幾年前應該做的事情！

到目前為止，我已經設法截斷了sysmaintplan_logdetail和sysmaintplan_log文件。

現在我希望查看一些備份數據庫——備份文件表超過 5Gb。

我嘗試使用 SP sp_delete_backuphistory但這填滿了我的 MSDB 事務日誌並幾乎殺死了伺服器……

有沒有其他方法可以清除它並縮小文件而不造成任何損壞？

有關資訊，我的 MSDB 數據文件仍然是 20Gb，使用的是 13Gb。登錄500Mb，用了6Mb。

任何幫助將不勝感激 - 我不是 DBA！

我嘗試使用 SP sp_delete_backuphistory 但這填滿了我的 MSDB 事務日誌並幾乎殺死了伺服器……

有沒有其他方法可以清除它並縮小文件而不造成任何損壞？

分批進行。

如果你的 msdb 很大，那麼在執行腳本後，為了釋放未使用的空間，我建議你縮小你的 msdb（是的，縮小它，然後一旦空間被釋放，你可以使用 OLA 的腳本進行重組/重建和更新統計資訊-這種收縮將是一次性的事情- 這樣您就可以釋放未使用的空間）。

確保根據伺服器的繁忙程度安排以下腳本頻繁執行（就作業頻率而言，日誌傳送是否正在執行以及數據庫郵件的使用+伺服器上發生的備份和恢復為他們都登錄到MSDB）

use msdb
/* Author : Kin
  Purpose: For dba.stackexchange.com - Trim down msdb
*/

-- Declaration
DECLARE @DeleteDate datetime 
DECLARE @DaysToRetain int
DECLARE @Batch int

set @DaysToRetain = 30
set @Batch = 5000
set @DeleteDate = convert(datetime,convert(varchar,getdate()-@DaysToRetain,101),101) 


-- ----------------
--  Index creation
-- ----------------
--  backupset
-- ----------------
Print 'Index Creation..'

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_backup_set_uuid')
begin   
   Create NONCLUSTERED index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)
end 

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_media_set_id')
begin   
   Create NONCLUSTERED index IX_backupset_media_set_id on backupset(media_set_id)
end

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_backup_finish_date')begin  
   Create NONCLUSTERED index IX_backupset_backup_finish_date on backupset(backup_finish_date)
end 

if not exists (select * from msdb..sysindexes where name = 'IX_backupset_backup_start_date')    
begin
   Create NONCLUSTERED index IX_backupset_backup_start_date on backupset(backup_start_date)
end


-- ------------
--  backupfile
-- ------------
if not exists (select * from msdb..sysindexes where name = 'IX_backupfile_backup_set_id')    
begin
   Create NONCLUSTERED index IX_backupfile_backup_set_id on backupfile(backup_set_id)
end

-- -------------------
--  backupmediafamily
-- -------------------
if not exists (select * from msdb..sysindexes where name = 'IX_backupmediafamily_media_set_id')    
begin
   Create NONCLUSTERED index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
end

-- -------------------
--  backupfilegroup
-- -------------------
if not exists (select * from msdb..sysindexes where name = 'IX_backupfilegroup_backup_set_id')    
begin
   Create NONCLUSTERED index IX_backupfilegroup_backup_set_id on backupfilegroup(backup_set_id)
end

-- ----------------
--  restorehistory
-- ----------------
if not exists (select * from msdb..sysindexes where name = 'IX_restorehistory_restore_history_id')    
begin
   Create NONCLUSTERED index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)
end

if not exists (select * from msdb..sysindexes where name = 'IX_restorehistory_backup_set_id')    
begin
   Create NONCLUSTERED index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)
end

-- -------------
--  restorefile
-- -------------
if not exists (select * from msdb..sysindexes where name = 'IX_restorefile_restore_history_id')    
begin
   Create NONCLUSTERED index IX_restorefile_restore_history_id on restorefile(restore_history_id)
end

-- ------------------
--  restorefilegroup
-- ------------------
if not exists (select * from msdb..sysindexes where name = 'IX_restorefilegroup_restore_history_id')    
begin
   Create NONCLUSTERED index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)
end

Print 'End of Index Creation..'


-- ------------------------------
--  Maintenance  before deletion
-- ------------------------------
--  Reindex 
-- ------------------------------

-- ----------------
--  backupset
-- ----------------
Print 'Maintenance Reindex..'

ALTER INDEX [IX_backupset_backup_set_uuid] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_backup_set_uuid..'

ALTER INDEX [IX_backupset_media_set_id] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_media_set_id..'


ALTER INDEX [IX_backupset_backup_finish_date] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_backup_finish_date..'

ALTER INDEX [IX_backupset_backup_start_date] ON [msdb].[dbo].[backupset] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupset_backup_start_date..'


-- ------------
--  backupfile
-- ------------
ALTER INDEX [IX_backupfile_backup_set_id] ON [msdb].[dbo].[backupfile] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupfile_backup_set_id..'

-- -------------------
--  backupmediafamily
-- -------------------
ALTER INDEX [IX_backupmediafamily_media_set_id] ON [msdb].[dbo].[backupmediafamily] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_backupmediafamily_media_set_id..'

-- ------------------
--  backupfilegroup
-- ------------------
ALTER INDEX [IX_backupfilegroup_backup_set_id] ON [msdb].[dbo].[backupfilegroup] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print '[IX_backupfilegroup_backup_set_id]..'

-- ----------------
--  restorehistory
-- ----------------
ALTER INDEX [IX_restorehistory_restore_history_id] ON [msdb].[dbo].[restorehistory] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_restorehistory_restore_history_id..'


ALTER INDEX [IX_restorehistory_backup_set_id] ON [msdb].[dbo].[restorehistory] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_restorehistory_backup_set_id..'

-- -------------
--  restorefile
-- -------------
ALTER INDEX [IX_restorefile_restore_history_id] ON [msdb].[dbo].[restorefile] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_restorefile_restore_history_id..'


-- ------------------
--  restorefilegroup
-- ------------------
ALTER INDEX [IX_restorefilegroup_restore_history_id] ON [msdb].[dbo].[restorefilegroup] REORGANIZE WITH ( LOB_COMPACTION = OFF)
WAITFOR DELAY '00:00:05'
print 'IX_restorefilegroup_restore_history_id..'

Print 'End of Maintenance Reindex..'



--delete records    

print 'DEL restorefile..' 
if exists (select * from msdb.dbo.sysobjects where name = 'restorefile')    
begin 
   
   DELETE top (@Batch) FROM msdb..restorefile
   FROM msdb..restorefile rf with (nolock)
       INNER JOIN msdb..restorehistory rh with (nolock) ON rf.restore_history_id = rh.restore_history_id
       INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
   WHERE bs.backup_finish_date < @DeleteDate 
   
   
   while @@rowcount <> 0    
   begin    
   
       DELETE top (@Batch) FROM msdb..restorefile
       FROM msdb..restorefile rf with (nolock)
           INNER JOIN msdb..restorehistory rh with (nolock) ON rf.restore_history_id = rh.restore_history_id
           INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
       WHERE bs.backup_finish_date < @DeleteDate 
       
   end    
end    



print 'DEL restorefilegroup..'  
if exists (select * from msdb.dbo.sysobjects where name = 'restorefilegroup')    
begin 
   
   DELETE top (@Batch) FROM msdb..restorefilegroup
   FROM msdb..restorefilegroup rfg with (nolock)
       INNER JOIN msdb..restorehistory rh with (nolock) ON rfg.restore_history_id = rh.restore_history_id
       INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
   WHERE bs.backup_finish_date  < @DeleteDate 

   
   while @@rowcount <> 0    
   begin    
   
       DELETE top (@Batch) FROM msdb..restorefilegroup
       FROM msdb..restorefilegroup rfg with (nolock)
           INNER JOIN msdb..restorehistory rh with (nolock) ON rfg.restore_history_id = rh.restore_history_id
           INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
       WHERE bs.backup_finish_date  < @DeleteDate  
   End
End 
   
   
   
print 'Temp table ..'   
SELECT media_set_id, backup_finish_date
   INTO #Temp 
FROM msdb..backupset with (nolock)
WHERE backup_finish_date  < @DeleteDate 


   


print 'DEL backupfile..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupfile')    
begin 
   
   DELETE top (@Batch) FROM msdb..backupfile
   FROM msdb..backupfile bf with (nolock)
       INNER JOIN msdb..backupset bs with (nolock) ON bf.backup_set_id = bs.backup_set_id
       INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
   WHERE bs.backup_finish_date  < @DeleteDate 
   
   
   while @@rowcount <> 0    
   begin    
   
       DELETE top (@Batch) FROM msdb..backupfile
       FROM msdb..backupfile bf with (nolock)
           INNER JOIN msdb..backupset bs with (nolock) ON bf.backup_set_id = bs.backup_set_id
           INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
       WHERE bs.backup_finish_date  < @DeleteDate      
   End
End 



print 'DEL backupmediafamily..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupmediafamily')    
begin 
   
   DELETE top (@Batch) FROM msdb..backupmediafamily
   FROM msdb..backupmediafamily bmf with (nolock)
       INNER JOIN msdb..backupmediaset bms with (nolock) ON bmf.media_set_id = bms.media_set_id
       INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
   
   
   while @@rowcount <> 0    
   begin    
       
       DELETE top (@Batch) FROM msdb..backupmediafamily
       FROM msdb..backupmediafamily bmf with (nolock)
           INNER JOIN msdb..backupmediaset bms with (nolock) ON bmf.media_set_id = bms.media_set_id
           INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
   End
End 



print 'DEL backupfilegroup..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupfilegroup')    
begin 
   
   Delete top (@Batch) FROM msdb..backupfilegroup
       FROM msdb..backupfilegroup bfg with (nolock)
       INNER JOIN msdb..backupset bs with (nolock) ON bfg.backup_set_id = bs.backup_set_id
       INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
   
   
   while @@rowcount <> 0    
   begin    
   
       Delete top (@Batch) FROM msdb..backupfilegroup
       FROM msdb..backupfilegroup bfg with (nolock)
           INNER JOIN msdb..backupset bs with (nolock) ON bfg.backup_set_id = bs.backup_set_id
           INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id

   End
End 
       
   

print 'DEL restorehistory..' 
if exists (select * from msdb.dbo.sysobjects where name = 'restorehistory')    
begin       
   DELETE top (@Batch) FROM msdb..restorehistory
   FROM msdb..restorehistory rh with (nolock)
       INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
   WHERE bs.backup_finish_date  < @DeleteDate 
   
   
   while @@rowcount <> 0    
   begin    
   
       DELETE top (@Batch) FROM msdb..restorehistory
       FROM msdb..restorehistory rh with (nolock)
           INNER JOIN msdb..backupset bs with (nolock) ON rh.backup_set_id = bs.backup_set_id
       WHERE bs.backup_finish_date  < @DeleteDate 
   
   End
End 
   
   
print 'DEL backupset..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupset')    
begin   

   DELETE top (@Batch) FROM msdb..backupset
   FROM msdb..backupset bs with (nolock)
       INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
       
   while @@rowcount <> 0    
   begin    
   
       DELETE top (@Batch) FROM msdb..backupset
       FROM msdb..backupset bs with (nolock)
           INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
   
   End
End 
   

print 'DEL backupmediaset..' 
if exists (select * from msdb.dbo.sysobjects where name = 'backupmediaset')    
begin   

   DELETE top (@Batch) FROM msdb..backupmediaset
   FROM msdb..backupmediaset bms with (nolock)
       INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
       
   while @@rowcount <> 0    
   begin    
   
       DELETE top (@Batch) FROM msdb..backupmediaset
       FROM msdb..backupmediaset bms with (nolock)
           INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
   
   End
End 
   

DROP TABLE #Temp



print 'Update Statistic on msdb'
-- Updates the statistics for all tables in the database. 
EXEC sp_updatestats
go

引用自：https://dba.stackexchange.com/questions/63755

相關問答

Sql-Server

從表中刪除列，截斷它，然後像以前一樣將數據重新插入表中不會減少佔用的空間

  • December 6, 2020
檢視問答
Sql-Server

停止 MSDB 的收縮是否安全？

  • August 24, 2019
檢視問答
Postgresql

發出 truncate 命令後，表的外部大小仍未釋放（作為磁碟空間）

  • March 16, 2017
檢視問答
Sql-Server

截斷 SQL Server 錯誤日誌的安全方法

  • January 5, 2013
檢視問答
Postgresql

PostgreSQL：TRUNCATE 後未釋放磁碟空間

  • June 27, 2011
檢視問答
Postgresql

具有較小數據類型的表似乎在磁碟上佔用更多空間？

  • May 15, 2022
檢視問答