Blog gratis
Reportar
Editar
¡Crea tu blog!
Compartir
¡Sorpréndeme!
img
img

 

 

SQL Server
Blog. (Apuntes y Recopilaciones) por Norman M. Pardell

img
img
27 de Febrero, 2011 · CHECKDB

Integridad y corrupción en las bases de datos: DBCC CHECKDB

 Cuando hay corrupción en una base de datos, la única manera segura, válida y rápida de volver a poner la base de dato en producción es a través de un Restore con un backup válido. En caso de no ser posible porque los backups no son válidos, o poco recientes, se pueden intentar cosas para intentar “reparar” la base de datos.

 

 Es importante tener en cuenta los puntos siguientes:

 

      - Intentar “reparar” una base de datos corrupta puede llevar mucho tiempo

      - Necesitamos concentrarnos en una base de datos a la vez, ya que para cada síntoma de corrupción los pasos a seguir para probar pueden ser distintos de un síntoma a otro

      - El comportamiento de una base de datos corrupta no es predecible. Podemos repetir una misma prueba 2 veces seguidas y que no tengamos el mismo resultado

      - No hay garantía de que lleguemos a un estado en que la base no tenga problemas de consistencia

      - Si lo conseguimos, se pueden haber perdido datos (y no podemos saber cuáles)

      - Si la base es utilizada por otra aplicación (por ejemplo Sharepoint), la pérdida de datos en esa base, puede llevar a la situación que la propia aplicación ya no soporte esta base de datos y se tenga que restaurar de un backup válido.

 

 

Consideraciones a tener, antes de ejecutar DBCC CHECKDB:

 

 Si se ejecuta DBCC CHECKDB cuando ya hay mucha actividad en el sistema, se deteriora el rendimiento de DBCC por dos motivos. En primer lugar, porque hay menos memoria disponible y se fuerza al motor de base de datos de SQL Server a almacenar los datos internos de DBCC CHECKDB en la base de datos tempdb. En segundo lugar, Si una carga de trabajo que consume una gran cantidad de memoria y está utilizando el mismo disco, donde se encuentran los ficheros de la base de datos, la cual esta siendo tratada por CHECKDB, se verá afectado el rendimiento provocando una ejecución más lenta.

 

 Dado que la base de datos tempdb reside en disco, el cuello de botella de las operaciones de E/S que se produce a medida que se escriben los datos en el disco, independientemente de la actividad del sistema, hacen recomiendar colocar la base de datos tempdb en discos rápidos, como un dispositivo RAID, separados de las bases de datos de usuario.

 

Si ejecutamos:

 

                DBCC CHECKDB WITH ESTIMATEONLY

 

 Muestra la cantidad de espacio para la base de datos tempdb que se prevé necesario para ejecutar DBCC CHECKDB con todas las demás opciones especificadas.

 

 La ejecución de DBCC CHECKDB ejecuta automáticamente DBCC CHECKTABLE para cada tabla de la base de datos, así como DBCC CHECKALLOC y DBCC CHECKCATALOG, por lo que deja de ser necesario ejecutarlos independientemente.

 

 DBCC CHECKDB no examina los índices deshabilitados.

 En las versiones de SQL Server 2005 anteriores al Service Pack 2, al ejecutar DBCC CHECKDB se borra la memoria caché del plan de la instancia de SQL Server. Al borrar la memoria caché del plan, se provoca una nueva compilación de todos los planes de ejecución posteriores y puede ocasionar una disminución repentina y temporal del rendimiento de las consultas. En el Service Pack 2, al ejecutar DBCC CHECKDB no se borra la memoria caché del plan.

 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 ejecuta. Si no se puede crear una instantánea o se especifica TABLOCK, DBCC CHECKDB se requieren bloqueos compartidos de tabla para realizar las comprobaciones de tabla. (Por motivos de rendimiento, las instantáneas de bases de datos no están disponibles en la tempdb. Eso significa que no es posible obtener la coherencia transaccional necesaria.) Solo produce DBCC CHECKDB un error cuando se ejecuta en la base de datos master, si no se puede crear una instantánea de base de datos interna.

