No es posible eliminar el fichero principal de Datos o de Log
Esto es así By Design. El primer fichero de datos y el primer fichero de Log, no se pueden eliminar, y si lo intentamos, obtendremos el siguiente mensaje de error: The primary data or log file cannot be removed from a database.
Estos ficheros, aunque no los podemos eliminar, si los podemos mover de sitio, eso sí, con la correspondiente indisponibilidad, ya sea poniendo OffLine la Base de Datos o mediante Dettach/Attach.
Cómo eliminar un fichero secundario de datos en SQL Server
La forma tradicional de eliminar un fichero de datos en SQL Server consiste habitualmente en dos pasos:
- Primero, tendremos que vaciar dicho fichero, tarea que realizaremos usando el comando DBCC SHRINKFILE con la opción EMPTYFILE. Este comando puede ejecutarse con la base de datos ONLINE, pero debe tenerse en cuenta que puede durar bastante tiempo (en función del tamaño del fichero que estamos vaciando), durante su ejecución el rendimiento puede verse afectado (es una operación intensiva en acceso a disco), y debemos asegurarnos previamente de tener espacio suficiente en el resto de ficheros de datos para almacenar la información contenida en el fichero de datos que vamos a vaciar.
- Segundo, con el fichero ya vacío, procederemos a eliminar el fichero de la Base de Datos, ejecutando un comando ALTER DATABASE REMOVE FILE. Será un instante, y puede ejecutarse con la base de datos ONLINE.
Por tener alguna referencia del tiempo necesario para hacer los SHRINKFILE sobre ficheros de bases de datos reales de un entorno de Producción:
- Fichero NDF de 600MB: 12 min.
- Fichero NDF de 4GB: 24 min.
- Fichero NDF de 13GB: 40 min.
- Fichero NDF de 40GB: 3 horas.
Llegados a este punto, nos puede surgir la duda de cómo obtener una información de progreso de la ejecución del comando DBCC SHRINKFILE. Bueno, podemos consultar las vista dinámica (DMV) sys.dm_exec_requests, en particular el campo percent_complete, pero sólo nos sirve para hacernos una idea. Por poner un ejemplo, hace poco en un SHRINKFILE después de más de 3 horas mostraba que sólo se había completado un 13%, y cinco minutos después el SHRINKFILE había finalizado. Moraleja: consultar varias veces esta vista nos permite ver que efectivamente la operación está progresando, pero en ningún caso debemos confiar completamente en su estimación del trabajo realizado. Este es caso del que hablaba:
Si deseamos comprobar el tamaño de los ficheros de datos, podemos ejecutar el comando DBCC SHOWFILESTATS, consultar sys.sysfiles, o bien consultar sys.database_files.
También podemos utilizar FILEPROPERTY para obtener la propiedad SpaceUsed (devuelve el número de páginas asignadas, siendo cada página de 8KB) de un fichero de la Base de Dato, por ejemplo, apoyándonos en la vista del sistema sys.database_files o en sys.sysfiles.
Cómo eliminar un fichero secundario de LOG en SQL Server
El procedimiento para eliminar un fichero secundario de LOG de una Base de Datos SQL Server es algo distinto a como se haría con un fichero de datos. Vamos a explicarlo siguiendo un ejemplo realizado con una Base de Datos SQL Server 2008 R2 con el Recovery Model a FULL.
Como en el caso anterior, si intentamos eliminar un fichero de LOG que está en uso, obtendremos un mensaje de error: The file cannot be removed because it is not empty.
Sin embargo, para eliminar un fichero de LOG no necesitamos ejecutar previamente un DBCC SHRINKFILE EMPTYFILE para vaciar el fichero. En lugar de esto, en primer lugar deberemos ejecutar el comando DBCC LOGINFO para comprobar qué ficheros de LOG están en uso, y cuales no. Para esto deberemos fijarnos en que la columna Status debe es 0 para todas las filas del fichero de LOG, ya que el valor de Status a 2 indica que está en uso.
Para vaciar el LOG ejecutaremos un BACKUP LOG. Esto se llevará el contenido del LOG a un fichero de Backup. Aunque nos podemos encontrar con ciertos problemillas (ej: transacciones en curso, especialmente cuando son transacciones muy largas, un Mirror sin sincronizar, etc), en muchos casos sólo con esto será suficiente.
Realizado esto, volveremos a comprobar el estado del LOG ejecutando el comando DBCC LOGINFO. Como podemos observar en la siguiente pantalla capturada, el fichero 3 aún está en uso, mientras que el fichero 4 ya no está siendo utilizado.
Ahora ya podremos eliminar el fichero 4, ejecutando el correspondiente comando ALTER DATABASE REMOVE FILE, que podrá ejecutarse con éxito.
Un detalle curioso, es que después de eliminar el fichero de LOG, si consultamos la vista sys.master_files o sys.database_files, el fichero que acabamos de eliminar aún aparece (es un fichero de log fantasma – phantom log file). No tiene mayor importancia, ya que si nos fijamos en los campos state y state_desc de dicha vista, podremos observar que el fichero eliminado aparece como OFFLINE.
Tan sólo deberemos esperar a que se ejecute el siguiente Backup de LOG (recordemos que estamos trabajando con una Base de Datos con el Recovery Model a FULL), tras lo cual, si volvemos a consultar sys.master_files o sys.database_files, podremos comprobar que el fichero eliminado definitivamente ya no aparece.
Despedida y Cierre
Hasta aquí llega el presente artículo, en el cual hemos intentado describir los pasos a seguir para eliminar un fichero de datos o un fichero de LOG de una Base de Datos SQL Server, una tarea de mantenimiento bastante típica.
Por último, antes de finalizar, tan sólo queda dejar un enlace para descargar un fichero SQL con los distintos comandos y consultas SQL que hemos visto a lo largo del presente artículo, para poder tenerlo a modo de ejemplo.
Poco más por hoy. Como siempre, confío que la lectura resulte de interés.
