In de msdb database worden veel processen van microsoft vastgelegd, onder andere systeembrede stored procedures en ook logging van de SQL agent en database mail. Ongemerkt groeit deze database heel snel heel hard en zit deze database in no-time op enkele tientallen gigabyte grootte. Bij goed SQL server beheer hoort dat niet. Die groei moet in de perken worden gehouden.
Waardoor groeit msdb
De groei van de msdb database kent vele oorzaken. De oorzaken die de meeste groei veroorzaken zijn:
- De historie van SQL agent jobs
- De logs van database mail
- De logs van backups
- De logs van logshipping procedures
Wat kunnen we eraan doen?
Wanneer u nog geen onderhoud op de msdb database heeft gepleegd, is het handig om eerst de volgende punten vast te stellen:
- Hoe groot is de msdb database nu?
- Hoeveel van die ruimte is er al vrij?
- Hoe groot is msdb database op de storage?
- Wat zijn mijn grootste tabellen in de database?
Het vast stellen van de bovenstaande punten kan het makkelijkst via SQL Server Management Studio. Door met de rechtermuis klik op de msdb database te klikken, kan er voor Reports worden gekozen. De relevante rapporten zijn Disk Usage en Disk Usage by Table. Dit geeft een goed beeld van de database. Wanneer de database groter dan 5GB is, wijst dat al op excessieve historische logging. Per tabel kan vervolgens worden gekeken hoeveel records er in de tabel zitten en hoeveel ruimte die tabel in neemt. Met onderstaande query kan bijvoorbeeld worden gekeken hoeveel stappen er per SQL agent job zijn gedraaid in het verleden.
SELECT
sj.name AS [SQL Server Agent job],
count(sjh.step_name) AS [Aantal stappen in de historie]
FROM dbo.sysjobhistory sjh WITH (NOLOCK)
JOIN dbo.sysjobs sj ON sjh.job_id = sj.job_id
GROUP BY sj.name
ORDER BY sj.name;
[/code>]
<strong>Opschonen historische SQL agent jobs</strong>
Voor het opschonen van historische SQL agent jobs wordt de stored procedure <em>sp_purge_jobhistory</em> gebruikt. Deze stored procedure kan de hele historie van één job opruimen of de historie van alle jobs tot en met een bepaalde datum; of een combinatie hiervan. Globaal kan daar onderstaande query voor worden gebruikt
[code language="sql"]
DECLARE @Peildatum datetime = (SELECT DATEADD(MONTH,-1,GETDATE()))
EXECUTE [msdb].[dbo].[sp_purge_jobhistory]
-- @job_name = 'LSRestore_CS-DT1712014_HiX62_Support',
@oldest_date = @Peildatum
Opschonen database mail
Voor het opschonen van database mail worden de stored procedures sysmail_delete_log_sp en sysmail_delete_mailitems_sp gebruikt. Voorbeeld query:
DECLARE @Peildatum datetime = (SELECT DATEADD(MONTH,-1,GETDATE()))
EXECUTE sysmail_delete_log_sp @logged_before = @Peildatum
EXECUTE sysmail_delete_mailitems_sp @sent_before = @Peildatum
Opruimen van de logshipping historie
Voor het opschonen van historische backup data wordt de stored procedure sp_delete_backuphistory gebruikt. Voorbeeld query:
DECLARE @Peildatum datetime = (SELECT DATEADD(MONTH,-1,GETDATE()))
EXECUTE sp_delete_backuphistory @oldest_date = @Peildatum
Opruimen van de logshipping historie
Voor het opschonen van historische logshipping data wordt de stored procedure sp_cleanup_log_shipping_history gebruikt.
LET OP: deze stored procedure draait in de master database! Voorbeeld query:
DECLARE @database nvarchar(100)
DECLARE @agent_id nvarchar(100)
DECLARE @agent_type int
DECLARE @NumberOfRecords int
DECLARE agent_cursor CURSOR FOR
SELECT DISTINCT
database_name,
agent_id,
agent_type,
COUNT(*) AS NumberOfRecords
FROM msdb.dbo.log_shipping_monitor_history_detail
GROUP BY database_name,agent_id,agent_type
-- ORDER BY database_name, agent_id, agent_type
OPEN agent_cursor
FETCH NEXT FROM agent_cursor
INTO @database, @agent_id, @agent_type, @NumberOfRecords
IF @@FETCH_STATUS <> 0
PRINT 'No records'
WHILE @@FETCH_STATUS = 0
BEGIN
execute master.dbo.sp_cleanup_log_shipping_history
@agent_id = @agent_id,
@agent_type = @agent_type;
FETCH NEXT FROM agent_cursor
INTO @database, @agent_id, @agent_type, @NumberOfRecords
END
CLOSE agent_cursor
DEALLOCATE agent_cursor
En dan…? De msdb database is nog steeds niet kleiner geworden (op de storage)
Dat klopt! Verwijderen van data uit tabellen zorgt er alleen maar voor dat er ruimte binnen de database vrijkomt, die dan eerst weer hergebruikt wordt. Met de functie SHRINKFILE kan de database ook op de storage kleiner worden gemaakt. De meningen zijn erg verdeeld of het gebruik van de functie SHRINKFILE wél of niet verstandig is. Deze discussie moet u binnen uw eigen organisatie voeren. Ik leg u in deze bijdrage alleen uit, hoe de functie wordt gebruikt. De query’s:
-- We gaan naar de msdb database
USE msdb;
GO
-- We verkleinen eerst de data file
DBCC SHRINKFILE (N'MSDBData' , 500);
GO
-- We verkleinen dan de log file
DBCC SHRINKFILE (N'MSDBLog' , 200);
GO
-- Daarna is het verstandig de indexen opnieuw op te bouwen
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)";
GO
Het opnieuw indexeren van de tabellen is een belangrijk onderdeel van de procedure om ervoor te zorgen dat eventuele fragmentatie van de database tabellen wordt tegen gegaan.
Nu is de msdb database weer behapbaar. Om dit zo te houden, is mijn advies om alle opruimquery’s in de SQL agent op te nemen en de opruimacties maandelijks uit te voeren. Dat zou uw msdb database in topconditie moeten houden.
