restore msdb database

USE master
GO
--just to verify if the below command succeeds the restore should succeed too
RESTORE VERIFYONLY
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
--restore the database
RESTORE DATABASE [msdb]
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
WITH REPLACE
GO
--msdb database was curripted and when to suspect mode. So fixed it by restoring a copy of a backup

Cleaning up of msdb database

Cleaning up msdb database is one of the neglected yet most important task of a sql administrator. We have some SSIS packages, full back up once a day and transactional backup in every 15 minutes. We use databaseMail extensively therefore cleaning up msdb database is a good practice.

The msdb database was 241 MB and after cleaning up ( running the following scripts) I am able to bring it down to 153 MB. I am keeping a log of the database growth in a table in every 4 hours. I do not think that the size of the msdb has to be this big. So I am leaving it the way it is and then looking at table where the file growth is captured  befor I make the decision if I need to rsize the db to a smaller size.

The scripts that I ran

--removing anything before a month

DECLARE
SET @date = DATEADD(day, -30, CURRENT_TIMESTAMP)
 --delete backup history

--delete old mail items
--especially, if you are sending attachements
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @date
--delete the log of the sent items
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @date
--delete the SQL Server agent job history log
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @date
EXEC msdb.dbo.sp_delete_backuphistory @date @date datetime