Tara Kizer Blog

Tara Kizer

SQL Server jobs on production instances

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 "and @dbname is not null" 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 <> 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 > @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.