Audit des autogrow de fichiers de bases de données avec SQL Server

Il y a quelques jours on m’a posé la question suivante : Est il possible d’être prévenu par mail lorsqu’une extension automatique de fichiers se produit sur une de nos bases de production avec SQL Server 2005 ? La réponse est oui et je monterais dans ce billet qu’il existe au moins deux approches pour réaliser cela. Ceci est également valable pour SQL Server 2008.

Commençons par la première approche qui est la plus simple en terme d’implémentation. Celle-ci utilise les alertes WMI SQL qui se basent sur les notifications d’événements SQL Server. En utilisant la classe DATE_FILE_AUTO_GROW celle-ci permet de détecter le déclenchement des extensions automatique de fichiers de bases de données.

Le script suivant réalise les tâches suivantes :

- Création d’un job SQL Server qui permet d’envoyer un mail de notification avec le nom de la base de données concernée par l’auto extension.
- Création d’une alerte SQL Server qui utilise les WMI et la classe d’événement DATA_FILE_AUTO_GROW. Le job SQL Server créé précédemment sera utilisé comme job de réponse à l’alerte. Les jetons d’étape de travail seront utilisés pour transmettre le nom de la base concernée comme paramètre d’entrée du job de réponse. Pour cela il faut paramétrer l’agent SQL Server pour permettre l’utilisation de ces jetons. (SQL Server Agent > Clic droit et Propriétés > Menu Système d’alerte > Section Remplacement des jetons).

USE [msdb]

GO

DECLARE @jobId BINARY(16);

DECLARE @ReturnCode BIT;

BEGIN TRANSACTION;

– Création du job

EXEC @ReturnCode = msdb.dbo.sp_add_job

 @job_name=N’Maintenance – Capture agrandissement fichier de bases de données’,

 @enabled=1,

 @notify_level_eventlog=0,

 @notify_level_email=0,

 @notify_level_netsend=0,

 @notify_level_page=0,

 @delete_level=0,

 @description=N’Travail de réponse à l »alerte SQL Server : Trap database file growth : Récupération du DatabaseName event et envoi d »un mail’,

 @owner_login_name=N’sa’,

 @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0)

GOTO QuitWithRollback

– Création de l’étape du job

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

 @job_id=@jobId, @step_name=N’etape1′,

 @step_id=1,

 @subsystem=N’TSQL’,

 @command=N’DECLARE @base NVARCHAR(50);

 DECLARE @subject_mail VARCHAR(100);

 DECLARE @body_mail VARCHAR(500);

 DECLARE @destinaires VARCHAR(500);

 SET @base = N »$(ESCAPE_SQUOTE(WMI(DatabaseName))) »;

 SET @subject_mail = »Agrandissement de la base » + @base;

 SET @body_mail = »La base de données » + @base + » a subi une auto extension car le fichier de données était plein »;

 SET @destinataires = »administrateur@domain.com »;
 EXEC msdb.dbo.sp_send_dbmail

 @profile_name = »DBA »,

 @recipients= @destinaires,

 @subject = @subject_mail ,

 @body = @body_mail,

 @body_format = »TEXT »;’,

 @database_name=N’msdb';

IF (@@ERROR <> 0 OR @ReturnCode <> 0)

GOTO QuitWithRollback

– Mise à jour job avec l’étape créée précédemment

EXEC @ReturnCode = msdb.dbo.sp_update_job

 @job_id = @jobId,

 @start_step_id = 1;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)

GOTO QuitWithRollback

– Ajout du job au serveur local

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver

 @job_id = @jobId,

 @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0)

GOTO QuitWithRollback

– Ajout d’une alerte et mise à jour avec le job de réponse

EXEC msdb.dbo.sp_add_alert @name=N’Alert & Job Trap database file growth’,

 @message_id=0,

 @severity=0,

 @enabled=1,

 @delay_between_responses=0,

 @include_event_description_in=0,

 @wmi_namespace=N’\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER’,

 @wmi_query=N’SELECT * FROM DATA_FILE_AUTO_GROW’,

 @job_id= @jobId

COMMIT TRANSACTION;

RETURN;

QuitWithRollback:

 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;

 RETURN;

Une fois l’alerte et le job créé et si une extension automatique d’un fichier de bases de données se produit alors un mail de notification sera automatiquement envoyé :

Cependant cette première approche possède un inconvénient majeur. En effet si l’on est bien prévenu d’une auto extension il nous manque, en revanche, certaines données qui peuvent être intéressantes comme le ou les fichiers de données concernés et la taille de l’extension par exemple La deuxième approche consiste à prendre un instantané des tailles de fichiers de l’ensemble des bases de données et de comparer cet instantané à intervalle régulier par l’intermédiaire d’un job SQL Server avec la taille actuelle de ces mêmes fichiers. Si une augmentation est constatée alors une extension a eu lieu. Il est également possible d’utiliser cette deuxième approche comme extension de la première. Le scénario serait le suivant :

- Une extension d’un fichier déclenche un événement détecté par l’alerte.
- L’alerte exécute un job de réponse qui récupère la taille actuelle de l’ensemble des fichiers de la base de données et les compare avec l’instantané pris ultérieurement et possédant les mêmes informations. Il suffit alors de récupérer les fichiers qui ont vu leur taille augmentée et de lancer une notification par mail.
- Enfin il faudra rafraîchir cet instantané pour prendre en compte les nouvelles tailles de fichier.

Commençons par implémenter une table de travail qui permet de mémoriser l’instantané des tailles de fichiers à un instant donné.

CREATE TABLE dbo.audit_file_size
(
date_maj DATETIME NOT NULL,
database_name SYSNAME NOT NULL,
logical_name SYSNAME NOT NULL,
physical_name NVARCHAR(260) NOT NULL,
size_in_kb INT
);

Une première image de référence est nécessaire pour réaliser les futures comparaisons de taille des fichiers lors des opérations d’extension :

INSERT INTO dbo.audit_file_size
SELECT
    GETDATE(),
    d.name,
    m.name,
    m.physical_name,
    m.size * 8
FROM sys.master_files AS m
INNER JOIN sys.databases AS d
ON m.database_id = d.database_id
AND m.[type] = 0;
GO

Il suffit ensuite de modifier le job créé avec la première méthode en insérant ce nouveau script :

SET NOCOUNT ON;

– Création table de travail pour comparaison des tailles de fichiers

IF EXISTS (SELECT 1 FROM tempdb.sys.objects

WHERE object_id = OBJECT_ID(‘tempdb..##result_auto_grow’))

DROP TABLE ##result_auto_grow;

CREATE TABLE ##result_auto_grow

(

database_name SYSNAME NOT NULL,

logical_name SYSNAME NOT NULL,

physical_name NVARCHAR(260) NOT NULL,

size_in_kb_before INT NOT NULL,

size_in_kb_after INT NOT NULL,

size_in_kb_diff INT NOT NULL,

free_space_percent DECIMAL(5,2) NOT NULL

);

DECLARE @t_spaceused TABLE

(

database_name SYSNAME NOT NULL,

logical_name SYSNAME NOT NULL,

physical_name NVARCHAR(260) NOT NULL,

size_in_kb INT NOT NULL,

free_space_percent DECIMAL(5,2) NOT NULL

);

DECLARE @sql VARCHAR(1000);

– Récupération informations actuelles des tailles et espace libre

– dans les fichiers de données

SET @sql = ‘USE [‘ + ‘?’ + ‘];

SELECT

 DB_NAME(),

 name,

 physical_name,

 size * 8.0,

 CAST((1 – FILEPROPERTY(name, »SpaceUsed ») * 1.0 / (size * 1.0)) * 100 AS DECIMAL(5,2))

FROM sys.database_files

WHERE type = 0;';

INSERT INTO @t_spaceused

EXEC sp_MSForEachDB @sql;

– Opération de comparaison des tailles de fichiers actuelles

– avec l’instantanné

INSERT INTO ##result_auto_grow

SELECT

 a.database_name,

 a.logical_name,

 a.physical_name,

 a.size_in_kb,

 b.size_in_kb,

 b.size_in_kb – a.size_in_kb,

 b.free_space_percent

FROM dbo.audit_file_size AS a

INNER JOIN @t_spaceused AS b

ON a.database_name = b.database_name

AND a.logical_name = b.logical_name

WHERE b.size_in_kb – a.size_in_kb > 0;

– Envoi mail si extension détectée

IF @@ROWCOUNT > 0

BEGIN

 DECLARE @tableHTML NVARCHAR(MAX);

 SET @tableHTML =

 N'Rapport expansion automatique des fichiers de bases de données’ +

 N' ’ +

 N'<table border="0" style="border: 2px solid; bordercolor: black; cellSpacing=10; cellPadding=10; ‘ +

 N’font-family:Verdana; font-size:10px;">’ +

 N'<tr><td>Database</td><td>File</td><td>size before (KB)</td>’ +

 N'<td>size after (KB)</td><td>diff (KB)</td>’ +

 N'<td>Free space (%)</td></tr>’ +

 CAST ((SELECT td = database_name , »,

 td = physical_name, »,

 td = size_in_kb_before, »,

 td = size_in_kb_after, »,

 td = size_in_kb_diff, »,

 td = free_space_percent

 FROM ##result_auto_grow

 FOR XML PATH(‘tr’), TYPE

 ) AS NVARCHAR(MAX) ) +

 N'</table>';

 EXEC msdb.dbo.sp_send_dbmail

 @profile_name = ‘DBA’,

 @recipients = ‘administrateur@domain.com‘,

 @subject = ‘Alerte : Expansion automatique de fichier de bases de données’,

 @body = @tableHTML,

 @body_format = ‘HTML';

END;

– Rafraîchissement instantanné des tailles de fichiers

TRUNCATE TABLE dbo.audit_file_size;

INSERT INTO dbo.audit_file_size

SELECT

 GETDATE(),

 d.name,

 m.name,

 m.physical_name,

 m.size * 8

FROM sys.master_files AS m

INNER JOIN sys.databases AS d

ON m.database_id = d.database_id

AND m.[type] = 0;

Le résultat est le suivant lorsqu’une extension est détectée :

Bon audit d’extension de fichiers de base de données !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon

Identifiez-vous

Créer un compte

David Barbarin

MVP DataPlatform – MCM SQL Server

Audit des autogrow de fichiers de bases de données avec SQL Server

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?