System database msdb size in huge! How to purge MSDB Backup and Restore History?

 

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

SQL Server Performance Checklist

Documented the SQL Server Performance Checklist in Excel Spreadsheet format .

SQL Server Performance baseline

1 Find Objects using Cursors
-------------------------------------

SELECT object_name(id) FROM syscomments
WHERE text LIKE '%DECLARE%CURSOR%'

2. Find views using Views
-------------------------------------------

Select * from sysComments where text like '%from v%' and type = 'V'

3. TempDB Usage History at instance level
----------------------------------------------
SELECT getdate(),
SUM(user_object_reserved_page_count) * 8 as user_objects_kb,
SUM(internal_object_reserved_page_count) * 8 as internal_objects_kb,
SUM(version_store_reserved_page_count) * 8 as version_store_kb,
SUM(unallocated_extent_page_count) * 8 as freespace_kb
FROM sys.dm_db_file_Space_Usage
where database_id = 2

4. TempDB Usage History at object level
--------------------------------------------

SELECT
obj.objectid as [Object_Id],
getdate() as SampleDateTime,
sum(tmp.user_objects_alloc_page_count) AS user_objects_alloc_page_count,
sum(tmp.user_objects_dealloc_page_count) AS user_objects_dealloc_page_count,
sum(tmp.internal_objects_alloc_page_count) AS internal_objects_alloc_page_count,
sum(tmp.internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage AS tmp
LEFT OUTER JOIN sys.dm_exec_requests AS req
ON tmp.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS obj
WHERE tmp.session_id > 50
AND obj.objectid is not null
Group BY obj.ObjectId

5. Object Block history
-----------------------------------
SELECT s.object_id
, SampleDateTime = getdate()
, indexname=i.name, i.index_id
, [block_pct]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_ms = row_lock_wait_in_ms
, [avg_row_lock_waits_ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s
,sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
And row_lock_wait_count <> 0

6. CPU Usage History
----------------------------
SELECT
objectid as [Object_Id]
, getdate() As SampleDateTime
, total_cpu_time
, total_execution_count
, number_of_statements
FROM (
SELECT
qs.sql_handle
, sum(qs.total_worker_time) as total_cpu_time
, sum(qs.execution_count) as total_execution_count
, count(*) as number_of_statements
FROM
sys.dm_exec_query_stats qs
GROUP BY qs.sql_handle ) dt
Cross Apply sys.dm_exec_sql_text(dt.sql_handle) eqp
WHERE ObjectId is not null

7. I/O Usage History
-----------------------------------
Select ObjectId As [Object_ID], GetDate() as SampleDateTime
, (total_logical_reads/Cast(execution_count as Decimal(38,16))) as avg_logical_reads
, (total_logical_writes/Cast(execution_count as Decimal(38,16))) as avg_logical_writes
, (total_physical_reads/Cast(execution_count as Decimal(38,16))) as avg_physical_reads
, Execution_Count
from (
select
Sum(total_logical_reads) as total_logical_reads
, Sum(total_logical_writes) as total_logical_writes
, Sum(total_physical_reads) as total_physical_reads
, Sum(execution_count) as execution_count
, sh.objectid
from sys.dm_exec_query_stats
Cross Apply sys.dm_exec_sql_text(sql_Handle) sh
Where objectId is not null
Group By objectid
) SubQry

8. Table Data Growth volume History
------------------------------------------
DECLARE @START_DATE datetime,
@END_DATE datetime

SET @START_DATE = getdate()
SET @END_DATE = getdate()

IF @START_DATE = @END_DATE
SET @END_DATE = Convert(varchar(10), DateAdd(day, 1, @END_DATE), 101)

SELECT startdt.object_id, object_name(startdt.object_id) as [Object_Name], startdt.[RowCount] as Starting_Row_Count,
enddt.[RowCount] as Ending_Row_Count,
pct_Growth = case when startdt.[rowcount] = 0 then 0
else (enddt.[rowCount] - startdt.[rowCount])/Convert(Decimal(38, 16), startdt.[rowcount])
end
FROM Benchmark_Table_DataVolumeHistory startdt
INNER JOIN (
Select object_id, min(SampleDateTime) as minSampleDateTime,
max(SampleDateTime) As maxSampleDateTime
FROM Benchmark_Table_DataVolumeHistory
WHERE SampleDateTime >= Convert(varchar(10), Getdate(), 101)
And SampleDateTime < @END_DATE
GROUP BY object_Id
) sub
ON startdt.object_id = sub.object_id
and startdt.SampleDateTime = minSampleDateTime
INNER JOIN BenchMark_Table_DataVolumeHistory enddt
ON enddt.object_id = sub.object_id
and enddt.SampleDateTime = maxSampleDateTime


SELECT UseCaseDesc, startdt.object_id, object_name(startdt.object_id) as [Object_Name], startdt.[RowCount] as Starting_Row_Count,
enddt.[RowCount] as Ending_Row_Count,
pct_Growth = case when startdt.[rowcount] = 0 then 0
else (enddt.[rowCount] - startdt.[rowCount])/Convert(Decimal(38, 16), startdt.[rowcount])
end
FROM Benchmark_Table_DataVolumeHistory startdt
INNER JOIN (
Select uc.UseCaseDesc, hist.object_id, min(SampleDateTime) as minSampleDateTime,
max(SampleDateTime) As maxSampleDateTime
FROM Benchmark_Table_DataVolumeHistory hist
INNER JOIN Benchmark_useCase_Object_assn ua
ON hist.object_id = ua.object_id
inner join benchmark_usecase uc
on ua.usecaseid = uc.usecaseid
WHERE SampleDateTime >= Convert(varchar(10), Getdate(), 101)
And SampleDateTime < @END_DATE
GROUP BY hist.object_Id, usecasedesc
) sub
ON startdt.object_id = sub.object_id
and startdt.SampleDateTime = minSampleDateTime
INNER JOIN BenchMark_Table_DataVolumeHistory enddt
ON enddt.object_id = sub.object_id
and enddt.SampleDateTime = maxSampleDateTime

Missing performance counters X64 OS with SQL2000 and SQL 2005

If you’re using x64 Windows and x64 SQL Server you shouldn’t have any issues. System Monitor will by default have the correct counters loaded and available for you to view and the same applies to IA64 Windows and IA64 SQL Server deployments.

However, if you’re using SQL Server in a Windows-On-Windows (WOW) mode on x64 (which is to say that x64 Windows is emulating a 32-bit environment to facilitate you running an x86 instance of SQL Server), When you start System Monitor on the x64 host, you’ll find that none of the counters relating to the 32-bit SQL Server instance are available. This is be cause on an x64 host you’ll be running an x64 version of System Monitor by default.

In the logfile I overlooked Event ID 1021:

Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a 64-bit environment. Contact the file vendor to obtain a 64-bit version. Alternatively, you can open the 32-bit extensible counter DLL by using the 32-bit version of Performance Monitor. To use this tool, open the Windows folder, open the Syswow64 folder, and then start Perfmon.exe.

Resolution:
Start C:\WINDOWS\SysWOW64\Perfmon.exe, this is the 32-bit version, and all the counters are there.


Monitoring the 32-bit SQL Server instance in the scenario will require running a 32-bit instance of System Monitor, and this can be achieved by running the following

mmc /32perfmon.msc

Once the 32-bit MMC console has started with the Perfmon snap-in, the 32-bit SQL Server counters should be available for addition to your log.

Troubleshooting SQL Server 2005/2008 Performance and Scalability Flowchart

Flowchart on Troubleshooting SQL Server 2005/2008 Performance and Scalability Flowchart is available on SQL Server Customer Advisory Team ( SQL CAT ) site.

http://sqlcat.com/files/folders/280/download.aspx