Verwaiste SQL Server Dateien finden

5. August 2015

Manchmal gibt’s nach dem Aufräumen von nicht mehr benötigten SQL Server Datenbanken auf einer Entwicklungsumgebung nicht viel mehr freien Platz auf der Festplatte als vorher. Das kann daran liegen, dass SQL Server beim Droppen der Offline Datenbanken die dazugehörigen MDF/NDF/LDF-Dateien nicht löscht. Das Verhalten ist auch dokumentiert. 
Mit folgendem Skript findet man die MDF/NFD/LDF-Dateien, die im default Datenverzeichnis vorhanden sind, aber von keiner Datenbank auf der aktuellen Instanz verwendet werden*

DECLARE @BasePath NVARCHAR(1000) = CONVERT(NVARCHAR(1000), 

    SERVERPROPERTY('InstanceDefaultDataPath'));

 

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL

    DROP TABLE #DirectoryTree;      

 

CREATE TABLE #DirectoryTree

(

     filename NVARCHAR(512)

    ,depth INT

    ,isfile BIT

); 

 

/*Eine Alternative zur standardmäßig mitgelieferten, aber 

undokumentierten xp_dirtree ist z.B. eine eigene SQLCLR Implementierung.

In der konstenpflichtigen Version von SQL# (http://www.sqlsharp.com/)

ist so eine Implementierung schon vorhanden.*/

INSERT  #DirectoryTree

        (filename

        ,depth

        ,isfile)

EXEC master.sys.xp_dirtree @BasePath, 1, 1;      

 

SELECT

    dt.filename

FROM

    #DirectoryTree dt

WHERE

    dt.isfile = 1

    AND RIGHT(dt.filename, 4) IN ('.mdf', '.ldf', '.ndf')

    AND NOT EXISTS 

        (SELECT 1 FROM sys.master_files mf

            WHERE mf.physical_name = @BasePath + dt.filename);

*lauffähig ab SQL Server 2012. Zum Bestimmen des Datenverzeichnisses vor SQL Server 2012 braucht man eine andere Lösung (z.B. den Umweg über Registry. Hier sind noch weitere Möglichkeiten beschrieben).