SQL Server - Apprendre une méthode pour lister les fichiers de données (.mdf
.ndf) et de log (.ldf) orphelins, un tutoriel de Hmira

Le , par hmira, Membre éprouvé
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
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
EXEC dbo.uspListeFichiersDataOuLogOrphelinsDeBasesDeDonnees NULL, NULL

Résultat : ( deux fichiers orphelins ) :

Code : Sélectionner tout
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


Vous avez aimé cette actualité ? Alors partagez-la avec vos amis en cliquant sur les boutons ci-dessous :


 Poster un commentaire

Avatar de SQLpro SQLpro - Rédacteur https://www.developpez.com
le 23/01/2017 à 18:08
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 +
Avatar de hmira hmira - Membre éprouvé https://www.developpez.com
le 28/01/2017 à 21:20
Citation Envoyé par SQLpro;bt3716
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+
Avatar de SQLpro SQLpro - Rédacteur https://www.developpez.com
le 29/01/2017 à 11:54
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 +
Offres d'emploi IT
ATS Rennes- Concepteur développeur NTIC (H/F)
Atos Technology Services - Bretagne - Rennes (35000)
Consultant testing confirmé h/f
Sogeti France - Aquitaine - Bordeaux (33000)
Développeur fan de scala / spark
Urban Linker - Ile de France - Paris (75011)

Voir plus d'offres Voir la carte des offres IT
Contacter le responsable de la rubrique Accueil