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 4 janvier 2017, par hmira

0PARTAGES

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

Une erreur dans cette actualité ? Signalez-nous-la !

Votre nom : Votre e-mail :

Décrivez l'erreur que vous souhaitez porter à notre connaissance :

Identifiez-vous

Créer un compte

SQL Server - Apprendre une méthode pour lister les fichiers de données (.mdf

.ndf) et de log (.ldf) orphelins, un tutoriel de Hmira

Identifiant
Mot de passe

Mot de passe oublié ?

Identifiez-vous

Créer un compte

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

SQL Server - Apprendre une méthode pour lister les fichiers de données (.mdf

.ndf) et de log (.ldf) orphelins, un tutoriel de Hmira