Large size MSDB, log_shipping_monitor_histroy_detail

Question

Large size MSDB, log_shipping_monitor_histroy_detail

Steve Roe

Old Hand

Points: 325
More actions
March 4, 2008 at 8:30 am

#154171

Hi all,
New poster and beginner SQL Server 2005 user.
I have a set of 5 servers running around 10-20 databases on each server. All the databases use log shipping every 15 minutes to a sixth server which would act as a "standby" server.
On one of the 5 servers the msdb database is around 10GB in size. The server is beginning to run sluggishly and occasionally the log shipping alert job reports a failure, which then corrects itself.
I have run the following T-SQL to get some statistics out of the db to see where the space is being used:
USE msdb
SELECT TOP(30) OBJECT_NAME(object_id), rows
FROM sys.partitions
ORDER BY rows DESC
The results show:
log_shipping_monitor_history_detail3439436
log_shipping_monitor_history_detail3439436
log_shipping_monitor_history_detail3438282
log_shipping_monitor_error_detail3400085
log_shipping_monitor_error_detail3400085
log_shipping_monitor_error_detail3399105
sysjobhistory2762508
sysjobhistory2762508
backupfile102071
backupmediaset34087
backupmediaset34087
backupmediafamily34087
backupmediafamily34087
backupset34087
backupset34087
backupfilegroup34087
syscolpars2646
syscolpars2646
sysmultiobjrefs2320
sysmultiobjrefs2320
sysrowsetcolumns1438
syshobtcolumns1438
sysmail_log938
sysobjvalues800
sysobjkeycrypts649
sysschobjs613
sysschobjs613
sysschobjs613
sysschobjs613
sysiscols545
I've tried running the sp_cleanup_log_shipping_history stored procedure to reduce the history, but it doesn't appear to have any effect.
Can anyone help a newbie? Thanks in advance
Steve

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

I believe you simply have to shrink the msdb database.

running sp_cleanup_log_shipping_history will remove rows from the history tables, but that just created free space--the space is still reserved for future rows.

You'd need to explicitly shrink the database to make the database smaller.

I'm sure if you right clicked on the database/selected properties you'd see something like

Size: 1024.23 Mb

Space Available: 889.67Mb

as an example above, i could shrink the database from a bit over a gig to about 200 meg, given the values above.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Roe Old Hand Points: 325 More actions · Answer 2

Thanks for the reply Lowell.

After running sp_cleanup_log_shipping_history for each database I looked at the properties of the database as suggested.

Size: 10380.94 MB

Space Available: 0.25MB

In addition, rerunning the statistics shows:

log_shipping_monitor_history_detail3437489
log_shipping_monitor_history_detail3437489
log_shipping_monitor_history_detail3437489
log_shipping_monitor_error_detail3399013
log_shipping_monitor_error_detail3398841
log_shipping_monitor_error_detail3397169
sysjobhistory2785537
sysjobhistory2785537
backupfile102521
backupmediaset34237
backupmediaset34237
backupmediafamily34237
backupmediafamily34237
backupset34237
backupset34237
backupfilegroup34237
syscolpars2646
syscolpars2646
sysmultiobjrefs2320
sysmultiobjrefs2320
sysrowsetcolumns1438
syshobtcolumns1438
sysmail_log938
sysobjvalues800
sysobjkeycrypts649
sysschobjs613
sysschobjs613
sysschobjs613
sysschobjs613
sysiscols545

On the face of it the log_shipping_monitor tables have decreased, but only slightly.

Steve Roe Old Hand Points: 325 More actions · Answer 3

I've checked the size of the msdbdata.mdf files on the other servers in the setup and they are also large ( > 5GB) so it seems to be an issue on all the servers.

sachinmundra SSC Rookie Points: 45 More actions · Answer 4

you should use the system sproc : sp_cleanup_log_shipping_history to clean up those tables.

jjradha SSChasing Mays Points: 616 More actions · Answer 5

Hi Steve,

I am having the same problem, would like to shrink my MSDB database in 2 of my production servers.

Apparently it has something to do with the log shipping I used to have.

I am not 100% sure of that either, but it must be something odd 🙂

if anyone can help me please

as how to shrink the MSDB database please let me know,

thanks in advance

marcello miorelli

distill SSC Enthusiast Points: 181 More actions · Answer 6

I have the same issue with my MSDB data on my Primary Log Shipping server.

I have looked into the issue but with little results.

Basically the system stored proc - sp_cleanup_log_shipping_history works off the retention rate set up in the log-shipping jobs. When i looked at the retention on the log shipping jobs it is set to 3days yet the tables are huge

I ran the sp_cleanup_log_shipping_history for each of the agent_id's associated to the Log Shipping jobs but this has not reduced the used space in my MSDB

Any help on reducing the log_shipping_error_details and log_shipping _monitor_history_detail would be greatly appreciated

Thank you in advance