Thursday, October 18, 2012

MSDB size Growing too Bid...

I usually  faced this issue in my environment.Customer has dropped mail about why MSDB database growing to big..After that I logged into the server and checked the MSDB Database Size.It was occupied 20 GB.
Then I started investigation why MSDB database is growing.

Here is the simple information ..I found to resolve the issue.

MSDB Database Size









After some time of investigation.I have checked the sizes of the Objectes in MSDB Database.
Here is the script to find out the Objects sizes details in MSDB Database.
---------
select
        object_name(i.object_id) as ObjectName,
        i.[name] as IndexName,
        sum(a.total_pages) as TotalPages,
        sum(a.used_pages) as UsedPages,
        sum(a.data_pages) as DataPages,
        (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
        (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
        (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
    from
        sys.indexes i
        inner join sys.partitions p
            on i.object_id = p.object_id and i.index_id = p.index_id
        inner join sys.allocation_units a
            on p.partition_id = a.container_id
    group by
        i.object_id,
        i.index_id,
        i.[name]
    order by
        sum(a.total_pages) desc,
        object_name(i.object_id)
    go
----
Output of the object details..
-===============


 Based on the output SYSSSISLOG Object stored lots of data since 2010.Packges executeion information tracked into this object. so that MSDB growing to big.
After That we have created Job for deleted old entries...Then Shrinked the MSDB.Then controlled size.
.
DBCC SHRINKDATABASE('MSDB')

For growing of the MSDB Database may be caused by Database Mail ,backup and Restore history Objects..Because these objects will be tracked backup,restore and Mails operations history in MSDB .So that MSDB database growing too big...

Thanks
ASR

 

No comments:

Post a Comment