none
MSDB is too big RRS feed

  • Question

  • My MSDB is too big [about 14 GB]. When I closely looked all tables, here is what I found -

     

    Top 3 biggest tables are -

    sysmaintplan_logdetail - rows- 124923  - data - 13375456 KB
    DTA_Tuninglog - 181658 rows - data - 163248 KB
    sysmail_mailitems - records - 14227      data - 126208 KB

     

    I am not a DBA, so if any one can explain in simple terms how I can cleanup this maintenance plan log detail without increasing the size of tempdb? How can I stop this database growing in the future?

     

    I have only a gig of space left on that drive.

     

    Thanks in advance

    Tuesday, December 18, 2007 9:54 PM

Answers

  • try this code

     

    before it's recommended to backup MSDB database.

     

    Code Snippet

    ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];

    ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];

     

    truncate table msdb.dbo.sysmaintplan_logdetail;

    truncate table msdb.dbo.sysmaintplan_log;

     

    ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])

    REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);

     

    ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])

    REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

     

     

    Thursday, December 20, 2007 3:12 PM
  • it's ok, you only clean up data so far.

     

    how you haven't posted the result header, only the result detail.

    I imagine this scenario:

     

    database_name = msdb
    database_size =  14339.63 MB

    unallocated_space = 13660.91 MB

     

    if it's right and now you need to release space that was allocated, you'll use the DBCC SHRINKFILE command.

     

    Example:

    This command shrink MSDBData file to 1Gb.

     

    DBCC SHRINKFILE (MSDBData, 1000)

    Friday, December 21, 2007 10:37 AM

