Developers Club geek daily blog

2 years, 4 months ago
Story about msdb of 42 GB in size Recently minute was issued to look why the old test server godlessly braked … I had no relation to it, but I was overcome by sports interest to understand that is with it not so.

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:

D:\SQL_2012\SYSTEM\MSDBData.mdf
D:\SQL_2012\SYSTEM\MSDBLog.ldf

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'

Small outputs...

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: sysmagazine.com@gmail.com.

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.
Best wishes.

comments powered by Disqus