[SOLVED]:MSDB size grown huge

MSBD Size getting increased in GBs

On one day morning I seen c:\ drive of SQL SERVER got reduced to 10 MB free space and after using disk space fan free tool found it was MSDB which has used 19 GB .

As msdb is a system database its very risky to work with this database as it keep all information about database server users/SQL jobs and result sets of all jobs …etc

To identify which table used huge size executed the TSQL command

select object_name(p.object_id), p.index_id, f.name, sum(total_pages)/128, convert(varchar(10), getdate(), 101), count(*) as partitions

from sys.partitions p join

sys.allocation_units a on p.partition_id = a.container_id join

sys.filegroups f on a.data_space_id = f.data_space_id

group by p.object_id, p.index_id, f.name

order by sum(total_pages)/128 desc

 

the above query shown that the major space is used by table BACKUPSET ( 14 GB ).

 

This is table which will get used by SQL jobs created by Maintenance Plan for backup of databases so decided to delete all record from this table

 

But when delete from backupset command fired, SQL server given error of FK constraint for backupfile table and then for backupfilegroup table

 

So followed below steps

  1. Stopped SQL Agent service
  2. As c:\ was not having space first I need to move LDF file on different drive so used command

    ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLOG , FILENAME = ‘e:\msdblog.ldf’ )

  3. Restarted SQL server
  4. Then execute command in sequence as below

    Delete from backupfile

    Delete from backupfilegroup

    delete from restorefile

    delete from restorefilegroup

    delete from restorehistory

    Delete from backupset

    delete from backupmediafamily

    delete from backupmediaset

  5. The msdb database size come to 7 GB after all above process. J

 

Tahnsk

Prashant Deshpande

Advertisements

Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s