Si ejecutamos:

 

                DBCC CHECKDB WITH TABLOCK

 Hace que DBCC CHECKDB obtenga bloqueos en lugar de utilizar una instantánea de base de datos interna. Se incluye un bloqueo exclusivo (X) a corto plazo en la base de datos. TABLOCK hace que DBCC CHECKDB se ejecute más rápido en una base de datos con mucha carga, pero disminuye la simultaneidad disponible en la base de datos mientras DBCC CHECKDB está ejecutándose.

 TABLOCK limita las comprobaciones que se llevan a cabo; DBCC CHECKCATALOG no se ejecuta en la base de datos y los datos de Service Broker no se validan.

 

 Se recomienda utilizar la opción PHYSICAL_ONLY para un uso frecuente en sistemas de producción. El uso de PHYSICAL_ONLY puede reducir mucho el tiempo de ejecución de DBCC CHECKDB en bases de datos grandes. También se recomienda ejecutar DBCC CHECKDB sin opciones de forma periódica. La frecuencia con que se deben realizar estas ejecuciones varía en función de la empresa y su entorno de producción.

 Si ejecutamos:

                DBCC CHECKDB WITH PHYSICAL_ONLY

 Esta comprobación se ha diseñado para proporcionar una pequeña comprobación de sobrecarga de la coherencia física de la base de datos; también detecta páginas rasgadas, errores de suma de comprobación y errores de hardware comunes que pueden comprometer los datos del usuario. PHYSICAL_ONLY siempre implica NO_INFOMSGS y no se permite con ninguna de las opciones de reparación.

                DBCC CHECKDB WITH NO_INFOMSGS

                Suprime todos los mensajes de información

 En SQL Server 2005 con el Service Pack 1, se crea un archivo de volcado (SQLDUMPnnnn.txt) en el directorio LOG de SQL Server siempre que DBCC CHECKDB detecta un error relacionado con datos dañados. El acceso está limitado a la cuenta de servicio de SQL Server y a los miembros de la función sysadmin. De forma predeterminada, la función sysadmin contiene todos los miembros del grupo BUILTINAdministrators de Windows y el grupo de administradores local.

 

 

Resolver  errores de DBCC CHECKDB:

 Si DBCC CHECKDB notifica errores, se recomienda restaurar la base de datos a partir de una copia de seguridad en lugar de ejecutar REPAIR con una de sus opciones. La opción de reparación que se debe utilizar se especifica al final de la lista de errores notificados por CHECKDB. No obstante, la corrección de errores mediante la opción REPAIR_ALLOW_DATA_LOSS puede requerir eliminar algunas páginas y, por tanto, también algunos datos.

Si ejecutamos:

 

DBCC CHECKDB (BBDD, REPAIR_ALLOW_DATA_LOSS)

 Intenta reparar todos los errores indicados. Estas reparaciones pueden ocasionar alguna pérdida de datos.

DBCC CHECKDB (BBDD, REPAIR_FAST)

 La sintaxis se mantiene únicamente por compatibilidad con versiones anteriores. No se realizan acciones de reparación.

DBCC CHECKDB (BBDD, REPAIR_REBUILD)

 Lleva a cabo rápidamente acciones de reparación menores, como la reparación de claves adicionales en índices sin agrupar, y reparaciones lentas como la regeneración de índices. Estas reparaciones se pueden realizar sin riesgo de pérdida de datos.

 

 En algunas circunstancias, es posible que la base de datos contenga valores que no son válidos o que no están comprendidos en el intervalo correcto de acuerdo con el tipo de datos de la columna. En SQL Server 2000, DBCC CHECKDB no realiza comprobaciones de intervalo o de integridad en estos valores de columna. Sin embargo, en SQL Server 2005, DBCC CHECKDB puede detectar valores de columna que no son válidos para todos los tipos de datos de columna. Por tanto, al ejecutar DBCC CHECKDB con la opción DATA_PURITY en bases de datos que se han actualizado desde versiones anteriores de SQL Server, pueden desvelarse errores de valores de columna que ya existían. Dado que SQL Server 2005 no puede reparar estos errores automáticamente, será necesario actualizar el valor de la columna de forma manual. Si CHECKDB detecta un error de este tipo, devuelve una advertencia, el número de error 2570 e información para identificar la fila afectada y corregir el error manualmente.

 Si ejecutamos:

 

DBCC CHECKDB WITH DATA_PURITY

 

 Hace que DBCC CHECKDB compruebe si la base de datos contiene valores de columna que no son válidos o están fuera del intervalo correcto. Por ejemplo, DBCC CHECKDB detecta las columnas cuyos valores de fecha y hora son superiores o inferiores al intervalo de valores válido para el tipo de datos datetime; o bien las columnas del tipo de datos decimal o numérico aproximado con valores de escala o precisión que no son válidos.

 Para las bases de datos creadas en SQL Server 2005, las comprobaciones de integridad de valores de columna están habilitadas de manera predeterminada y no requieren la opción DATA_PURITY. De manera predeterminada, en las bases de datos actualizadas desde versiones anteriores de SQL Server, las comprobaciones de valores de columna no se habilitan hasta que se ejecuta DBCC CHECKDB WITH DATA_PURITY sin errores en la base de datos. Después, DBCC CHECKDB comprueba la integridad de los valores de columna de manera predeterminada. Para obtener más información sobre cómo podría afectar a CHECKDB la actualización de bases de datos de versiones anteriores de SQL Server, vea la sección Notas más adelante en este tema.

 

 Una vez finalizadas las reparaciones, realice una copia de seguridad de la base de datos.

En nuestro caso, los errores que devuelve el DBCC CHECKDB son:

 

 

Caso Práctico:

 

 Al ejecutar DBCC CHECKDB, en una base de datos obtenemos:

 

Msg 8914, Level 16, State 1, Line 1

