locked
msdbdata.mdf file is very large RRS feed

  • Question

  • msdbdata.mdf file is very large. I wanted small it. I defined history clean up task for this purpose but when i started this task ,my drive c began to fill and 'sysmaintplan_logdetail' table that caused very larg msdbdata.mdf file none change and msdblog.ldf Was greater than previous.

    can help to me for resolve my problem?

    Thanks for attention you

    Saturday, January 7, 2012 7:07 AM

Answers

All replies

  • Take a look at the following thread which provides an alternative solution to cleaning up the table you have stated:

    http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/d81fe528-5ce5-4d31-b758-01e716bd0ec8/

    As recommended, take a backup of your msdb database before proceeding to make any changes.


    John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter
    • Marked as answer by Maggie Luo Wednesday, February 1, 2012 9:23 AM
    Saturday, January 7, 2012 8:19 AM
  • You may also plan to have the database split up into separate secondary files as well depending on the volume of tables, group them across the other files as well. Advantage is that the data base would be distributed across the files and then have them maintained better and with ease.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Saturday, January 7, 2012 4:33 PM
  • use msdb
    go
    select count(start_time) from sysmaintplan_logdetail with (nolock) where start_time < '1/01/2012'

    Please take the MSDB database before purging the data...

    sp_maintplan_delete_log @oldest_time='1/01/2012'

    NOTE: Deleting a large number of rows is very slow. please show patiencey in completing this process. Beucase of slow process its not recommend deleting large number rows at a time from this table, delete it by lower chenks of data is preferable.. Once the desired data has been deleted... you need to shirnk the MSDB database to retain the disk space

    If you are planning to truncate all the data you can go with John's reference.. it's better to keep atleast one month data

    Thanks,


    Thanks, Satish Kumar.
    Saturday, January 7, 2012 4:37 PM