First of all opened Resource Monitor and looked at a total load. Process of sqlserv.exe loaded the CPU under 100% and created big disk queue which was for 300 … while value above of unit is already considered problem.
In the analysis of disk activity noticed continuous IO operations in msdb:
Looked at the msdb size:
SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024 FROM sys.database_files
also included the hand person mode:
name size space_used ------------ -------------- --------------- MSDBData 42626.000000 42410.374395 MSDBLog 459.125000 6.859375
The data file occupied 42 GB … Having taken a small break I began to understand what the reason of such unhealthy volume of msdb and how to overcome problems with server performance.
Checked resource-intensive requests which were executed on the server:
SELECT r.session_id , db = DB_NAME(r.database_id) , r.[status] , p.[text] --, sql_text = SUBSTRING(p.[text], (r.statement_start_offset / 2) + 1, -- CASE WHEN r.statement_end_offset = -1 -- THEN 2147483647 -- ELSE ((r.statement_end_offset - r.statement_start_offset) / 2) + 1 -- END) , r.cpu_time , r.total_elapsed_time , r.reads , r.writes , r.logical_reads FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) p WHERE r.[sql_handle] IS NOT NULL AND r.session_id != @@SPID ORDER BY logical_reads DESC
On the first place it is proud the system stored procedure was located:
session_id db status text cpu_time total_elapsed_time reads writes logical_reads ---------- -------- -------- ------------------------------------- ----------- ------------------ ------- --------- --------------- 62 msdb running create procedure [sys].[sp_cdc_scan] 111638 6739344 618232 554324 2857923422
Of which of name it is possible to guess that it is about CDC (Change Data Capture) which is applied as means to tracking of the changed data. By CDC it is based on reading the transaction log and always works in an asynchronous mode due to use of Service Broker.
Because of problems in a configuration, in attempt to send Event Notification for Service Broker, the message can not reach destinations and then it is archived in the separate table … Strongly it is boringly told … Generally, if Service Broker is often used, then it is necessary to monitor sys.sysxmitqueue. When in this table there is a permanent gain of data, it either a bug, or we incorrectly use Service Broker.
This request it is possible to return the object list and their size:
USE msdb GO SELECT TOP(10) o.[object_id] , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name , o.[type] , i.total_rows , i.total_size FROM sys.objects o JOIN ( SELECT i.[object_id] , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2)) , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END) FROM sys.indexes i JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id JOIN sys.allocation_units a ON p.[partition_id] = a.container_id WHERE i.is_disabled = 0 AND i.is_hypothetical = 0 GROUP BY i.[object_id] ) i ON o.[object_id] = i.[object_id] WHERE o.[type] IN ('V', 'U', 'S') ORDER BY i.total_size DESC
After execution received the following results:
object_id obj type total_rows total_size ----------- -------------------------------- ---- ------------ ----------- 68 sys.sysxmitqueue S 6543502968 37188.90 942626401 dbo.sysmail_attachments U 70 2566.00 1262627541 dbo.sysmail_attachments_transfer U 35 2131.01 1102626971 dbo.sysmail_log U 44652 180.35 670625432 dbo.sysmail_mailitems U 19231 123.39 965578478 dbo.sysjobhistory U 21055 69.05 366624349 dbo.backupfile U 6529 14.09 727673640 dbo.sysssispackages U 9 2.98 206623779 dbo.backupset U 518 1.88 286624064 dbo.backupfilegroup U 3011 1.84
At once I will tell that we will not disregard all tables in this list. But at first it is necessary to clear sys.sysxmitqueue.
To delete data directly from sys.sysxmitqueue it will not be obtained as this table is a system entity (S). After short searches I found a method how to force SQL Server to clear this table. During creation of new Service Broker all messages associated with the old broker automatically are removed.
USE msdb GO ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE
But before command execution it is strongly recommended to disconnect SQL Server Agent and to transfer SQL Server to Single-User Mode. Removal of the existing messages in all queues of Service Broker occupied me minutes ten. On completion I received the following message:
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
After reset of service SQL Server all problems with performance left … the soul rejoiced and it would be possible to put the end to it. But we will remember that it was not the only big table in msdb. Let's deal with the others …
For those who like to send mail through Database Mail it is necessary to know that SQL Server logs all mailing group and stores in msdb. All mail attachments which go with a letter body there accurately remain … Therefore it is very much recommended to clear this information periodically. It is possible to do it by hands, i.e. to watch what tables it is necessary to clean:
SELECT o.name, p.[rows] FROM msdb.sys.objects o JOIN msdb.sys.partitions p ON o.[object_id] = p.[object_id] WHERE o.name LIKE 'sysmail%' AND o.[type] = 'U' AND p.[rows] > 0
Or to use already ready stored procedures of sysmail_delete_mailitems_sp and sysmail_delete_log_sp:
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -7, GETDATE()) EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DateBefore --, @sent_status = 'sent' EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @DateBefore
History of execution of the SQL Server Agent tasks also remains in msdb. When records in a log become it becomes not strongly convenient to work with it much therefore I try to clean periodically it sp_purge_jobhistory:
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -7, GETDATE()) EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @DateBefore
Still it is necessary to mention, about information on backup copies which are logged in msdb. Old records about the created backups can be deleted with sp_delete_backuphistory:
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -120, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @DateBefore
But it is necessary to remember about one nuance — during removal of the database of record about its backup copies are not removed from msdb:
USE [master] GO IF DB_ID('backup_test') IS NOT NULL BEGIN ALTER DATABASE [backup_test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [backup_test] END GO CREATE DATABASE [backup_test] GO BACKUP DATABASE [backup_test] TO DISK = N'backup_test.bak' GO DROP DATABASE [backup_test] GO SELECT * FROM msdb.dbo.backupset WHERE database_name = 'backup_test'
In my case when bases often are created and removed it can lead to growth of msdb. In a situation when information on backups is not necessary, it can be deleted with hranimky sp_delete_database_backuphistory:
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'backup_test'
The system msdb base is used by many SQL Server components, for example, such as Service Broker, SQL Server Agent and Database Mail. It should be noted that there is no ready plan of service which would consider written above therefore it is important to carry out preventive measures periodically. In my case, after removal of excess information and truncation of the file the msdb size became 200 MB against initial 42 GB.
I hope this post left an instructive story about advantage of permanent administration … not only user, but also system databases.
This article is a translation of the original post at habrahabr.ru/post/273633/
If you have any questions regarding the material covered in the article above, please, contact the original author of the post.
If you have any complaints about this article or you want this article to be deleted, please, drop an email here: firstname.lastname@example.org.
We believe that the knowledge, which is available at the most popular Russian IT blog habrahabr.ru, should be accessed by everyone, even though it is poorly translated.
Shared knowledge makes the world better.