June 13, 2013 at 4:33 am
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
June 13, 2013 at 2:30 pm
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