none
Huge MSDB data file, cannot shrink

    Question

  • I inherited an instance with a 65GB (yes gigabyte!) msdb.  This is sql2008.  I have verified that I do not have excessive backup history:

    select count(*) from backupfile
    -----------
    252

    select count(*) from backupmediafamily
    -----------
    112

    select count(*) from backupmediaset
    -----------
    112

    select count(*) from backupset
    -----------
    112

    I have verified that I do not have any email attachments taking up space:

    DECLARE @GETDATE datetime
    SET @GETDATE = GETDATE()
    EXECUTE msdb.dbo.sysmail_delete_mailitems_sp  @sent_before = @GETDATE

    (0 row(s) affected)

    I ran sp_spaceused against all of the tables in msdb and the largest two are these, which take up 4MB and 1.4MB:

    sp_spaceused sysssispackages     
    name rows reserved data index_size unused
    -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
    sysssispackages 14 4048 KB 3968 KB 24 KB 56 KB

    sp_spaceused sysjobhistory     
    name rows reserved data index_size unused
    -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
    sysjobhistory 258 1432 KB 864 KB 168 KB 400 KB

    sp_spaceused on msdb gets me this:

    database_name database_size     unallocated space
    msdb                63616.25 MB       6.82 MB

    reserved                data                index_size        unused
    65134776 KB          64540464 KB   584704 KB       9608 KB

    I would prefer to avoid re-creating the msdb database, if possible.

    Any thoughts?

    Tuesday, June 01, 2010 4:28 PM

Answers