SQL Server jobs on production instances

Mon Jun 30, 2008 by Tara Kizer in sql-server-database-administration

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 = 50, @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	DBCC CHECKDB(dbName) WITH PHYSICAL_ONLY	Daily	Loop through each database, see this for sample code.
Update Statistics	EXEC sp_updatestats @resample = 'resample'	Daily	Might not be needed if your indexes are getting defragmented daily. Loop through each database, see this for sample code.

Do you have any jobs that you put on every production SQL Server instance that I didn't cover? If you do, I'd love to hear from you.

Legacy Comments


jeff 2008-06-30	re: SQL Server jobs on production instances Tara -- This is a great post; it is so nice to see everything all in one grid, with examples, notes, and links. Not being a DBA myself, but occasionally called upon to administer a database server, it is very handy to see what the experts do to keep things running smooth. Thank you!

Tara 2008-06-30	re: SQL Server jobs on production instances Thanks Jeff. I'm not sure what the experts do on their systems, but I've shown what we do here. ;)

tosc 2008-07-01	re: SQL Server jobs on production instances Hi Tara, a wonderful handout - "maintenance-plan"!

Tim 2008-07-03	re: SQL Server jobs on production instances We also run UPDATE USAGE on a weekly basis.

Tara 2008-07-03	re: SQL Server jobs on production instances Tim, hopefully you aren't running update usage on SQL Server 2005 as it is no longer necessary. The only time that you need to run it on 2005 is when the databases have been upgraded from an earlier version to 2005. And you only need to run it once in this situation. After that and also on newly created databases, the data is always kept up to date. No more dbcc updateusage! I found this out about two weeks ago when I was doing some research on a problem. I stumbled across it in SQL Server Books Online.

Manoj 2008-07-04	re: SQL Server jobs on production instances Great effort Tara.

Don Kolenda 2008-07-07	re: SQL Server jobs on production instances Fantastic, Tara! It is really great that you would share this with the db community. I definitely will keep my eyes open for other posts from you. Thanks again!

Rob 2008-07-15	re: SQL Server jobs on production instances Hi Tara, Very handy set of scripts! Thank you. Just a small comment: I ran your code below on a system with 1 user-database, which happened to be in an offline state. I then recieved the message: Msg 942, Level 14, State 4, Procedure isp_ALTER_INDEX, Line 67 Database 'PerfTest' cannot be opened because it is offline. To solve this, I've added &quot;and @dbname is not null&quot; to the WHERE clause. SET NOCOUNT ON DECLARE @dbName sysname, @rc int SELECT name INTO #db FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND name NOT LIKE '%ReportServer%' AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' SELECT @rc = 1, @dbName = MIN(name) FROM #db WHILE @rc &lt;&gt; 0 and @dbname is not null BEGIN EXEC dbo.isp_ALTER_INDEX @dbName = @dbName, @statsMode = 'SAMPLED', @defragType = 'REBUILD', @minFragPercent = 10, @maxFragPercent = 100, @minRowCount = 1000 SELECT TOP 1 @dbName = name FROM #db WHERE name &gt; @dbName ORDER BY name SET @rc = @@ROWCOUNT END DROP TABLE #db

Kamran Shahid` 2008-07-16	re: SQL Server jobs on production instances should this also work for every month Feb, or months with 31 days for deleting database backup history DECLARE @d datetime SET @d = DATEADD(day, -30, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory

Tara 2008-07-16	re: SQL Server jobs on production instances Kamran, That code just deletes the last 30 days of data. It doesn't touch month information. So it could be any day of the year and it will still delete the past 30 days.

Artem Ervits 2008-07-17	re: SQL Server jobs on production instances I also run transaction log backup on model db since it is shipped in full recovery by default.

Tara 2008-07-17	re: SQL Server jobs on production instances It is unnecessary to run transaction log backups on model as you shouldn't be running any DML (or similar) operations on it that would require point in time recovery. A full backup of model should be enough for recoverability. The log should never fill up for model.

Scott 2008-07-21	re: SQL Server jobs on production instances In your isp_UPDATE_STATISTICS you hard code the table owner as dbo. To prevent errors for the odd tables that sneak in that are owned by another schema I have changed the dynamic SQL that populates the temp table with the list of tables to: SET @SQL = '' SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) ' SET @SQL = @SQL + 'SELECT [name] ' SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects ' SET @SQL = @SQL + 'WHERE xtype = ''U'' AND [name] <> ''dtproperties'' and uid = 1' I am guessing that dbo will always be uid 1 and so far it works for me.

Artem Ervits 2008-07-28	re: SQL Server jobs on production instances I was running a monitoring application as evaluation called Longitude and it gave me a warning that model db log was almost full. Do you think setting model db as simple recovery a good idea?

Tara 2008-07-28	re: SQL Server jobs on production instances Artem, you should look into why this is occurring rather than making a config change. But yes you can make the change, it affects all newly created databases though, so keep that in mind.

Artem Ervits 2008-07-28	re: SQL Server jobs on production instances I understand that, but it doesn't hurt to backup the tran log does it?

Tara 2008-07-28	re: SQL Server jobs on production instances It wouldn't hurt anything.

David Lathrop 2008-09-05	re: SQL Server jobs on production instances In response to Scott's earlier comment: >> To prevent errors for the odd tables that sneak in that are owned by another schema I have changed the dynamic SQL ... Two handy new functions in SQL 2005 are SCHEMA_NAME ( schema_id ) and OBJECT_SCHEMA_NAME ( object_id [, database_id ] ). The original dynamic SQL could have been changed to SET @SQL = '' SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) ' SET @SQL = @SQL + 'SELECT ( OBJECT_SCHEMA_NAME (id) + ''.''+ [name]) AS [name] ' SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects ' SET @SQL = @SQL + 'WHERE xtype = ''U'' AND [name] <> ''dtproperties'' ' Its a pain to have different versions of code for SQL 2000 and 2005, but for this type of code its often better to do that because of the differences introduced by SQL 2005 "schema separate from ownership". We should probably change this to use sys.objects (and "xtype" to "type") and we get: SET @SQL = '' SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) ' SET @SQL = @SQL + 'SELECT ( SCHEMA_NAME(obj.schema_id) + ''.''+ [name]) AS [name] ' SET @SQL = @SQL + 'FROM ' + @dbName + '.sys.objects AS obj ' SET @SQL = @SQL + 'WHERE type = ''U'' AND [name] <> ''dtproperties'' ' SET @SQL = @SQL + ' AND is_ms_shipped = 0' SET @SQL = @SQL + ' AND obj.schema_id IS NOT IN ( SCHEMA_ID( ''sys'' ), SCHEMA_ID( ''INFORMATION_SCHEMA'' ) )' -- always 4 and 3 Note that column sys.objects.principal_id is normally NULL in SQL 2005, since most objects are schema owned. So don't try to check for "principal_id = 1" or you probably won't find much. If you lookup the "Compatibility View" sys.sysobjects in SQL 2005 BOL, you will find the following warning: ------------------------------------------- For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner. Important: If you use any of the following SQL Server 2005 DDL statements, you must use the sys.objects catalog view instead of sys.sysobjects. CREATE \| ALTER \| DROP USER CREATE \| ALTER \| DROP ROLE CREATE \| ALTER \| DROP APPLICATION ROLE CREATE SCHEMA ALTER AUTHORIZATION ON OBJECT ------------------------------------------- There are equivalent warnings on the old pre-2005 stored procedures for managing object, user, roles, logins, etc,. These stored procedures try to mimic the pre-2005 behavior by manipulating schemas to match the owner.