Will able to shrink MSDB - Log Shipping

  • Experts,

    In our production server MSDB database growing very huge can we shrinking the log and data files but in that respective server log shipping configured and its working successfully around 700 databases.

    Is it having any impact log shipping configuration when we going to shrinking MSDB files?

    So pls advice to do further.

    thanks.

    Pradeep

  • As always, it is not advisable o shrink a database unless it is an extreme circumstance, like you are running out of disk space, but it can be done. However, for the shrink to work you need to create some space. Listed below are some purge scripts for MSDB. We had a similar situation here (we also perform log shipping) and I used these scripts to free up some space. After that I created a job to purge all history over 30 days. The job runs everyday and I have not had any growth since that time.

    --Warning: It is advisable to re-index the history tables before and after executing these stored procedures,

    --otherwise, these store procedure could take a long time to purge the history tables.

    --The MSDB..SP_DELETE_BACKUPHISTORY proc has terrible performance unless you add the following

    --indexes to speed up the search process. Cuts runtime down dramatically.

    USE MSDB

    GO

    create index BACKUPSET_I01 ON BACKUPSET (MEDIA_SET_ID)

    GO

    create index BACKUPSET_I02 ON BACKUPSET (BACKUP_SET_ID,MEDIA_SET_ID)

    GO

    USE msdb

    declare @deleteday datetime

    SET @deleteday= dateadd(dd,-30,getdate())

    EXEC msdb.dbo.sp_delete_backuphistory @deleteday

    USE msdb;

    GO

    EXEC sp_delete_backuphistory @oldest_date = '01/14/2013';

    --remove history for a specific job

    USE msdb ;

    GO

    EXEC dbo.sp_purge_jobhistory

    @job_name = N'NightlyBackups' ;

    GO

    --remove history for all jobs

    USE msdb ;

    GO

    EXEC dbo.sp_purge_jobhistory ;

    GO

    --deleting all events

    EXECUTE msdb.dbo.sysmail_delete_log_sp ;

    GO

    --delete oldest event

    EXECUTE msdb.dbo.sysmail_delete_log_sp

    @logged_before = 'October 9, 2005' ;

    GO

    --delete all events of a certain type

    EXECUTE msdb.dbo.sysmail_delete_log_sp

    @event_type = 'success' ;

    GO

    --deleting all emails

    DECLARE @getdate-2 datetimeSET @getdate-2 = GETDATE() EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @getdate-2;GO

    --deleting oldest emails

    EXECUTE msdb.dbo.sysmail_delete_mailitems_sp

    @sent_before = 'October 9, 2012' ;

    GO

    --deleting all emails of a certain type

    EXECUTE msdb.dbo.sysmail_delete_mailitems_sp

    @sent_status = 'failed' ;

    GO

    If you need it, I can also post the script for the nightly purge job as well.

    Good luck.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply