Discussion :

[philwood]

Bonjour,
Je souhaiterais superviser avec mon logiciel de supervision Patrol l'augmentation automatique "autogrow" de mes fichiers de bases de données sous sql server 2008 R2.
J'ai bien trouver sur le net un tas de requêtes pour afficher les dernières opérations d'autogrow à partir du fichier de trace de sql server mais cela est difficilement exploitable pour générer des alertes patrol. D'autre propose d'envoyer un email mais ce n'est pas ce qui m'intéresse.

Bref, est ce qu'il est possible tout simplement de générer une alerte dans l'event log application de windows à chaque opération d'autogrow de sqlserver afin de le superviser via Patrol facilement ?
Je précise que je suis novice sur windows et sql server (je suis d'habitude sur linux postgresql )
Merci d'avance

[dbaffaleuf]

Hello,

Il existe une procédure stockée étendue xp_logevent qui permet d'écrire des messages dans le journal Application. Par exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
exec xp_logevent 666667, 'autogrowth detecte', 'Informational'
GO

Mais il faut pouvoir coupler avec la détection d'autogrowth que l'on va retrouver dans la trace par défaut en effet. Donc c'est faisable avec un procédure stockée par exemple, planifiée dans un job de l'agent SQL.

[philwood]

Merci pour ta réponse,
J'ai pu récupérer ici et là des bout de code sql et créer une procédure stocké qui détecte les opérations d'autogrow et génère un event dans le log windows.

Le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
-- Script d'installation de surveillance des opérations d'autogrow
-- Les messages d'informations sont générés sous l'observateur d'évènements Windows/application  
 
------------------------------------------------------------------------------------------------------------
 
 
-- Création de la notification d'évenement 
 
 
USE [msdb];
GO
 
-- Drop the notification if it exists
IF EXISTS ( SELECT  *
            FROM    sys.server_event_notifications
            WHERE   name = N'CaptureAutogrowEvents' ) 
    BEGIN
        DROP EVENT NOTIFICATION CaptureAutogrowEvents ON SERVER;
    END
 
-- Drop the route if it exists
IF EXISTS ( SELECT  *
            FROM    sys.routes
            WHERE   name = N'AutogrowEventRoute' ) 
    BEGIN
        DROP ROUTE AutogrowEventRoute;
    END
 
-- Drop the service if it exists
IF EXISTS ( SELECT  *
            FROM    sys.services
            WHERE   name = N'AutogrowEventService' ) 
    BEGIN
        DROP SERVICE AutogrowEventService;
    END
 
-- Drop the queue if it exists
IF EXISTS ( SELECT  *
            FROM    sys.service_queues
            WHERE   name = N'AutogrowEventQueue' ) 
    BEGIN
        DROP QUEUE AutogrowEventQueue;
    END
 
--  Create a service broker queue to hold the events
CREATE QUEUE [AutogrowEventQueue]
WITH STATUS=ON;
GO
 
--  Create a service broker service receive the events
CREATE SERVICE [AutogrowEventService]
ON QUEUE [AutogrowEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
 
-- Create a service broker route to the service
CREATE ROUTE [AutogrowEventRoute]
WITH SERVICE_NAME = 'AutogrowEventService',
ADDRESS = 'LOCAL';
GO
 
-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureAutogrowEvents]
ON SERVER
WITH FAN_IN
-- ajouter ou supprimer DATA_FILE_AUTO_GROW selon le besoin de surveillance
FOR DATA_FILE_AUTO_GROW, LOG_FILE_AUTO_GROW	
TO SERVICE 'AutogrowEventService', 'current database';
GO
 
-----------------------------------------------------------------------------------------------------------
 
--  Création de la procédure stockée pour activer la file d'attente
 
IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[SQLskills_ProcessAutogrowEvents]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) 
    DROP PROCEDURE [dbo].[SQLskills_ProcessAutogrowEvents];
GO
 
CREATE PROCEDURE [dbo].[SQLskills_ProcessAutogrowEvents]
    WITH EXECUTE AS OWNER
AS 
    DECLARE @message_body XML;
    DECLARE @message_sequence_number INT;
    DECLARE @dialog UNIQUEIDENTIFIER;
    DECLARE @message VARCHAR(255);
 
    WHILE ( 1 = 1 ) 
        BEGIN
            BEGIN TRANSACTION;
 
-- Receive the next available message FROM the queue
 
WAITFOR
   (
      RECEIVE TOP(1) -- just handle one message at a time
         @message_body=CAST(message_body AS XML)
         FROM dbo.AutogrowEventQueue
   ), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away
 
-- If we didn't get anything, bail out
            IF ( @@ROWCOUNT = 0 ) 
                BEGIN
                    ROLLBACK TRANSACTION;
                    BREAK;
                END 
-- Intérrogation du Data File Auto Grow Event Class
            DECLARE @EventType VARCHAR(128);
            DECLARE @ServerName VARCHAR(128);
            DECLARE @FileName VARCHAR(128);
            DECLARE @PostTime VARCHAR(128);
            DECLARE @DatabaseName VARCHAR(128);
            DECLARE @Duration VARCHAR(128);
            DECLARE @GrowthPages INT;
 
            SELECT @EventType = @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
                                     'varchar(128)') ,
              @Duration = @message_body.value('(/EVENT_INSTANCE/Duration)[1]',
                                     'varchar(128)') ,
              @ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
                                     'varchar(128)') ,
              @FileName = @message_body.value('(/EVENT_INSTANCE/FileName)[1]',
                                     'varchar(128)') ,
              @PostTime = CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)[1]',
                                                         'datetime') AS VARCHAR) ,
              @DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]',
                                     'varchar(128)') ,
              @GrowthPages = @message_body.value('(/EVENT_INSTANCE/IntegerData)[1]',
                                     'int');
 
-- Formatage du message
            SELECT  @message = 'Une opération AutoGrow a eu lieu:'
                    + CHAR(10) + CAST('ServerName: ' AS CHAR(25))
                    + @ServerName + CHAR(10) + CAST('PostTime: ' AS CHAR(25))
                    + @PostTime + CHAR(10)
                    + CAST('FileName: ' AS CHAR(25)) + @FileName
                    + CHAR(10) + CAST('Duration_ms: ' AS CHAR(25)) + @Duration
                    + CHAR(10) + CAST('GrowthSize_KB: ' AS CHAR(25))
                    + CAST(( @GrowthPages * 8 ) AS VARCHAR(20));
 
-- Envoie du message dans l'event log application event ID 17061 (ID message généré par un trigger)
exec xp_logevent 66999, @message , 'Informational';
            COMMIT TRANSACTION;
        END
GO
 
--------------------------------------------------------------------------------------------------
 
--  Activation de file d'attente de la procédure
ALTER QUEUE [AutogrowEventQueue]
   WITH STATUS=ON, 
      ACTIVATION 
         (STATUS=ON,
          PROCEDURE_NAME = [SQLskills_ProcessAutogrowEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);
GO