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:
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 |
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:
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 |
--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:
1 2 3 4 |
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:
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 |
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 🙂