SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Will able to shrink MSDB - Log Shipping


Will able to shrink MSDB - Log Shipping

Author
Message
pradeep.mohan
pradeep.mohan
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 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
Noetic DBA
Noetic DBA
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 Visits: 422
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search