利用SQLAGENT作业清理MSDB中的记录
作者：[51ak ]
MSDB数据库的快速增长（如果有日志传送等作业的时候) 需要定时清理，用下面的脚本创建一个作业可以解决此问题
/****** Object: Job [数据库维护-delete msdb] Script Date: 07/14/2011 10:39:37 ******/
IF EXISTS(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'数据库维护-delete msdb')
begin
declare @tempid varchar(255)
SELECT @tempid=job_id FROM msdb.dbo.sysjobs_view WHERE name = N'数据库维护-delete msdb'
EXEC msdb.dbo.sp_delete_job@job_id=@tempid, @delete_unused_schedule=1
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 07/14/2011 10:39:37 ******/
IF NOT EXISTS(SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category@class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job@job_name=N'数据库维护-delete msdb',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'gaocontrol', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [1] Script Date: 07/14/2011 10:39:38 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep@job_id=@jobId, @step_name=N'1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'use NorthWind
declare @i int,@cnt int,@job_id uniqueidentifier
Ｓelect job_id ,identity(int,1,1) as rid into #temp
from msdb.dbo.sysjobs where name like''LSRestore_%'' or name like''LSCopy_%'' or name like''LSBack_%'' and enabled=1
Ｓelect @cnt =COUNT (*) from #temp
set @i=1
while @i <=@cnt
begin
set @job_id =(Ｓelect job_id from #temp where rid=@i )
EXEC msdb.dbo.sp_update_job @job_id=@job_id, @enabled=0;
set @i =@i +1
end
declare @current varchar(50)
set @current=(Select CONVERT(varchar(100), GETDATE()-30, 23))
waitfor DELAY ''00:05:00''
--exec msdb.dbo.sp_delete_backuphistory @current
ALTER TABLE msdb.dbo.[backupmediafamily] DROP CONSTRAINT FK__backupmed__media__0A688BB1;
ALTER TABLE msdb.dbo.[backupset] DROP CONSTRAINT FK__backupset__media__10216507;
ALTER TABLE msdb.dbo.[backupfilegroup] DROP CONSTRAINT FK__backupfil__backu__14E61A24;
ALTER TABLE msdb.dbo.[backupfile] DROP CONSTRAINT FK__backupfil__backu__19AACF41;
ALTER TABLE msdb.dbo.[restorehistory] DROP CONSTRAINT FK__restorehi__backu__1E6F845E;
ALTER TABLE msdb.dbo.[restorefile] DROP CONSTRAINT FK__restorefi__resto__2057CCD0;
ALTER TABLE msdb.dbo.[restorefilegroup] DROP CONSTRAINT FK__restorefi__resto__22401542;
truncate table msdb.dbo.backupfile
truncate table msdb.dbo.backupfilegroup
truncate table msdb.dbo.backupmediafamily
truncate table msdb.dbo.backupmediaset
truncate table msdb.dbo.backupset
truncate table msdb.dbo.restorefile
truncate table msdb.dbo.restorefilegroup
truncate table msdb.dbo.restorehistory
truncate table msdb.dbo.log_shipping_monitor_history_detail
ALTER TABLE msdb.dbo.[backupmediafamily] ADD CONSTRAINT FK__backupmed__media__0A688BB1 FOREIGN KEY (media_set_id) REFERENCES backupmediaset(media_set_id);
ALTER TABLE msdb.dbo.[backupset] ADD CONSTRAINT FK__backupset__media__10216507 FOREIGN KEY (media_set_id) REFERENCES backupmediaset(media_set_id);
ALTER TABLE msdb.dbo.[backupfilegroup] ADD CONSTRAINT FK__backupfil__backu__14E61A24 FOREIGN KEY (backup_set_id) REFERENCES backupset(backup_set_id);
ALTER TABLE msdb.dbo.[backupfile] ADD CONSTRAINT FK__backupfil__backu__19AACF41 FOREIGN KEY (backup_set_id) REFERENCES backupset(backup_set_id);
ALTER TABLE msdb.dbo.[restorehistory] ADD CONSTRAINT FK__restorehi__backu__1E6F845E FOREIGN KEY (backup_set_id) REFERENCES backupset(backup_set_id);
ALTER TABLE msdb.dbo.[restorefile] ADD CONSTRAINT FK__restorefi__resto__2057CCD0 FOREIGN KEY (restore_history_id) REFERENCES restorehistory(restore_history_id);
ALTER TABLE msdb.dbo.[restorefilegroup] ADD CONSTRAINT FK__restorefi__resto__22401542 FOREIGN KEY (restore_history_id) REFERENCES restorehistory(restore_history_id);
DBCC SHRINKDATABASE(N''msdb'' )
set @i=1
while @i <=@cnt
begin
set @job_id =(Ｓelect job_id from #temp where rid=@i )
EXEC msdb.dbo.sp_update_job @job_id=@job_id, @enabled=1;
set @i =@i +1
end
truncate table #temp
drop table #temp',
@database_name=N'northwind',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job@job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule@job_id=@jobId, @name=N'1',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110114,
@active_end_date=99991231,
@active_start_time=40010,
@active_end_time=235959,
@schedule_uid=N'ceee8082-105f-4e13-a277-82816d2a2a89'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver@job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Ｓelect 'OOOOOOOK' as result
end
ELSE
begin
Ｓelect '==============================' as result