All replies

  • You can use this procedure: sp_maintplan_delete_log

     

    Check out: http://www.elsasoft.org/SUMMER.msdb/sp_dbospmaintplandeletelog.htm

    Tuesday, December 18, 2007 11:44 PM
  • Pls check the below link, http://www.mssqltips.com/tip.asp?tip=1231and ensure that the job history log is'nt huge enough. Also refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201. Change the file growth pattern to MB instead of %. Also change the recovery model of msdb to simple if it was full.

    - Deepak
    Wednesday, December 19, 2007 12:27 AM
    Moderator
  • Alex,

     

    I tried to use sp_maintplan_delete_log, but after running for close to an hour, it's erroring out complaining about the lack of hard disk space [I've only 700 MB left on C drive]. I believe either MSDB log or TEMPDB is growing when I tried to do the delete. So I may have to adopt a different method. If I can wipe out entire MSDB without logging anything into any other databases, that is the method I'm looking for.

     

    If you know of anything that I can do, please let me know.

     

    Thanks

    -Binoy

    Thursday, December 20, 2007 2:55 PM
  • Deepak,

     

    I read these posts before, but thought that they were mostly talking about SQL server 2000. My database is on SQL server 2005. Even then, I may try some of what they were talking about. I will try all avenues to get this resolved ASAP.

     

    Thanks

    -Binoy

    Thursday, December 20, 2007 3:00 PM
  • try this code

     

    before it's recommended to backup MSDB database.

     

    Code Snippet

    ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];

    ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];

     

    truncate table msdb.dbo.sysmaintplan_logdetail;

    truncate table msdb.dbo.sysmaintplan_log;

     

    ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])

    REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);

     

    ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])

    REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

     

     

    Thursday, December 20, 2007 3:12 PM
  • Alex,

     

    Great! That was a fast response.

     

    Since this is a production server, I will try it tonight and will let you know.

    Thursday, December 20, 2007 4:42 PM
  • Alex,

     

    I tried these, still msdbdata.mdf is showing 14.5 GB.

     

    sp_spaceused reports-

    msdb         14339.63 MB        13660.91 MB

     

    For msdb, recovery mode is 'simple' and it grows by MB (not %) still sysmaintplan_logdetail is 45 MB after truncating it an hour back.

     

    I looked at all tables in msdb. Largest one is DTA_tuninglog at 165 MB.

     

    Am I missing anything?

     

    Thanks

    -Binoy

    Friday, December 21, 2007 4:57 AM
  • k... do one thing... run the following command and see which table is taking more space and post back the result

     

    select 'Exec SP_SpaceUsed  '+Name from sysobjects where xtype='u'

     

    Madhu

    Friday, December 21, 2007 9:52 AM
    Moderator
  • it's ok, you only clean up data so far.

     

    how you haven't posted the result header, only the result detail.

    I imagine this scenario:

     

    database_name = msdb
    database_size =  14339.63 MB

    unallocated_space = 13660.91 MB

     

    if it's right and now you need to release space that was allocated, you'll use the DBCC SHRINKFILE command.

     

    Example:

    This command shrink MSDBData file to 1Gb.

     

    DBCC SHRINKFILE (MSDBData, 1000)

    Friday, December 21, 2007 10:37 AM
  • Madhu,

     

    Here are the tables with atleast one record-

     

    database_name rows reserved data index_size unused
    sysmaintplan_logdetail 2097 228000 KB 227936 KB 16 KB 48 KB
    DTA_tuninglog 181658 165336 KB 163248 KB 56 KB 2032 KB
    sysmail_mailitems 14311 128000 KB 126984 KB 216 KB 800 KB
    backupfile 216767 90592 KB 89752 KB 608 KB 232 KB
    backupset 107372 96768 KB 61696 KB 34768 KB 304 KB
    backupmediafamily 107372 62848 KB 23808 KB 38744 KB 296 KB
    backupfilegroup 107372 13280 KB 12848 KB 120 KB 312 KB
    backupmediaset 107372 48960 KB 11232 KB 37376 KB 352 KB
    sysjobactivity 47254 9232 KB 8536 KB 88 KB 608 KB
    sysdtspackages90 23 8144 KB 8072 KB 24 KB 48 KB
    sysmail_log 7066 2712 KB 1968 KB 32 KB 712 KB
    sysjobs 1771 3176 KB 1416 KB 536 KB 1224 KB
    sysjobsteps 1771 3168 KB 1240 KB 496 KB 1432 KB
    sysjobhistory 999 1776 KB 832 KB 184 KB 760 KB
    sysjobservers 1771 1744 KB 816 KB 128 KB 800 KB
    DTA_reports_query 797 920 KB 800 KB 16 KB 104 KB
    DTA_input 2 616 KB 544 KB 8 KB 64 KB
    DTA_reports_index 5527 864 KB 528 KB 208 KB 128 KB
    DTA_reports_column 10158 728 KB 464 KB 160 KB 104 KB
    sysschedules 1784 968 KB 256 KB 16 KB 696 KB
    DTA_reports_table 3176 328 KB 216 KB 72 KB 40 KB
    sysjobschedules 1784 392 KB 128 KB 16 KB 248 KB
    DTA_reports_querycolumn 2937 272 KB 88 KB 104 KB 80 KB
    sysmail_send_retries 793 200 KB 88 KB 16 KB 96 KB
    DTA_output 2 88 KB 80 KB 8 KB 0 KB
    DTA_reports_queryindex 1251 104 KB 40 KB 64 KB 0 KB
    DTA_reports_querytable 867 72 KB 24 KB 48 KB 0 KB
    DTA_reports_querydatabase 833 72 KB 24 KB 48 KB 0 KB
    restorefilegroup 9 32 KB 24 KB 8 KB 0 KB
    MSdbms_datatype_mapping 325 40 KB 24 KB 16 KB 0 KB
    MSdbms_map 248 40 KB 24 KB 16 KB 0 KB
    restorehistory 9 48 KB 16 KB 32 KB 0 KB
    DTA_reports_indexcolumn 212 32 KB 8 KB 24 KB 0 KB
    MSdbms_datatype 141 16 KB 8 KB 8 KB 0 KB
    DTA_reports_database 134 32 KB 8 KB 24 KB 0 KB
    syssessions 44 32 KB 8 KB 24 KB 0 KB
    syscategories 22 16 KB 8 KB 8 KB 0 KB
    sysmaintplan_log 18 16 KB 8 KB 8 KB 0 KB
    syssubsystems 11 32 KB 8 KB 24 KB 0 KB
    sysmaintplan_subplans 8 16 KB 8 KB 8 KB 0 KB
    MSdbms 7 16 KB 8 KB 8 KB 0 KB
    sysmail_configuration 7 16 KB 8 KB 8 KB 0 KB
    DTA_progress 6 16 KB 8 KB 8 KB 0 KB
    sysdtscategories 3 32 KB 8 KB 24 KB 0 KB
    sysmail_profile 2 32 KB 8 KB 24 KB 0 KB
    syscachedcredentials 2 16 KB 8 KB 8 KB 0 KB
    sysmail_profileaccount 2 16 KB 8 KB 8 KB 0 KB
    sysdtspackagefolders90 2 48 KB 8 KB 40 KB 0 KB
    sysoperators 1 32 KB 8 KB 24 KB 0 KB
    sysmail_principalprofile 1 16 KB 8 KB 8 KB 0 KB
    sysmail_account 1 32 KB 8 KB 24 KB 0 KB
    sysdbmaintplans 1 16 KB 8 KB 8 KB 0 KB
    sysmail_servertype 1 16 KB 8 KB 8 KB 0 KB
    sysmail_server 1 16 KB 8 KB 8 KB 0 KB

     

    Please let me know if you need any more info.

     

    Thanks

    -Binoy

    Saturday, December 22, 2007 12:51 AM
  • Alex,

     

    I believe I tried that last night, but will try it again tonight and will post the results tomorrow.

     

    Sorry for not posting headers.

     

    database name    database size   unallocated space   reserved           data             index size     unused
    msdb                   14353.19 MB    13364.29 MB         995160 KB       866936 KB   115888 KB    12336 KB

     

    Thanks again for helping me out

    -Binoy

    Saturday, December 22, 2007 12:57 AM
  • Alex,

     

    This time it worked. msdb is now down to 1.3GB.

     

    How can I keep msdb down to somewhere around 2GB? Is it good to select maximum database size as 2GB?

     

    Right now recovery mode is 'simple' and database is growing in MB rather than %.

     

    We have around 60 databases that are getting backed up every day and their transaction logs every hour.

     

    Any ideas to keep msdb to it's best minimum will be appreciated.

     

    Deepak, Madhu - thanks for sharing your valuable time to help me out.

     

    Thanks

    -Binoy

    Saturday, December 22, 2007 9:50 PM
  •  

    How can I keep msdb down to somewhere around 2GB? Is it good to select maximum database size as 2GB?

    [Answer] it's possible, but first you need to analyze the average size and set the maximum database size. If you set 2Gb should be not enough.

     

    Right now recovery mode is 'simple' and database is growing in MB rather than %.

    [Answer] it's better growing up in MB rather than %. an example could be 200Mb.

     

    We have around 60 databases that are getting backed up every day and their transaction logs every hour.

    Any ideas to keep msdb to it's best minimum will be appreciated.

    [Answer] Review MSDB backup plan and monitor the size of the tables mentioned before, you could schedule the clean up and shrink task per week, for example.

    Monday, December 24, 2007 9:55 PM
  • For cleaning up your MSDB tables prefixed with DTA_ simply opening up the Database Engine Tuning Advisor and delete the old sessions.
    Wednesday, April 7, 2010 6:35 PM
  • Thanks WebWillam ,

    That helped .

    Thursday, June 23, 2011 4:02 AM
  • Thanks  Alex

    Your script worked for  me SQL Server 2008 r2

    Ron

    Wednesday, October 10, 2012 1:07 PM