¿Qué son los Ficheros de Tipo "sparse" y Qué Importancia Tienen para un DBA?

Durante la pasada semana trabajé junto con uno de mis compañeros del grupo de soporte a la Plataforma Windows en un problema acerca de la copia de seguridad en ficheros de datos de SQL Server. Mi compañero y yo aprendimos varias cosas en este caso y creo que merece la pena compartir esta información con vosotros.

Nuestro cliente estaba utilizando Microsoft Data Protection Manager (DPM) para realizar copias de seguridad de los servidores Windows y las bases de datos de SQL Server y estaba encontrando errores precisamente al copiar los ficheros de base de datos. Durante nuestro trabajo en el caso el cliente encontró el siguiente artículo en un blog de MSDN que explicaba en detalle la causa del problema: "Did your backup program/utility leave your SQL Server running in a squirrely scenario?". Si nunca has oído hablar de los cheros de tipo sparse (traducido como "fichero disperso") y cómo estos afectan a SQL Server, te recomiendo que eches un vistazo a este post del blog del grupo de soporte para SQL Server.

¿Qué son los ficheros sparse?

Los ficheros sparse son creados por SQL Server durante operacones como la creación de un snapshot o instantánea de una base de datos o durante la ejecución de un sentencia DBCC CHECKDB. En el caso de un snapshot el fichero sparse es borrado cuando el snapshot es borrado, mientras que en el caso de una operación de comprobación de consistencia de una base de datos con DBCC CHECKDB el fichero sparse es borrado automáticamente por SQL Server justo después de la ejecución. Puedes encontrar información completa sobre el papel de los ficheros "sparse" en los siguientes enlaces:

Instantánea de base de datos interna

DBCC CHECKDB utiliza una instantánea interna de la base de datos para la coherencia transaccional necesaria para realizar estas comprobaciones. Así se evitan problemas de bloqueo y simultaneidad cuando se ejecutan estos comandos. Para obtener más información, vea Descripción del tamaño de los archivos dispersos en instantáneas de bases de datos y la sección sobre el uso de la instantánea de base de datos interna DBCC en DBCC (Transact-SQL).

Descripcion del tamaño de los archivos dispersos en instantáneas de bases de datos

Los archivos dispersos son una característica del sistema de archivos NTFS. Inicialmente, un archivo disperso no incluye datos de usuario y no se le asigna espacio en disco para éstos. Para obtener información general sobre el uso de los archivos dispersos en instantáneas de bases de datos y el crecimiento de éstas, vea Funcionamiento de las instantáneas de la base de datos.

Cuando se crea por primera vez, un archivo disperso ocupa poco espacio en disco. A medida que se escriben datos en él, NTFS le asigna espacio en disco de forma gradual. El tamaño de un archivo disperso puede aumentar en gran medida. Si una instantánea de base de datos se queda sin espacio, se marcará como sospechosa y se deberá quitar. Sin embargo, la base de datos de origen no se verá afectada y las acciones en ella continuarán normalmente.

¿Qué es lo que sucede?

Los ficheros de tipo sparse son una funcionalidad ofrecida por el sistema de ficheros NTFS. Con determinadas versiones de NTFS.SYS la propiedad sparse de un fichero es incorrectamente transferida al fichero original, también llamado fichero "padre", del que el fichero de tipo sparse procede. Este sería un ejemplo de esta situación:

1. Ejecutamos una sentencia DBCC CHECKDB on una base de datos SQL Server como parte de un proceso de mantenimiento. Esta acción se completa sin errores
2. Realizamos una copia de seguriad de la base de datos con utilidades como DPM o alguna otra utilidad de backup
3. En esta situación los ficheros originales de base de datos son marcados como de tipo sparse

¿Cuál es el problema?

SQL Server no soporta operaciones de copia de seguridad o restauración en ficheros de tipo "sparse de forma que podemos ponernos en una situación de no-soporte sin ni siquiera ser conscientes de ello

¿Cómo puedo saber si estoy en esta situación?

Ejecuta la siguiente consulta y comprueba si en la columna 'is_sparse' aparecen valores distintos de 0 (cero):

use <DatabaseName>; SELECT is_sparse, * from sys.database_files

El siguiente ejemplo muestra una base de datos con varios ficheros de este tipo:

La herramienta de configuración de mejores prácticas para SQL Server SQL Server 2008 R2 Best Practices Analyzer incluye unan nueva relga para detectar cuándo una base de datos incluye ficheros de tipo "sparse". Se pueden encontrar más detalles en el artículo de la Knowledge Base KB2028447.

¿Qué puedo hacer para evitar este problema?

Como primera medida debemos actualizar la versión de NTFS.SYS en los servidores Windows que alojan ficheros de base de datos. La última versión de NTFS.SYS evita que este problema ocurra. Lo siguiente es una lista de los hotfixs para NTFS.SYS que debemos tener en cuenta (estos enlaces hacen referencia a artículos de traducción automática):

Para Windows Server 2003:

No puede restaurar archivos de gran tamaño en el sistema de archivos NTFS cuando se eliminan todas las secuencias de datos que tienen atributos dispersos en la versión de 64 bits de Windows XP SP2 o en Windows Server 2003 Service Pack 2, https://support.microsoft.com/kb/973886/es-es

Mensaje de error al ejecutar el comando "chkdsk" junto con el "/ v" cambiar en un equipo basado en Windows Server 2003: "Corregir archivo disperso segmento de registro, https://support.microsoft.com/default.aspx?scid=kb;es-es;932021

Para Windows Server 2008:

Informes del Administrador de disco incorrectas un tiempo después de que el sistema operativo se inicia en Windows Server 2008 o en Windows Vista de uso de disco, https://support.microsoft.com/kb/981891/es-es

Para SQL Server 2005 y 2008:

Ademas de las actualizaciones indicadas para Windows, debemos aseguarnos de que tenemos instalada la úlitma versión del Paquete de Servicio (Service Pack, SP) y Paquete Acumulativo (Cumulative Update, CU) tanto en SQL Server 2008 como 2005 para prevenir que este problema vuelva a suceder.

¿Cómo puedo solucionar el problema?

Actualizar los servidores Windows y SQL Server con los últimos Paquetes de Servicio y hotfixes evitará que el problema vuelve a suceder pero no solucionará el problema si este ya existe. El post del grupo de producto mencionado inicialmente incluye el procedimiento para solucionar este problema tanto ten SQL Server 2005 como en SQL Server 2008.

Otra opción para poser solucionar este problem es vaciar (en Inglés, empty) el contenido del fichero de base de datos a otro fichero dentro del mismo filegroup. Podemos realizar esta acción utilizando la opción EMPTYFILE del commando Transact-SQL SHRINKFILE, aquí se muestra un ejemplo:

Este método no puede ser utilizado cuando el fichero con la propiedad sparse es el primer fichero del filegroup primario (PRIMARY) ya que este fichero contiene varios objetos del sistema que no puedes ser movidos.

¡Después de seguir estos pasos SQL Server todavía muestra los ficheros como "sparse"!

Después de seguir estas indicaciones es posible que SQL Server siga mostrando los fichero como de tipo sparse a pesar de que la utilidad de Windows fsutil muestre los ficheros como no-sparse. Si este es el caso, debemos de asegurarnos de hacer un detach y a continuación un attach de estos ficheros; esto es necesario para que la información de la vista sys.database_files sea actualizada.

Jorge Pérez Campo - Microsoft Customer Support Services