Thursday, February 19, 2009

SQL Server Day to day maintenance


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  



Bookmark and Share