Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Will able to shrink MSDB - Log Shipping Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 4:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:44 AM
Points: 91, Visits: 322
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
Post #1462971
Posted Thursday, June 13, 2013 2:30 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 414, Visits: 390
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 datetimeSET @GETDATE = GETDATE() EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;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.
Post #1463307
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse