GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

Eliminar ficheros de Base de Datos en SQL Server


Una de las operaciones de mantenimiento más típicas al trabajar con Bases de Datos SQL Server, es tener que eliminar un fichero de datos o un fichero de LOG de una Base de Datos, que ya no necesitamos, para poder liberar espacio en disco. El procedimiento a seguir es distinto, según sea un fichero de datos o de LOG, y tiene algún pequeño detalle a tener en cuenta, especialmente para aquellos que no trabajen habitualmente administrando SQL Server y que tienen que enfrentarse con este tipo de tareas por motivos variopintos.

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.

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.
Primero, tendremos que vaciar dicho fichero, tarea que realizaremos usando el comando DBCC SHRINKFILE con la opción EMPTYFILE. Segundo, con el fichero ya vacío, procederemos a eliminar el fichero de la Base de Datos, ejecutando un comando ALTER DATABASE REMOVE FILE.

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:

Cómo obtener una información de progreso de la ejecución del comando DBCC SHRINKFILE consultando sys.dm_exec_requests

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.

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.

También podemos utilizar FILEPROPERTY para obtener la propiedad SpaceUsed (devuelve el número de páginas asignadas, siendo cada página de 8KB)

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

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.

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 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.

Para vaciar el LOG ejecutaremos un BACKUP LOG

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.

Realizado esto, volveremos a comprobar el estado del LOG ejecutando el comando DBCC LOGINFO

Ahora ya podremos eliminar el fichero 4, ejecutando el correspondiente comando ALTER DATABASE REMOVE FILE, que podrá ejecutarse con éxito.

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.

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.

Descargar Script SQL con comandos y consultas SQL de ejemplo

Poco más por hoy. Como siempre, confío que la lectura resulte de interés.

 


[Fecha del Artículo (UTC): 07/04/2014]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Enero de 2017 (2)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas