Here’s a little SQL Server T-SQL script I wrote that will executes some day-to-day maintenance
It’ calls some sp’s in MSDB to remove old (as specified by the parameter in the dateadd() function) mail, job and backup history info that will normally accumulate in msdb making it grow bigger over time.
Also a DBCC CHECKDB statement is constructed for each on-line database. Microsoft recommends executing this command frequently. Especially before a full backup is made.
benefits of this script over a SSIS maintenance plan:
- newly created databases are automaticly included
- Databases can be set off-line without the job-step to fail
- Databases in standby mode are skipped
- minumum overhead, giving you precise control
- I've been running this script for more then 2 years now without any error(s)...
I normally schedule this script daily in SQL Server Agent as a separate job before the full backup kicks in, or include as a separate step in the backup process.
Here’s T-SQL Code (store in the CodeCatalog):
-- ====================================================================
-- Author: SQL Server Advisor
-- Create date: jan/2009
-- Description: execute maintenance tasks per database, deletes MSDB log info
-- ====================================================================
CREATE PROCEDURE [dbo].[usp_DatabaseMaintenance] AS
BEGIN
DECLARE @Histdate DATETIME
DECLARE @dbname VARCHAR(100), @command NVARCHAR(350)
SELECT @Histdate= DATEADD(DAY, -28, GETDATE())
--sp’s stored in MSDB
EXEC msdb..SP_DELETE_BACKUPHISTORY @Histdate -- delete old history info
-- delete old mail items
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Histdate
-- delete the log of the sent items
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @Histdate
-- delete the job history log
EXEC msdb.dbo.SP_PURGE_JOBHISTORY @oldest_date = @Histdate
--get all the on-line databases
DECLARE dbnames_cursor CURSOR FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('tempdb') -- skip the unwanted DBs
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
ORDER BY name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbname = QUOTENAME(@dbname)
SET @command = 'DBCC CHECKDB (' + @dbname + ') WITH NO_INFOMSGS'
PRINT @command
EXEC SP_EXECUTESQL @command
FETCH NEXT FROM dbnames_cursor INTO @dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
END
