SQL Server jobs on production instances

来源：这里教程网时间：2026-03-02 10:00:13 作者：

The most important thing that a Database Administrator does is backups. To automate them, we schedule them as jobs. But what other jobs are important on SQL Server instances?Here are the jobs that I have on every SQL Server 2005 production instance:

Name	Code	Schedule	Notes
Backup System Databases	EXEC isp_Backup @path = 'F:Backup', @dbType = 'System', @bkpType = 'Full', @retention = 5, @liteSpeed = 'N'	Daily
Backup Transaction Logs	EXEC isp_Backup @path = 'F:Backup', @dbType = 'User', @bkpType = 'TLog', @retention = 5, @liteSpeed = 'Y'	Every 15 minutes
Backup User Databases	EXEC isp_Backup @path = 'F:Backup', @dbType = 'User', @bkpType = 'Full', @retention = 5, @liteSpeed = 'Y'	Daily
Defragment Indexes	EXEC isp_ALTER_INDEX @dbName = @dbName, @statsMode = 'SAMPLED', @defragType = 'REBUILD', @minFragPercent = 10, @maxFragPercent = 100, @minRowCount = 1000	Daily or weekly	Loop through each database, see this for sample code.
Delete Backup History	DECLARE @d datetime SET @d = DATEADD(day, -30, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory @d	Daily or weekly	See this for more details.
Delete Database Mail History	DECLARE @d datetime SET @d = DATEADD(dd, -5, GETDATE()) EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @d	Daily or weekly	Needed only on systems that send mail with attachments using Database Mail. See this for more details.
Integrity Checks	EXEC isp_DBCC_CHECKDB	Daily
Update Statistics	EXEC isp_UPDATE_STATISTICS @dbName = @dbName, @sample = 25	Daily	Might not be needed if your indexes are getting defragmented daily. Loop through each database, see this for sample code.