Incorrect PFS free space information for page (1:61871) in object ID 2009058193, index ID 1, partition ID 72057597430071296, alloc unit ID 71907784698953728 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

Msg 8914, Level 16, State 1, Line 1

Incorrect PFS free space information for page (1:63663) in object ID 2009058193, index ID 1, partition ID 72057597430071296, alloc unit ID 71907784698953728 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

Msg 8914, Level 16, State 1, Line 1

Incorrect PFS free space information for page (1:64839) in object ID 2009058193, index ID 1, partition ID 72057597430071296, alloc unit ID 71907784698953728 (type LOB data). Expected value   0_PCT_FULL, actual value 100_PCT_FULL.

.... etc...

CHECKDB found 0 allocation errors and 42 consistency errors in database 'BBDD'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (BBDD).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

La opción mínima de reparación que propone el DBCC CHECKDB es repair_allow_data_loss. En este caso, podríamos estar ante la situación de que intentando reparar la base de datos tenemos el riesgo de perder datos, y puediera ser que la aplicación ya no soportaría el estado de la base de datos, siendo la única solución restaurar de un backup válido.

 

 Por otra parte, también es cierto que aunque la opción sea repair_allow_data_loss, teóricamente lo que hace en casos de error 8914 es corregir el valor del espacio libre en las páginas:

 

[PFS free space error

You may encounter the following error when running DBCC CHECKDB:

Msg 8914, Level 16, State 1, Line 1 Incorrect PFS free space information for page (1:128) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.

In this case, the CHECKDB code recommends REPAIR_ALLOW_DATA_LOSS but repair simply fixes the free space information in the PFS page with no data loss occurring as a result.]

 

El plan de acción que propongo es el siguiente:

1º.- Efectuar un backup de la base de datos

2º.- Ejecutar DBCC CHECKDB (BBDD, REPAIR_ALLOW_DATA_LOSS)

3º.- Ejecutar un DBCC CHECKDB (BBDD)

4º.- Realizar un backup de la bbdd, para disponer de un backup de una base de datos coherente.

 

 

En casos de error 8914, normalmente, se da este error cuando se utilizan registros mínimos para los LOB ( Se producen en determinadas ocasiones en bbdd con el modelo de recuperación SIMPLE, durante BULK INSERT / bcp / inserciones grandes con la opción TABLOCK).

 

 A pesar de que se trata de una salida de error, no significa que exista nada malo con la base de datos, simplemente significa que hay de un número de páginas adicionales que están vacíos y destinados a la estructura de árboles de páginas, así que mientras el mensaje que dice que la página está vacía, en realidad son marcadas 100% para que solo sean asignadas con información sobre la estructura de árboles , nada puede ir mal con esas páginas. Por desgracia, DBCC informa de estos “errores”, si se pueden considerar errores...

 

 Se pueden deshacerse de las páginas vacías LOB utilizando ALTER INDEX ... REORGANIZE CON (LOB_COMPACTION = ON), sin la necesidad de realizar DBCC CHECKDB (BBDD, REPAIR_ALLOW_DATA_LOSS). Por lo tanto, si vemos algunos de estos errores (donde realmente no tenemos nada de qué preocuparnos), puede que podamos evitarlos con el “ALTER INDEX…”.

 

 

Basado en:

http://msdn.microsoft.com/en-us/library/ms176064.aspx

http://msdn.microsoft.com/es-es/library/ms175515(v=sql.90).aspx

Y casos propios.

 

Apuntes y recopilaciones por  Norman M. Pardell 

 

Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.

 

publicado por normanmpardell a las 19:48 · Sin comentarios  ·  Recomendar
Comentarios (0) ·  Enviar comentario
Esta entrada no admite comentarios.
img
.Sobre mí
FOTO

Norman M. Pardell

MCITP: Database Administrator & Database Developer, SQL Server 2008. MCC Award Certificate. Consultor Senior de bases de datos en Capgemini España, S.L. Asesoramiento en implementación, desarrollo y gestión de bases de datos en grandes compañías. Actualmente, asignado a proyecto en compañía líder en el sector energético global. Más de 10 años trabajando con SQL Server (y otros gestores de BBDD)

» Ver perfil

img
.Secciones
» Inicio
img
.Enlaces
» Microsoft MSDN Foros
» Windows Server 2012
img
.Más leídos
» Asignar la cantidad correcta de Memoria para SQL Server
» Base de Datos Sospechosa (Suspect)
» Como modificar la Intercalación (Collation) en SQL Server
» Como renombrar una instancia de SQL Server. sp_dropserver. sp_addserver
» Detectar bloqueos. SQL Server V.2005 y superiores
» Funciones SQL Server. Funciones escalares y funciones con valores de tabla.
» Integridad y corrupción en las bases de datos: DBCC CHECKDB
» Log de transacciones ( .ldf ). SQL Server.
» Migrando SQL Server 2005, 2008, 2008 R2 a SQL Server 2012
» Transacciones activas. SQL server 2008
img
.Nube de tags [?]
                                                           
img img
FULLServices Network | Crear blog | Privacidad