msdb growing in size

• gk-411903

  Hall of Fame

  Points: 3801

  More actions

  November 21, 2007 at 2:56 pm

  #176929

  Hello Forum

  I have a primary and standby setup for one of our production databases; The msdb in the primary server (A) is 1.5GB in size and on the secondary server (B) is 500+MB; I have done switchover to the secondary thrice in the last 3 months but we switch back to the primary server within an hour. In other words most of the time (99.99%) it is only the primary server (A) shipping logs to (B);

  I read through the links

  http://www.sqlservercentral.com/Forums/Topic302807-169-1.aspx

  In the Secondary Server (B) the following are the results

  dbo log_shipping_monitor_history_detail 405.820

  dbo backupfile 7.070

  dbo backupset 5.203

  dbo backupmediafamily 1.914

  dbo sysjobhistory 1.875

  dbo restorefile 1.273

  dbo backupfilegroup 1.258

  dbo backupmediaset 1.117

  dbo restorehistory 1.008

  dbo log_shipping_monitor_error_detail 0.180

  dbo MSdbms_map 0.023

  etc.

  I tried the following

  use master

  sp_help_log_shipping_secondary_database @secondary_database = N'my_secondary_db'

  use master

  sp_cleanup_log_shipping_history

  @agent_id = '94DEB3BA-3EB2-57B9-C73B-7A00A429BEE3'

  , @agent_type = 2

  Command(s) completed successfully.

  sp_cleanup_log_shipping_history

  @agent_id = '94DEB3BA-3EB2-57B9-C73B-7A00A429BEE3''

  , @agent_type = 1

  Command(s) completed successfully.

  The msdb has not shrunk still. Any ideas?

• JDixon-385826

  SSCrazy

  Points: 2723

  More actions

  November 26, 2007 at 10:56 pm

  #754382

  Does MSDB grow everytime you fail over?

• gk-411903

  Hall of Fame

  Points: 3801

  More actions

  December 12, 2007 at 4:25 pm

  #759639

  Good question. I did not notice to that extent. Next time i do switch over i will look at it. The suggestion i got is that delete from log_shipping_monitor_history_detail. Currently working on the same.:crying:

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  December 13, 2007 at 6:13 am

  #759785

  you have deleted from the tables but until you issue a dbcc shrinkfile or shrinkdatabase you will not see a reduction in the size of the database files. Does an sp_spaceused show a large amount of freespace in the database? If so shrink the database, unless disk space is not a problem, in which case leave it as is.

  ---------------------------------------------------------------------