On one of my SQL Server 2008 Instance, the msdb database sizes around 4 GB. I decided to find out the which table / index occupies major junk on this. I have used query below;
--- Query 1, if there is partition
SELECT TOP 10 SERVERNAME=@@SERVERNAME,DB_NAME=DB_NAME(),TABLE_NAME=OBJECT_NAME(I.ID),INDEX_NAME=I.NAME,INDID,USED, ROWS, SIZE_N_MB = ROUND((USED*8.0/1024.0),2),
ROWMODCTR,STATISTICDT=STATS_DATE(I.ID,INDID)
FROM SYSINDEXES I, SYSOBJECTS O
WHERE I.ID = O.ID
AND INDID IN ( 0,1)
AND XTYPE = 'U'
ORDER BY USED DESC |
--- Query 2 If there are partitions
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) |
The output resulted;
The following 5 big tables takes up 88% of the total database size of 3.8 GB. These 5 tables occupy 3.3 GB on the msdb database.
Query 1: Output
| TABLE_NAME | INDEX_NAME | INDID | USED | ROWS | SIZE_N_MB | ROWMODCTR | STATISTICDT |
| backupfile | PK__backupfi__57D1800A17C286CF | 1 | 233057 | 4438738 | 1820.76 | 2646909 | 2/14/2011 15:57 |
| backupset | PK__backupse__21F79AAB0E391C95 | 1 | 115791 | 1489763 | 904.62 | 228112 | 6/19/2011 6:00 |
| backupmediafamily | PK__backupme__0C13C86F0880433F | 1 | 50738 | 1489616 | 396.39 | 23386 | 7/26/2011 7:00 |
| backupfilegroup | PK__backupfi__760CD67A12FDD1B2 | 1 | 43284 | 2967333 | 338.16 | 1775145 | 2/14/2011 15:57 |
| backupmediaset | PK__backupme__DAC69E4D04AFB25B | 1 | 29250 | 1489616 | 228.52 | 44462 | 7/22/2011 15:43 |
| sysjobhistory | clust | 1 | 3553 | 33177 | 27.76 | 2428 | 7/25/2011 8:14 |
Query 2: Output
| objectName | indexName | totalPages | usedPages | dataPages | totalSpaceMB | usedSpaceMB | dataSpaceMB | Rows |
| backupfile | PK__backupfi__57D1800A17C286CF | 233073 | 233043 | 231653 | 1820 | 1820 | 1809 | 4438738 |
| backupset | PK__backupse__21F79AAB0E391C95 | 100217 | 100200 | 99826 | 782 | 782 | 779 | 1489763 |
| backupfilegroup | PK__backupfi__760CD67A12FDD1B2 | 43289 | 43283 | 43077 | 338 | 338 | 336 | 1489616 |
| backupmediafamily | PK__backupme__0C13C86F0880433F | 39473 | 39459 | 39287 | 308 | 308 | 306 | 2967333 |
| backupmediaset | PK__backupme__DAC69E4D04AFB25B | 18737 | 18728 | 18654 | 146 | 146 | 145 | 1489616 |
Analyze and find out the minimum and maximum date range date available
| SELECT COUNT(*) AS 'TotalRecords',
MIN(backup_start_date) AS 'MinDate',
MAX(backup_start_date) AS 'MaxDate'
FROM dbo.backupset |
Resolution:
The solution is we have to purge these MSDB Backup and Restore history tables
1. The sp_delete_backuphistory system stored procedure takes a single parameter - a cutoff date. Any data older than the supplied date is purged from the msdb tables.
2. The sp_delete_database_backuphistory system stored procedure allows you to delete historical backup data for a specific database. Unfortunately, this procedure does not offer the finer option of choosing a cutoff date. It's all or nothing.
3. sp_purge_jobhistory Removes the history records for a job.
4. Try considering creating indexes on the above said 5 tables, if no of rows are huge.
Source: http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
| use msdb
go
--backupset Create index IX_backupset_backup_set_id on backupset(backup_set_id)
go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)
go
Create index IX_backupset_media_set_id on backupset(media_set_id)
go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date)
go
Create index IX_backupset_backup_start_date on backupset(backup_start_date)
go
--backupmediaset
Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)
go
--backupfile
Create index IX_backupfile_backup_set_id on backupfile(backup_set_id)
go
--backupmediafamily
Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
go
--restorehistory
Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)
go
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)
go
--restorefile
Create index IX_restorefile_restore_history_id on restorefile(restore_history_id)
go
--restorefilegroup
Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)
go |
5. Alternatively, check when the statistics of these tables are last updated. Just run sp_updatestats. Just try sp_delete_backuphistory after the statistics are updated. The result is awesome! Very quick response.
Permanent Resolution
6. Further restrict the Job history growth by setting in SQL Agent –> History tab.
7. Also on the SQL Server Maintenance Plan “History Cleanup Task” can be of helpful
