none
Big msdb database - why? RRS feed

Answers

All replies

  • It may be that old job history is not being cleared out. If you execute these queries what is the result?

     

    -- this will show you how many rows of job history you have

    SELECT COUNT(*) FROM msdb.dbo.sysjobhistory (NOLOCK)

     

    -- this will show you the date of the earliest history entry

    SELECT MIN(run_date) FROM msdb.dbo.sysjobhistory (NOLOCK)

     

    Tuesday, October 4, 2011 2:56 PM
  • The table sysjobhistory in msdb database has 6 records, so this is not it.

     

    Tuesday, October 4, 2011 3:24 PM
  • ok, have you verified that the 100gb size of msdb is actually being used?

    open a new query window and run this:

     

    USE MSDB

    GO

    EXEC sp_spaceused

     

    if the 'unallocated space' figure is high it might just be that the database has been grown at some point and then a purge of some sort took place.

     

    If you really are using a lot of the space, right click on the msdb database in SSMS, and go Reports > Standard Reports > Disk Usage By Top Tables. This will show you all of the largest tables in the database.

     

     

    Tuesday, October 4, 2011 4:21 PM
  • All ocupied space is by table sysmaintplan_logdetail. It is almost 100GB. It has 466 records. What should I do then.
    Tuesday, October 4, 2011 4:40 PM
  • sysmaintplan_logdetail holds the history details and below resources will help you to deal with it

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

    http://johnsterrett.com/tag/sysmaintplan_logdetail/

    Note : Be careful to backup the database before you work on truncate part.

    Thank you,


    Anup | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Tuesday, October 4, 2011 4:49 PM
  • The first article have helped. Thank you!
    Tuesday, October 4, 2011 7:13 PM
  • Great to know.Please mark as Answer so that it will help others while searching solutions.
    Anup | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Tuesday, October 4, 2011 7:20 PM