¿Cómo encontrar nuestros ficheros huérfanos?

En los últimos días tuvimos un problema de espacio en una de nuestras unidades de almacenamiento de datos de uno de nuestros servidores. Repasando la unidad de almacenamiento e instancia, nos dimos cuenta que muchos archivos mdf, ndf y ldf (a tener en cuenta durante la entrada si tienes extensiones personalizadas) no se encontraban asociados a ninguna base de datos. Estos archivos se encontraban huérfanos, seguramente debido a que con el tiempo se han ido eliminando bases de datos cuando estaban Offline sin eliminar los archivos asociados.

¿Cómo puedo encontrar y limpiar los archivos de base de datos no utilizados?

Os proponemos varias alternativas:

1.A través de T-SQL:

Buceando por la red encontramos el siguiente script:

use [master];
set nocount on

if object_id('tempdb..#rutas') is not null
drop table	 #rutas
create table	 #rutas ([path_id] int identity (1,1), [data_paths] varchar(255))
insert into	 #rutas ([data_paths])

select distinct left([physical_name], len([physical_name]) - charindex('\', reverse([physical_name])) -0)
from sys.master_files

if object_id('tempdb..#archivos_encontrados') is not null
drop table	 #archivos_encontrados
create table	 #archivos_encontrados ([files] varchar(255), [file_path] varchar(255), [depth] int, [file] int)

declare @get_files	varchar(max)
set	@get_files	= ''
select	@get_files	= @get_files +
'
insert into #archivos_encontrados ([files], [depth], [file]) exec master..xp_dirtree ''' + [data_paths] + ''', 1,1;
update #archivos_encontrados set [file_path] = ''' + [data_paths] + ''' where [file_path] is null;
' + char(10) from #rutas
exec	(@get_files)

select
	'no_associated_database'	= [files]
,	'path'				= [file_path]
from 
	 #archivos_encontrados
where
	[files] not in (select right([physical_name], charindex('\', reverse([physical_name])) - 1) from sys.master_files)
	and	[files] not in
	(
		'mssqlsystemresource.mdf'
	,	'mssqlsystemresource.ldf'
	,	'distmdl.mdf'
	,	'distmdl.ldf'
	)
	and [files] not like '%.cer'

La salida nos devuelve los ficheros huérfanos  y rutas de todas las bases de datos de la instancia:

use [master];

set nocount on

if object_id('tempdb..#rutas') is not null

drop table #rutas

create table #rutas ([path_id] int identity (1,1), [data_paths] varchar(255))

insert into #rutas ([data_paths])

select distinct left([physical_name], len([physical_name]) - charindex('\', reverse([physical_name])) -0)

from sys.master_files

if object_id('tempdb..#archivos_encontrados') is not null

drop table #archivos_encontrados

create table #archivos_encontrados ([files] varchar(255), [file_path] varchar(255), [depth] int, [file] int)

declare @get_files varchar(max)

set @get_files = ''

select @get_files = @get_files +

insert into #archivos_encontrados ([files], [depth], [file]) exec master..xp_dirtree ''' + [data_paths] + ''', 1,1;

update #archivos_encontrados set [file_path] = ''' + [data_paths] + ''' where [file_path] is null;

' + char(10) from #rutas

exec (@get_files)

select

'no_associated_database' = [files]

, 'path' = [file_path]

from

#archivos_encontrados

where

[files] not in (select right([physical_name], charindex('\', reverse([physical_name])) - 1) from sys.master_files)

and [files] not in

(

'mssqlsystemresource.mdf'

, 'mssqlsystemresource.ldf'

, 'distmdl.mdf'

, 'distmdl.ldf'

)

and [files] not like '%.cer'

La salida nos devuelve los ficheros huérfanos y rutas de todas las bases de datos de la instancia:

Otro script que nos encontramos fue el siguiente:

--Creamos tabla temporal para mantener los resultados
IF OBJECT_ID('tempdb..#ListadoDirectorio') IS NOT NULL
      DROP TABLE #ListadoDirectorio;

CREATE TABLE #ListadoDirectorio (
       id int IDENTITY(1,1)
      ,fullpath varchar(2000));

IF OBJECT_ID('tempdb..#ListaFichero') IS NOT NULL
      DROP TABLE #ListaFichero;

CREATE TABLE #ListaFichero (
       id int IDENTITY(1,1)
      ,fullpath varchar(2000)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);


-- Llenamos el listado de directories por los usados por las base de datos
INSERT INTO #ListadoDirectorio
SELECT DISTINCT LEFT(physical_name,LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)))
FROM sys.master_files

-- Declaramos Variables 
DECLARE @FolderActual Varchar(2000)

DECLARE folder_cursor CURSOR FAST_FORWARD READ_ONLY FOR 
SELECT fullpath FROM #ListadoDirectorio

OPEN folder_cursor

FETCH NEXT FROM folder_cursor INTO @FolderActual

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Populate File List for each directory
    INSERT INTO #ListaFichero (subdirectory, depth, isfile)
    EXEC master.sys.xp_dirtree @FolderActual,1,1
    UPDATE #ListaFichero
            SET fullpath = @FolderActual
            WHERE fullpath IS NULL;

    FETCH NEXT FROM folder_cursor INTO @FolderActual
END

CLOSE folder_cursor
DEALLOCATE folder_cursor

-- Tenemos un listado de los directories en #ListadoDirectorio
-- y  un  Listado de ficheros en #ListaFichero
--SELECT * FROM #ListadoDirectorio
--SELECT * FROM #ListaFichero

-- Borramos los ficheros que no son de BBDD
DELETE FROM #ListaFichero
WHERE UPPER(subdirectory) NOT LIKE '%.MDF'
AND UPPER(subdirectory) NOT LIKE '%.NDF'
AND UPPER(subdirectory) NOT LIKE '%.LDF'

-- Listamos los ficheros que NO son huérfanos
SELECT 'All Files'
SELECT sdb.name AS [Database], fl.fullpath AS [Path], fl.subdirectory AS [File] 
FROM #ListaFichero fl
JOIN sys.master_files smf
ON fl.fullpath + '\' + fl.subdirectory = smf.physical_name
JOIN sys.databases sdb
ON smf.database_id = sdb.database_id
ORDER BY sdb.name


-- Listamos los huérfanos
SELECT 'Orphaned Files'
SELECT fl.fullpath AS [OrphanPath], fl.subdirectory AS [OrphanFile]  FROM #ListaFichero fl
LEFT OUTER JOIN sys.master_files smf
ON fl.fullpath + '\' + fl.subdirectory = smf.physical_name
WHERE smf.database_id IS NULL
ORDER BY fl.subdirectory

-- Borramos las tablas temporales usadas
IF OBJECT_ID('tempdb..#ListadoDirectorio') IS NOT NULL
      DROP TABLE #ListadoDirectorio;

IF OBJECT_ID('tempdb..#ListaFichero') IS NOT NULL
      DROP TABLE #ListaFichero;

--Creamos tabla temporal para mantener los resultados

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

DROP TABLE #ListadoDirectorio;

CREATE TABLE #ListadoDirectorio (

id int IDENTITY(1,1)

,fullpath varchar(2000));

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

DROP TABLE #ListaFichero;

CREATE TABLE #ListaFichero (

id int IDENTITY(1,1)

,fullpath varchar(2000)

,subdirectory nvarchar(512)

,depth int

,isfile bit);

-- Llenamos el listado de directories por los usados por las base de datos

INSERT INTO #ListadoDirectorio

SELECT DISTINCT LEFT(physical_name,LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)))

FROM sys.master_files

-- Declaramos Variables

DECLARE @FolderActual Varchar(2000)

DECLARE folder_cursor CURSOR FAST_FORWARD READ_ONLY FOR

SELECT fullpath FROM #ListadoDirectorio

OPEN folder_cursor

FETCH NEXT FROM folder_cursor INTO @FolderActual

WHILE @@FETCH_STATUS = 0

BEGIN

-- Populate File List for each directory

INSERT INTO #ListaFichero (subdirectory, depth, isfile)

EXEC master.sys.xp_dirtree @FolderActual,1,1

UPDATE #ListaFichero

SET fullpath = @FolderActual

WHERE fullpath IS NULL;

FETCH NEXT FROM folder_cursor INTO @FolderActual

END

CLOSE folder_cursor

DEALLOCATE folder_cursor

-- Tenemos un listado de los directories en #ListadoDirectorio

-- y un Listado de ficheros en #ListaFichero

--SELECT * FROM #ListadoDirectorio

--SELECT * FROM #ListaFichero

-- Borramos los ficheros que no son de BBDD

DELETE FROM #ListaFichero

WHERE UPPER(subdirectory) NOT LIKE '%.MDF'

AND UPPER(subdirectory) NOT LIKE '%.NDF'

AND UPPER(subdirectory) NOT LIKE '%.LDF'

-- Listamos los ficheros que NO son huérfanos

SELECT 'All Files'

SELECT sdb.name AS [Database], fl.fullpath AS [Path], fl.subdirectory AS [File]

FROM #ListaFichero fl

JOIN sys.master_files smf

ON fl.fullpath + '\' + fl.subdirectory = smf.physical_name

JOIN sys.databases sdb

ON smf.database_id = sdb.database_id

ORDER BY sdb.name

-- Listamos los huérfanos

SELECT 'Orphaned Files'

SELECT fl.fullpath AS [OrphanPath], fl.subdirectory AS [OrphanFile] FROM #ListaFichero fl

LEFT OUTER JOIN sys.master_files smf

ON fl.fullpath + '\' + fl.subdirectory = smf.physical_name

WHERE smf.database_id IS NULL

ORDER BY fl.subdirectory

-- Borramos las tablas temporales usadas

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

DROP TABLE #ListadoDirectorio;

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

DROP TABLE #ListaFichero;

Su diferencia es que nos lista los ficheros de nuestras bases de datos y a continuación los que son huérfanos. Este script sólo busca en las carpetas indicadas en nuestras bases de datos que contienen sus ficheros.

Tras ejecutar cualquiera de las dos alternativas y con nuestro listado de ficheros huérfanos ya podemos eliminar, almacenar o lo que consideremos oportuno los archivos con el objetivo de mantener limpias nuestras unidades o no tener problemas en un futuro, por ejemplo, con ficheros con el mismo nombre al realizar una restauración.

2.Explorador

Un segundo método, poco (o nada) elegante pero eficaz, es simplemente seleccionar todos los archivos .MDF, .NDF y .LDF y moverlos de ubicación. Si está en uso el sistema operativo no nos permitirá moverlos ni eliminarlos:

Para ello debemos cerciorarnos de que el servicio de la instancia está corriendo.

También, algo más elegante pero también usando el explorador es comparar los archivos que están en uso en la instancia utilizando la siguiente query:

SELECT database_id, DB_NAME(database_id) AS DBName,
name AS LogicalName,
physical_name, convert(numeric(10,1),(size/128.0/1024.0)) SizeGB
FROM master.sys.master_files

SELECT database_id, DB_NAME(database_id) AS DBName,

name AS LogicalName,

physical_name, convert(numeric(10,1),(size/128.0/1024.0)) SizeGB

FROM master.sys.master_files

Comparamos su resultado con los ficheros que hay en las ubicaciones de nuestros ficheros de base de datos. Ya te avisamos que esto es bastante tedioso sobre todo si contamos con decenas o cientos de archivos.

3.PowerShell

Como ya te comentamos en entradas anteriores a través de InvokeSQL podemos ejecutar consultas T-SQL a través de powershell, por lo que podríamos ejecutar las querys del primer apartado de la entrada. También con este método podrías ejecutar la consulta en varias instancias a la vez. Por ejemplo:

use [master];
set nocount on

if object_id('tempdb..#rutas') is not null
drop table	 #rutas
create table	 #rutas ([path_id] int identity (1,1), [data_paths] varchar(255))
insert into	 #rutas ([data_paths])

select distinct left([physical_name], len([physical_name]) - charindex('\', reverse([physical_name])) -0)
from sys.master_files

if object_id('tempdb..#archivos_encontrados') is not null
drop table	 #archivos_encontrados
create table	 #archivos_encontrados ([files] varchar(255), [file_path] varchar(255), [depth] int, [file] int)

declare @get_files	varchar(max)
set	@get_files	= ''
select	@get_files	= @get_files +
'
insert into #archivos_encontrados ([files], [depth], [file]) exec master..xp_dirtree ''' + [data_paths] + ''', 1,1;
update #archivos_encontrados set [file_path] = ''' + [data_paths] + ''' where [file_path] is null;
' + char(10) from #rutas
exec	(@get_files)

select
	'no_associated_database'	= [files]
,	'path'				= [file_path]
from 
	 #archivos_encontrados
where
	[files] not in (select right([physical_name], charindex('\', reverse([physical_name])) - 1) from sys.master_files)
	and	[files] not in
	(
		'mssqlsystemresource.mdf'
	,	'mssqlsystemresource.ldf'
	,	'distmdl.mdf'
	,	'distmdl.ldf'
	)
	and [files] not like '%.cer'

use [master];

set nocount on

if object_id('tempdb..#rutas') is not null

drop table #rutas

create table #rutas ([path_id] int identity (1,1), [data_paths] varchar(255))

insert into #rutas ([data_paths])

select distinct left([physical_name], len([physical_name]) - charindex('\', reverse([physical_name])) -0)

from sys.master_files

if object_id('tempdb..#archivos_encontrados') is not null

drop table #archivos_encontrados

create table #archivos_encontrados ([files] varchar(255), [file_path] varchar(255), [depth] int, [file] int)

declare @get_files varchar(max)

set @get_files = ''

select @get_files = @get_files +

insert into #archivos_encontrados ([files], [depth], [file]) exec master..xp_dirtree ''' + [data_paths] + ''', 1,1;

update #archivos_encontrados set [file_path] = ''' + [data_paths] + ''' where [file_path] is null;

' + char(10) from #rutas

exec (@get_files)

select

'no_associated_database' = [files]

, 'path' = [file_path]

from

#archivos_encontrados

where

[files] not in (select right([physical_name], charindex('\', reverse([physical_name])) - 1) from sys.master_files)

and [files] not in

(

'mssqlsystemresource.mdf'

, 'mssqlsystemresource.ldf'

, 'distmdl.mdf'

, 'distmdl.ldf'

)

and [files] not like '%.cer'

También te invitamos a echar un ojo a la entrada de los compañeros de SQLShack que nos presentan una manera de realizar estas comprobaciones a través de PowerShell en una extensa entrada: https://www.sqlshack.com/multi-server-script-find-orphaned-data-files-using-powershell/

Y estos son las diferentes formas que hemos utilizado para repasar nuestras instancias dejando limpias nuestras unidades de almacenamiento evitando posibles problemas y, también importante, ahorrando un posible coste a nuestros clientes.

IMPORTANTE. Antes de eliminar cualquier fichero, recuerda realizar una copia de seguridad del mismo para poder recuperarlo si fuese necesario o tenerlo un tiempo en cuarentena. No queremos que nos llenéis el buzón de correo de quejas 🙂