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

 

Script for Backuptype and Backup location for each database..

Hello,

One of my frd asked me to below mentioned requirement.Actually I don't have experience on scripting level.but just I tried with equal join with temporary table.and get output as requirement.
=======AweSome====
So I would like to share these scripts to each one of you...
This is  for finding the "databasename" , "backup location" , "backup type","Backup finished Date" and "Backup size"
1)========================
create table #temp(DBname varchar (30), Path nvarchar(100), type varchar (30),backupfinishdate date)
insert into #temp
select database_name,physical_device_name,type as backuptype,backup_finish_date  from backupset bs,backupmediafamily bm where bs.media_set_id=bm.media_set_id
go
update #temp set type = 'FULL' where type='D'
go
update #temp set type = 'Diff' where type='I'
go
update #temp set type = 'Log' where type='L'
go

select * from #temp
drop table #temp

=========================
select database_name,physical_device_name,
case bs.type
when 'D' then 'Full'
when 'I' then 'Differencial'
When 'L' Then 'Tlog'
end as Backtype,backup_finish_date,
CAST((bs.backup_size/1024/1024) AS int) AS [Size in MB],
 from backupset bs,backupmediafamily bm where bs.media_set_id=bm.media_set_id

Please find below attached is output for above script..

Thanks
ASR