How to reduce the size of MSDB database

• Narayana_17

  SSC Eights!

  Points: 933

  More actions

  November 3, 2014 at 2:18 am

  #312225

  Hi,

  I am working with SQL server 2005 and the msdb database size has been increased to 16 GB. We need to reduce the size of the database before it reaches to maximum size and the server runs out of space. What i observerd is s that the smail_attachments table is14GB with 972717 rows and sysmail_mailitems table is 790MB with 1051844 rows.

  Please can anyone assist me how to resolve this issue .

  1. can i delete the data using the below script ?

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

  DECLARE @DeleteBeforeDate DateTime

  set @DeleteBeforeDate= '1/20/2010'

  EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate

  EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

  DBCC SHRINKDATABASE (msdb, TRUNCATEONLY);

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

  2. will the system hangup if i use the above script as it is a production server.

  3. if so is there any other way to reduce the size of msdb database .

  Please Help me to resolve this issue.

  Thanks in advance.

  Thanks & Regards

  Narayana

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  November 3, 2014 at 2:43 am

  #1756280

  This thread discusses the issue in depth

  -- Gianluca Sartori

• Narayana_17

  SSC Eights!

  Points: 933

  More actions

  November 3, 2014 at 4:38 am

  #1756293

  Hi ,

  Thanks for the response.

  I have read the post, but just wondering the pros and cons of deleting the data in Sysmail tables using the scripts.

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

  DECLARE @DeleteBeforeDate DateTime

  set @DeleteBeforeDate= '1/20/2010'

  EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate

  EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

  DBCC SHRINKDATABASE (msdb, TRUNCATEONLY);

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

  Is it safe to delete the data with the above scripts without any hangup of server.

  . Any help in this will be highly appreciated.

  Thanks

  Narayana

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  November 3, 2014 at 4:55 am

  #1756295

  Narayana_17 (11/3/2014)

  ---

  I have read the post, but just wondering the pros and cons of deleting the data in Sysmail tables using the scripts.

  The scripts deletes old mail messages sent using database mail. You probably don't need to keep this data.

  Is it safe to delete the data with the above scripts without any hangup of server.

  It doesn't cause the server to hang.

  -- Gianluca Sartori