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. |
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.