Voir le flux RSS

hmira

[Actualité] SQL Server - Lister les fichiers de données (.mdf, .ndf) ou de log (.ldf) orphelins

Note : 2 votes pour une moyenne de 5,00.
par , 30/12/2016 à 19h37 (1433 Affichages)
I - Préambule

Le but de ce billet est de vous présenter une méthode simple, vous permettant de lister les fichiers de données (.mdf, .ndf) ou les fichiers des journaux de transactions (.ldf) orphelins, c.à.d. des fichiers (.mdf, .ndf ou ldf) qui ne sont rattachés à aucune base de données du Serveur.
Ces fichiers orphelins de bases de données occupent généralement beaucoup d’espaces disques inutilement dès lors que les bases de données originelles correspondantes ont déjà fait l’objet de sauvegarde et d’archivage.

L’émergence de ces fichiers orphelins survient généralement après de multiples opérations habituelles d’administration, de type OFFLINE/ONLINE, DETACH/ATTACH appliquées à des bases de données, suivies de déplacements, au niveau de l’OS, des fichiers de bases de données (.mdf, .ndf ou ldf) d’un Serveurs SQL vers un autre, etc.

Ces fichiers (.mdf, .ndf ou ldf) orphelins, peuvent, après vérification minutieuse bien sûr, être définitivement supprimés pour libérer de l'espaces disques.

II - Mise en œuvre

Pour la mise en œuvre de cette méthode, j’utilise principalement :
- La procédure système non documentée master.sys.xp_dirtree Celle-ci permet de lister tous les fichiers et/ou sous répertoires d’un répertoire donné.
- La vue système master.sys.master_files. Celle-ci contient une ligne par fichier de base de données, et ce, pour toutes les bases de données de l’instance SQL Server.

Vous trouverez ci-dessous le script de la procédure dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees accomplissant cette tâche.
Celle-ci attend deux paramètres :
. @pi_DefaultDirectoryData : Le répertoire des fichiers de données (.mdf, ndf), exprimé soit de façon explicite, Exemple : N'C:\SQL\Data'
ou de façon implicite, en affectant la valeur NULL, pour faire référence au répertoire par défaut des fichiers de données défini au niveau de l'instance
. @pi_DefaultDirectoryLog : Le répertoire des fichiers des journaux de transactions (.ldf) ), exprimé soit de façon explicite, Exemple : 'C:\SQL\Log' ou de façon implicite, en affectant la valeur NULL, pour faire référence au répertoire par défaut des fichiers des journaux de transactions défini au niveau de l'instance.

Code SQL : 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
159
160
161
162
163
164
165
166
167
168
-- J'utilise la base master, mais vous pouvez utiliser une autre base de votre choix
USE master
GO
IF NOT EXISTS ( SELECT p.*
				FROM sys.procedures AS p WITH (nolock)
				INNER JOIN sys.schemas AS s WITH (nolock)
				  ON p.schema_id = p.schema_id
				WHERE s.name = N'dbo' AND P.name = N'uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees' AND P.type IN (N'P', N'PC')
			  )
BEGIN
	EXEC dbo.sp_executesql
		 @statement = N'CREATE PROCEDURE dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees
AS
BEGIN
-- !!! "Stub" doit être implémenté !!!
SET NOCOUNT ON;
END; ';
END;
GO
 
