Archives mensuelles : février 2013

Une procédure stockée pour connaître l’état des fichiers du journal des transactions

Publié le par
Répondre

Ha ! le fichier du journal des transactions, l’option de récupération, son grossissement, … et bien sûr la fameuse transaction restée ouverte qui a provoqué une explosion de la taille de celui-ci, allant jusqu’à remplir le volume disque qui l’héberge : tout autant de paramétrages et d’investigations fastidieuses.

Alors comme je n’aime pas beaucoup répéter plusieurs fois les mêmes opérations sans avoir un script, ou mieux, une procédure stockée sous la main, j’ai créé la suivante, dont voici le script de création :

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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
USE master
GO

-----------------------------------------------------------------
-- Nicolas Souquet - 09/02/2013
-- LA PROCEDURE STOCKEE SUIVANTE EST DONNEE EN TANT QUE TELLE
-- SON UTILISATION ET SA MODIFICATION EST A VOS RISQUES ET PÉRILS
-----------------------------------------------------------------
CREATE PROCEDURE [dbo].[sp__log_space_get]
        @_database_name sysname = NULL
AS
BEGIN
        SET NOCOUNT ON

        DECLARE @sql varchar(256)

        -- Récupération de l'espace disque disponible
        -- sur tous les volumes visibles par l'instance SQL Server
        DECLARE @free_disk_space TABLE
        (
                drive_letter char(1) NOT NULL
                , free_space_MB int NOT NULL
        )

        INSERT  INTO @free_disk_space
        EXEC    master.dbo.xp_fixeddrives

        -- Récupération de l'occupation des fichiers des journaux de transaction
        -- de toutes les bases de données hébergées par l'instance SQL Server
        DECLARE @dbcc_sqlperf_logspace TABLE
        (
                database_name sysname
                , log_size float
                , log_space_used_pct decimal (5,2)
                , status tinyint
        )
        INSERT  INTO @dbcc_sqlperf_logspace
        EXEC    ('DBCC SQLPERF(logspace)')

        -- Récupération de la liste de tous les fichiers virtuels
        -- du fichier du journal des transactions de toutes les bases de données
        -- disponibles, hébergées par l'instance SQL Server
        DECLARE @dbcc_loginfo TABLE
        (
                recovery_unit_id tinyint -- new column in SQL Server 2012
                , fileid tinyint NOT NULL
                , file_size bigint NOT NULL
                , start_offset bigint NOT NULL
                , f_seq_no bigint NOT NULL
                , status tinyint NOT NULL
                , parity tinyint NOT NULL
                , create_LSN varbinary(max) NOT NULL
        )

        -- Cas où l'on souhaite réaliser l'audit du fichier du journal des transactions
        -- de toutes les bases de données hébergées par l'instance SQL Server
        -- Dans ce cas le paramètre d'entrée @_database_name est à NULL
        IF @_database_name IS NULL
        BEGIN
                DECLARE @dbcc_loginfo_all_db TABLE
                (
                        database_name varchar(128)
                        , log_file_id tinyint
                        , vlf_count smallint
                )

                -- Récupération de liste de toutes les bases de données disponibles
                -- (pas une capture iOFFLINE, SINGLE_USER, ou ... SUSPECT ;))
                INSERT  INTO @dbcc_loginfo_all_db
                (
                        database_name
                        , log_file_id
                        , vlf_count
                )
                SELECT          D.name
                                , MF.file_id
                                , 0
                FROM            sys.databases AS D
                INNER JOIN      sys.master_files AS MF
                                        ON D.database_id = MF.database_id
                WHERE           D.source_database_id IS NULL -- ce n'est pas une capture instantanée de base de données
                AND             D.state_desc = 'ONLINE' -- la base de données est disponible
                AND             D.user_access_desc = 'MULTI_USER' -- la base de données est à l'écoute de connexions utilisateur
                AND             MF.type_desc = 'LOG' -- le type de fichier est "journal des transactions"

                -- Pour chaque base de données, récupération de la liste des fichiers virtuels
                -- du fichier du journal des transactions pour toutes les bases de données disponibles
                WHILE EXISTS
                (
                        SELECT  *
                        FROM    @dbcc_loginfo_all_db
                        WHERE   vlf_count = 0
                )
                BEGIN
                        SELECT  TOP 1 @_database_name = database_name
                        FROM    @dbcc_loginfo_all_db
                        WHERE   vlf_count = 0

                        SET @sql = 'DBCC LOGINFO (''' + @_database_name + ''')'

                        INSERT  INTO @dbcc_loginfo
                        EXEC    (@sql)

                        ;WITH
                                CTE AS
                                (
                                        SELECT  fileid
                                                , COUNT(*) AS vlf_count
                                        FROM    @dbcc_loginfo
                                        GROUP   BY fileid
                                )
                        UPDATE          @dbcc_loginfo_all_db
                        SET             vlf_count = C.vlf_count
                        FROM            @dbcc_loginfo_all_db AS DLAD
                        INNER JOIN      CTE AS C
                                                ON DLAD.log_file_id = C.fileid
                        WHERE           DLAD.database_name = @_database_name

                        DELETE  FROM @dbcc_loginfo
                END

                -- Résultat final, avec addition de quelques détails
                SELECT          LS.database_name
                                , D.recovery_model_desc
                                , MF.name AS logical_name
                                , FDS.drive_letter AS transaction_log_file_stored_on
                                , FDS.free_space_MB AS volume_free_space_MB
                                , CAST(LS.log_size AS decimal(38,2)) AS transaction_log_file_size_MB
                                , LS.log_space_used_pct
                                , CAST((LS.log_space_used_pct / 100.0) * LS.log_size AS decimal(38,2)) AS log_used_size_MB
                                , CASE MF.is_percent_growth
                                        WHEN 1 THEN CAST(MF.growth AS varchar(3)) + ' %'
                                        ELSE CAST(MF.growth / 128 AS varchar(20)) + ' MB'
                                END AS growth                          
                                , D.log_reuse_wait_desc
                                , DLAD.vlf_count
                                , MF.physical_name
                FROM            sys.master_files AS MF
                INNER JOIN      @dbcc_sqlperf_logspace AS LS
                                        ON LS.database_name = DB_NAME(MF.database_id)
                INNER JOIN      @free_disk_space AS FDS
                                        ON FDS.drive_letter = LEFT(MF.physical_name, 1) COLLATE database_default
                INNER JOIN      sys.databases AS D
                                        ON D.name = LS.database_name COLLATE database_default
                INNER JOIN      @dbcc_loginfo_all_db AS DLAD
                                        ON DLAD.database_name = D.name COLLATE database_default
                                        AND DLAD.log_file_id = MF.file_id
                                        AND DLAD.database_name = DB_NAME(MF.database_id)
                WHERE           MF.type_desc = 'LOG'
                AND             D.source_database_id IS NULL
                AND             D.state_desc = 'ONLINE'
                AND             D.user_access_desc = 'MULTI_USER'
        END
        ELSE
        BEGIN
                -- Si la base de données que l'on souhaite auditer n'existe pas ==> exception
                IF DB_ID(@_database_name) IS NULL
                BEGIN
                        RAISERROR('The database named ''%s'' does not exist', 16, 1, @_database_name)
                        RETURN
                END
               
                -- récupération de la liste des fichiers virtuels
                -- du fichier du journal des transactions de la base de données choisie
                DECLARE @vlf_count int
                SET @sql = 'DBCC LOGINFO (''' + @_database_name + ''')'

                INSERT  INTO @dbcc_loginfo
                EXEC    (@sql)

                -- Résultat final, avec addition de quelques détails
                ;WITH
                        CTE AS
                        (
                                SELECT  fileid
                                        , COUNT(*) AS vlf_count
                                FROM    @dbcc_loginfo
                                GROUP   BY fileid
                        )
                SELECT          LS.database_name
                                , D.recovery_model_desc
                                , MF.name AS logical_name
                                , FDS.drive_letter AS transaction_log_file_stored_on
                                , FDS.free_space_MB AS volume_free_space_MB
                                , CAST(LS.log_size AS decimal(38,2)) AS transaction_log_file_size_MB
                                , LS.log_space_used_pct
                                , CAST((LS.log_space_used_pct / 100.0) * LS.log_size AS decimal(38,2)) AS log_used_size_MB
                                , CASE MF.is_percent_growth
                                        WHEN 1 THEN CAST(MF.growth AS varchar(3)) + ' %'
                                        ELSE CAST(MF.growth / 128 AS varchar(20)) + ' MB'
                                END AS growth
                                , D.log_reuse_wait_desc
                                , C.vlf_count AS VLF_count
                                , MF.physical_name
                FROM            sys.master_files AS MF
                INNER JOIN      @dbcc_sqlperf_logspace AS LS
                                        ON LS.database_name = DB_NAME(MF.database_id)
                INNER JOIN      @free_disk_space AS FDS
                                        ON FDS.drive_letter = LEFT(MF.physical_name, 1) COLLATE database_default
                INNER JOIN      sys.databases AS D
                                        ON D.name = LS.database_name COLLATE database_default
                INNER JOIN      CTE AS C
                                        ON C.fileid = MF.file_id
                WHERE           MF.type_desc = 'LOG'
                AND             LS.database_name = @_database_name
                AND             D.source_database_id IS NULL
                AND             D.state_desc = 'ONLINE'
                AND             D.user_access_desc = 'MULTI_USER'
        END
END
GO

-- On marque la procédure stockée comme objet système
-- Dès lors, on pourra l'exécuter dans n'importe quel contexte de base de données
EXEC sp_ms_marksystemobject 'sp__log_space_get'

Elle donne un résultat comme le suivant :

sp__log_space_get

à savoir :

Рle nom de la base de donn̩es
– l’option de récupération de la base de données
Рle nom logique du fichier du journal des transactions (utile pour un DBCC SHRINKFILE apr̬s que le journal ait explos̩ en taille)
Рle volume sur lequel est h̩berg̩ le fichier du journal des transactions
– la quantité d’espace disque libre restant sur ce volume
– la taille actuelle du fichier du journal des transactions
– le pourcentage d’utilisation de ce fichier (taille occupée dans le fichier par rapport à la taille totale du fichier)
– la taille équivalente à ce pourcentage
Рle param̬tre de grossissement de ce fichier
– ce qui empêche actuellement la troncature de l’espace occupé dans ce fichier
– le chemin complet du fichier

On peut utiliser cette procédure stockée sous SQL Server 2005, 2008 et 2012.
Elle fonctionne également pour les bases de données ayant plusieurs fichiers du journal des transactions

Bonne gestion des fichiers du journal des transactions !

ElSüket ;)

Reprise d’activité étape par étapes : le poster de Paul Randal

Publié le par
Répondre

Paul Randal est très connu dans la communauté SQL Server puisqu’il publie très régulièrement des détails sur le fonctionnement interne de SQL Server, dont certains ne sont pas documentés, à travers son site SQLSkills.com (son blog et ceux d’autres experts sont référencés en bas à droite de la page).

Paul Randal vient donc de mettre à la disposition de la communauté un poster qui détaille les étapes de la reprise d’activité par un diagramme décisionnel.

Ce diagramme contient certaines étapes qui référencent un numéro, et chaque numéro correspond à un article publié par Paul Randal. Ils sont tous référencés sur cette page.

Toujours bon à avoir sous la main !