ms-sql 을 운용하다 보면 msdb 데이터베이스에 많은 자료가 저장될수 있다
보통의 경우 agents 실행 백업정보를 담고 있는데 여기서 2년된 자료를 삭제하는 쿼리문을 알아보도록 하자
DECLARE @backupID int
DECLARE @mediaID int
DECLARE @restoreID int
DECLARE @year char(4)
DECLARE @month char(2)
DECLARE @day char(2)
DECLARE @deleteday char(10)
SET @year = year(getdate())-2 — 2년전
SET @month = month(getdate())
SET @day = day(getdate())
IF len(@month) = 1
SET @month = ‘0’+ @month
IF len(@day) = 1
SET @day = ‘0’+ @day
SET @deleteday = @year +’-‘+ @month +’-‘+ @day
SELECT TOP 1 @backupID = backup_set_id, @mediaID = media_set_id from msdb..backupset
where
backup_finish_date > @deleteday
SELECT TOP 1 @restoreID = restore_history_id From msdb..restorehistory
Where
restore_date > @deleteday
delete from msdb..restorefile where restore_history_id < @restoreID
delete from msdb..restorefilegroup where restore_history_id < @restoreID
delete from msdb..restorehistory where restore_history_id < @restoreID or backup_set_id < @backupID
delete from msdb..backupfile where backup_set_id < @backupID
delete from msdb..backupmediafamily where media_set_id < @mediaID
delete from msdb..backupset where backup_set_id < @backupID
use msdb
DBCC SHOWCONTIG
DBCC DBREINDEX (restorefile, ”, 100)
DBCC DBREINDEX (restorefilegroup, ”, 100)
DBCC DBREINDEX (restorehistory, ”, 100)
DBCC DBREINDEX (backupfile, ”, 100)
DBCC DBREINDEX (backupmediafamily, ”, 100)
DBCC DBREINDEX (backupset, ”, 100)
출저 : http://zasfe.com/index.php/archives/images/tagcloud.swf?page=10