-- ----------------------------------------------------------------------------
-- Procédure          : dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees
-- Créateur           : Hamid MIRA
-- Date de création   : 28/12/2016
-- Objet              : Ce script permet de lister les fichiers de données (.mdf, .ndf, etc.)
--                      ou les fichiers de journaux de transactions (.ldf) orphelins, c.à.d. des fichiers (.mdf, .ndf, ldf , etc.)
--                      qui ne sont rattachés à aucune base de données du Serveur.
-- Paramètres :
--   @pi_DefaultDirectoryData : Le répertoire explicite des fichiers de données (.mdf, ndf)  Exemple : N'C:\SQL\Data'
--                              Transmettez NULL pour utiliser le répertoire par défaut des fichiers de données défini au niveau de l'instance
--   @pi_DefaultDirectoryLog  : Le répertoire explicite des fichiers des journaux de transaction  (.ldf) )  Exemple : 'C:\SQL\Log'
--                              Transmettez NULL pour utiliser le répertoire par défaut des fichiers des journaux de transactions défini au niveau de l'instance
-- Exemple d'utilisation :
--   EXEC dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees NULL, NULL
--   EXEC dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees N'C:\SQL\Data', N'C:\SQL\Log'
-- ----------------------------------------------------------------------------
ALTER PROCEDURE dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees
(@pi_DefaultDirectoryData nvarchar(512) = NULL,
 @pi_DefaultDirectoryLog  nvarchar(512) = NULL  )
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @MasterDirectoryData nvarchar(512),
			@MasterDirectoryLog nvarchar(512), 
			@VersionMajor tinyint;
 
	SET @VersionMajor = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128)), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128))) - 1) AS INT);
	PRINT '@VersionMajor=['+CAST(ISNULL(@VersionMajor,-1) AS VARCHAR(2))+']';
 
	IF @pi_DefaultDirectoryData IS NULL
	BEGIN
		-- Version SQL Server 2008 R2 (incluse) et versions antérieures
		IF @VersionMajor <= 10
		BEGIN
			EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @pi_DefaultDirectoryData output
		END
		ELSE
		BEGIN
			-- Version SQL Server 2012 (incluse) et versions supérieures
			SET @pi_DefaultDirectoryData =	CAST(SERVERPROPERTY('INSTANCEDEFAULTDATAPATH') AS nvarchar(512));
		END;
		IF @pi_DefaultDirectoryData IS NULL
		BEGIN
			EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterDirectoryData output
			SET @MasterDirectoryData = SUBSTRING(@MasterDirectoryData, 3, 255)
			SET @MasterDirectoryData = SUBSTRING(@MasterDirectoryData, 1, LEN(@MasterDirectoryData) - CHARINDEX('\', REVERSE(@MasterDirectoryData)))
			SET @pi_DefaultDirectoryData = @MasterDirectoryData;
		END;
	END;
 
	IF @pi_DefaultDirectoryLog IS NULL
	BEGIN
		-- Version SQL Server 2008 R2 (incluse) et versions antérieures
		IF @VersionMajor <= 10
		BEGIN
			EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @pi_DefaultDirectoryLog output
		END
		ELSE
		BEGIN
			-- Version SQL Server 2012 (incluse) et versions supérieures
			SET @pi_DefaultDirectoryLog =  CAST( SERVERPROPERTY('INSTANCEDEFAULTLOGPATH') AS nvarchar(512));
		END;
 
		IF @pi_DefaultDirectoryLog IS NULL
		BEGIN
			EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterDirectoryLog output
			SET @MasterDirectoryLog = SUBSTRING(@MasterDirectoryLog, 3, 255)
			SET @MasterDirectoryLog = SUBSTRING(@MasterDirectoryLog, 1, LEN(@MasterDirectoryLog) - CHARINDEX('\', REVERSE(@MasterDirectoryLog)))
			SET @pi_DefaultDirectoryLog = @MasterDirectoryLog;
		END;
	END;
 
	-- Recadrage éventuel de la valeur @pi_DefaultDirectoryData
	SET @pi_DefaultDirectoryData = RTRIM(LTRIM((@pi_DefaultDirectoryData)));
	SET @pi_DefaultDirectoryData  = LEFT(@pi_DefaultDirectoryData, 2) + REPLACE (RIGHT(@pi_DefaultDirectoryData, len(@pi_DefaultDirectoryData) -2), '\\', '\');
	IF RIGHT(@pi_DefaultDirectoryData, 1) <> '\'
		SET @pi_DefaultDirectoryData = @pi_DefaultDirectoryData + '\';
 
    -- Recadrage éventuel de la valur @pi_DefaultDirectoryLog
	SET @pi_DefaultDirectoryLog = RTRIM(LTRIM(( @pi_DefaultDirectoryLog)));
	SET @pi_DefaultDirectoryLog =  LEFT(@pi_DefaultDirectoryLog, 2) + REPLACE (RIGHT(@pi_DefaultDirectoryLog, len(@pi_DefaultDirectoryLog) -2), '\\', '\');
	IF RIGHT( @pi_DefaultDirectoryLog, 1) <> '\'
		SET @pi_DefaultDirectoryLog = @pi_DefaultDirectoryLog + '\';
 
	PRINT '@pi_DefaultDirectoryData=[' +ISNULL(@pi_DefaultDirectoryData, '{NULL}') +']';
	PRINT '@pi_DefaultDirectoryLog=[' +ISNULL( @pi_DefaultDirectoryLog, '{NULL}') +']';
 
	IF OBJECT_ID('tempdb.dbo.#DirTreeData') IS NOT NULL
		DROP TABLE #DirTreeData
	IF OBJECT_ID('tempdb.dbo.#DirTreeLog') IS NOT NULL
		DROP TABLE #DirTreeLog
 
	-- -----------------------------------------
	CREATE TABLE #DirTreeData(
		Id int identity(1,1) PRIMARY KEY CLUSTERED,
		SubDirectory nvarchar(255),
		Depth smallint,
		FileFlag bit,
		ParentDirectoryID int
		);
	CREATE TABLE #DirTreeLog(
		Id int identity(1,1) PRIMARY KEY CLUSTERED,
		SubDirectory nvarchar(255),
		Depth smallint,
		FileFlag bit,
		ParentDirectoryID int
		);
 
	INSERT INTO #DirTreeData (SubDirectory, Depth, FileFlag)
	EXEC master..xp_dirtree @pi_DefaultDirectoryData, 1, 1; -- Profondeur : 1 (premier niveau), Lister également les fichiers : 1  (Oui)
 
	UPDATE #DirTreeData
		SET SubDirectory = @pi_DefaultDirectoryData + LTRIM(RTRIM(SubDirectory));
 
	IF ISNULL(@pi_DefaultDirectoryLog, '') <> ISNULL(@pi_DefaultDirectoryData, '')
	BEGIN
		INSERT INTO #DirTreeLog(SubDirectory, Depth, FileFlag)
		EXEC master..xp_dirtree  @pi_DefaultDirectoryLog, 1, 1; -- Profondeur : 1 (premier niveau), lister également les fichiers : 1  (Oui)
		UPDATE #DirTreeLog
			SET SubDirectory = @pi_DefaultDirectoryLog + LTRIM(RTRIM(SubDirectory));
	END
 
	;WITH smf AS
	  (SELECT LEFT(RTRIM(LTRIM(physical_name)), 2) + REPLACE (RIGHT(RTRIM(LTRIM(physical_name)), len(RTRIM(LTRIM(physical_name))) -2), '\\', '\') AS physical_name_fmt
       FROM master.sys.master_files WITH (NOLOCK) )
 
	SELECT SubDirectory
	FROM #DirTreeData  dtd
	LEFT OUTER JOIN smf
	   ON smf.physical_name_fmt = dtd.SubDirectory
	WHERE dtd.FileFlag = 1            -- Fichiers uniquement (ignorer les répertoires)
	AND smf.physical_name_fmt IS NULL -- Pas de correspondance du fichier SubDirectory dans master.sys.master_files
	UNION ALL
	SELECT SubDirectory
	FROM #DirTreeLog dtl
	LEFT OUTER JOIN smf
	   ON smf.physical_name_fmt = dtl.SubDirectory
	WHERE dtl.FileFlag = 1            -- Fichier uniquement (ignorer les répertoires)
	AND smf.physical_name_fmt IS NULL --  Pas de correspondance du fichier SubDirectory dans master.sys.master_files
	ORDER BY SubDirectory; 
 
	-- Suppression des tables temporaires 
	IF OBJECT_ID('tempdb.dbo.#DirTreeData') IS NOT NULL
		DROP TABLE #DirTreeData
	IF OBJECT_ID('tempdb.dbo.#DirTreeLog') IS NOT NULL
		DROP TABLE #DirTreeLog
END;
GO

III – Exemple d’utilisation

Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
EXEC dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees NULL, NULL

Résultat : ( deux fichiers orphelins ) :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
SubDirectory
----------------------------------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\DEMO001_Data.mdf
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\DEMO001_Log.ldf
A+

Hamid MIRA

Envoyer le billet « SQL Server - Lister les fichiers de données (.mdf, .ndf) ou de log (.ldf) orphelins » dans le blog Viadeo Envoyer le billet « SQL Server - Lister les fichiers de données (.mdf, .ndf) ou de log (.ldf) orphelins » dans le blog Twitter Envoyer le billet « SQL Server - Lister les fichiers de données (.mdf, .ndf) ou de log (.ldf) orphelins » dans le blog Google Envoyer le billet « SQL Server - Lister les fichiers de données (.mdf, .ndf) ou de log (.ldf) orphelins » dans le blog Facebook Envoyer le billet « SQL Server - Lister les fichiers de données (.mdf, .ndf) ou de log (.ldf) orphelins » dans le blog Digg Envoyer le billet « SQL Server - Lister les fichiers de données (.mdf, .ndf) ou de log (.ldf) orphelins » dans le blog Delicious Envoyer le billet « SQL Server - Lister les fichiers de données (.mdf, .ndf) ou de log (.ldf) orphelins » dans le blog MySpace Envoyer le billet « SQL Server - Lister les fichiers de données (.mdf, .ndf) ou de log (.ldf) orphelins » dans le blog Yahoo

Catégories
SQL Server

Commentaires

  1. Avatar de SQLpro
    • |
    • permalink
    L'inconvénient est que les extensions .mdf et .ldf (il y a aussi .ndf) ne sont pas institutionnelle et que vous pouvez créer une base avec des noms de fichier comportant n'importe quelle extension...

    A +
  2. Avatar de hmira
    • |
    • permalink
    Citation Envoyé par SQLpro
    L'inconvénient est que les extensions .mdf et .ldf (il y a aussi .ndf) ne sont pas institutionnelle et que vous pouvez créer une base avec des noms de fichier comportant n'importe quelle extension...

    A +
    Ta remarque est juste concernant le caractère facultatif, non "institutionnel" des extensions .mdf, .ndf, et .ndf, aussi si tu regardes le détails de la procédure, aucune allusion n'est faite ni à l'extension .mdf, ni à .ldf ni à .ndf.
    La procédure se réfère tout simplement au nom réel du fichier défini au niveau de l'OS, tel qu'il est mentionné dans la vue système master.sys.master_files
    Donc, si par hasard, un fichier de base de données orphelin porte l'extension ".toto" (ce qui est complètement légal) ce dernier sera toute fois mis en exergue par le procédure.
    Pour te dire la vérité, j'ai eu exactement la même réflexion que toi, quand j'ai rédigé cet article, mais, j'ai volontairement, dans le préambule, utilisé les termes .mdf, et .ldf pour cela soit rapidement évocateur pour les développeurs et/ou administrateurs de bases de données, parce que même si cela n'est pas "institutionnel", dans la quasi totalité des cas, on retrouve quand même ces extensions.
    j'aurais dû peut être mentionner que ces extensions n'étaient pas "institutionnelles" et que la procédure est capable de détecter n'importe quel fichier orphelin quelle que soit son extension.
    A+
  3. Avatar de SQLpro
    • |
    • permalink
    Oui, parce que personnellement je conseille fortement de ne jamais mettre ces extensions aux fichiers SQL Server pour des raisons évidentes de sécurité !

    A +