Una vez me pidieron que ayudara a "arreglar" una base de datos. CHECKDB durante la verificación dio una lista de errores, algunos de los cuales fueron marcados como "incorregible". La aplicación funcionó, pero todavía estaba de alguna manera inquieta.
Sí, la solución correcta en tal situación sería hacer una copia de seguridad desde el momento en que los errores aún no aparecen, localizar los datos corruptos y sobrescribirlos desde una copia limpia. Pero ... como suele suceder, el error se detectó demasiado tarde, por lo que realmente no había nada de qué recuperarse. Por otro lado, habría una copia de seguridad, no habría esta historia.
Anamnesis
El primer paso es lanzar DBCC CHECKDB para comprender la magnitud de la tragedia. El equipo honestamente revisó todas las tablas, en su mayor parte sin encontrar ningún problema. En la misma conclusión, hubo cientos de informes de errores "corregibles". Algo como esto:
Index row (1:386974:44) with values (C_FK_6bb5032ec2f94557a7d4a9d39a356168 = '04DA7FC4-B8F2-4D97-B8D2-B207A918D3DF' and C_PK_dd87d9cad5504a1199d4ddaf511ea6a9 = 'F44E8C34-862D-4952-8821-D0E87143A74E') pointing to the data row identified by (C_PK_dd87d9cad5504a1199d4ddaf511ea6a9 = 'F44E8C34-862D-4952-8821-D0E87143A74E').
Y algunos errores más graves:
Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). Page (1:20426) is missing a reference from previous page (1:267203). Possible chain linkage problem.
Pues bien. La escala de trabajo se describe, ¡comencemos!
Errores corregibles
Para comprender por qué algunos errores pueden repararse fácilmente de forma automática, recordemos cómo se organizan los índices en MS SQL. Puede dividirlos en 2 tipos: agrupados y (sorprendentemente) no agrupados. (No profundizaremos en asuntos especiales como los índices de almacén de columnas; este no es el caso). Ambos son un árbol equilibrado, lo cual es muy conveniente para encontrar datos.
Es importante que los índices de clúster en su nivel de "hoja" almacenen directamente el contenido de las filas de la tabla. Pero los índices no agrupados almacenan solo los datos clave (y, si están disponibles, los campos "incluidos"), así como un enlace a la fila del índice del grupo. Es decir, si tenemos un problema en un índice no agrupado, simplemente podemos tomar y sobrescribir los datos dañados del índice agrupado. Bueno, o simplemente reconstruir el índice batido: es bueno, se conoce la composición de los campos y los datos de origen están cerca, intactos.
La tarea no es completamente creativa, por lo que puede confiarla con seguridad a un automóvil sin alma. Ejecutar el comando
DBCC CHECKDB (< >, REPAIR_REBUILD)
y estudiar el informe de progreso.
Tanto el registro de origen como el registro después de la "corrección" presentan las direcciones de las páginas dañadas. Comparamos estas direcciones y nos aseguramos de que todos los errores marcados como "corregibles" se resolvieron correctamente.
Trastorno de conectividad de datos
Ahora algo más serio. Después de corregir los índices no agrupados y borrar el registro de los mensajes de información, el informe contiene tres registros de errores "fatales"
Por supuesto, puede darse por vencido y cortar con el comando DBCC CHECKDB (<DB name>, REPAIR_ALLOW_DATA_LOSS). Pero ... simplemente no quiero perder los datos. Quiero restaurar todo lo que sea posible al máximo. Por lo tanto, veamos con más detalle lo que el informe de error nos dice en general.
Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). Page (1:20426) is missing a reference from previous page (1:267203). Possible chain linkage problem. Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). The previous link (1:267203) on page (1:267204) does not match the previous page (1:20426) that the parent (1:218898), slot 213 expects for this page. Table error: Object ID 1333579789, index ID 1, partition ID 72057609974841344, alloc unit ID 72057610075701248 (type In-row data). B-tree chain linkage mismatch. (1:20426)->next = (1:267204), but (1:267204)->Prev = (1:267203).
El informe contiene una historia turbia de que una página espera ver a un vecino, pero el vecino no sabe nada al respecto. Algún marinero pelevinsky Zheleznyak: subió a cubierta, pero no cubierta.
Para una comprensión completa, se necesitan más detalles y tendrán que ir directamente al contenido de las páginas. Pero antes de eso, descubriremos cómo se relacionan las páginas de índice de SQL Server en general.
Obviamente, las páginas tienen enlaces jerárquicos "verticales", que forman el árbol B. La página superior contiene enlaces a páginas de un nivel inferior y así sucesivamente hasta las mismas hojas. Como ya dije, esto es muy conveniente para encontrar valores: desea encontrar “Vasya Pupkin” y después de un par de páginas (“de B a G” → “de Ba a Bb” → “Vasya Pupkin”) encuentra el deseado.
Pero hay situaciones en las que una consulta necesita seleccionar inmediatamente rangos enteros de líneas ("De Vasya a Grisha"). En este caso, cada vez que baja del árbol de arriba a abajo, se topa con él. Para cumplir con tales solicitudes, las páginas almacenan enlaces "horizontales": cada página conoce el número vecino "antes" y "después". Con estas relaciones, los escaneos de índice son mucho más fáciles de realizar.
A juzgar por el registro de errores, tuvimos una falta de coincidencia de las conexiones horizontales y verticales. Pero para finalmente estar convencido de esto, echemos un vistazo a las páginas en sí.
¡Necesitamos ir más profundo!
Para ver las páginas, utilizaremos el antiguo, bien merecido e indocumentado equipo DBCC PAGE. Toma 4 parámetros:
- Eid Base
- ID del archivo base
- ID de página
- Nivel de detalle (0 a 3)
Dependiendo del último parámetro, puede ver solo el encabezado del servicio (0), o el contenido completo de la página (3), o el encabezado y alguna parte del contenido (1 y 2)
Por cierto, en SQL Server 2019, finalmente apareció una vista documentada
sys.db_db_page_info , que realiza tareas similares. Desafortunadamente, solo muestra los datos del encabezado (análogo al desglose 0), por lo que aún no responde nuestras tareas por completo.
Entonces, para empezar, ejecute el comando
DBCC TRACEON (3604, 1)
para que la salida de los comandos DBCC restantes llegue a nuestra consola y no a ErrorLog
Después de eso, mira el título de la página 20426:
DBCC PAGE (11, 1, 20426, 0)
Ya veo La página quiere estar en el medio entre las páginas 267203 y 267204. Pero ¿qué pasa con estas mismas páginas?
¡Sin dormir, sin espíritu! Están bien sin invitados no invitados.
Bueno, veamos la próxima tabla de contenido:
El rompecabezas se desarrolla gradualmente:
- En términos de enlaces "verticales" (estructura de árbol de índice), la página 20426 debe estar entre 267203 y 267204
- Las conexiones horizontales contradicen esto y dicen que no hay nadie entre 267203 y 267204.
Ahora intentemos comprender qué tipo de datos sufrió el error.
Las líneas dentro de la página se ordenan por clave de índice. En consecuencia, conociendo el primer y último valor clave en la página, puede encontrar el rango de registros "afectados".
La clave se puede ver simplemente en la descripción del índice. Usaremos el comando
sp_helpindex para esto. En este caso, la clave es solo una columna, incl. ponle toda la atención a ella.
Para encontrar las claves que aparecen en la página, utilizamos DBCC PAGE con un máximo de 3 niveles de detalle. Si se desplaza por el registro de salida, puede ver el contenido "en bruto" de cada línea y un desglose para cada campo:
Por cierto, los valores de límite clave (el primer valor en la página) también se pueden ver en la salida de PÁGINA DBCC para la página de índice superior (218898, vea la captura de pantalla anterior). Se muestran en la columna que sigue inmediatamente al número de página.
Un examen de las páginas muestra que la página 20426 contiene exactamente la mitad de las líneas de la página 267203. La naturaleza de la falla se vuelve clara. Cuando la página de la base de datos está llena y no tiene más espacio para insertar datos nuevos, se divide por la mitad en 2 páginas nuevas. Aparentemente, cuando la página 267203 se llenó, se creó el problema página 20426. El DBMS comenzó a reconstruir sus enlaces: logró escribir una nueva página en la estructura del índice. Pero por alguna razón, los registros horizontales no se pudieron actualizar y la nueva página se "colgó" en un estado indefinido.
¿Qué será todo esto para nosotros?
Ciertamente no hay mucho bien. Obviamente, los enlaces de página son necesarios para acceder a los datos. Al ejecutar la consulta, el DBMS determina independientemente de qué manera llegar a estos datos. Pero la regla general (de la cual son posibles las excepciones) es esta:
- Las selecciones "anchas" (por ejemplo, para informes) se realizan utilizando registros horizontales. Es más fácil desplazarse por varias páginas secuencialmente, eligiendo un amplio rango
- Las consultas de "punto" (actualizar un registro específico) se realizan mediante una búsqueda de "tabla de contenido".
Resulta que cuando cambian registros específicos, el DBMS llega a la página "problema" 20426. Y cuando ejecuta el informe, lee los datos "horizontalmente" y no ve los cambios realizados. Una vez más: en la práctica, el algoritmo puede ser más complicado, pero la clase de posibles problemas aún es comprensible.
Las eternas preguntas del intelectual ruso
Para ser honesto, todavía no está claro para mí cómo podría suceder esto. Un DBMS moderno es en realidad una cosa bastante confiable. Todos los cambios en el archivo de la base de datos (incluido el ajuste en las relaciones horizontales y verticales) se realizan en una transacción. Estas operaciones se registran en el registro de transacciones y, si no hay confirmación de una operación exitosa en este registro, todas las operaciones se revierten. Aquí puede ver que la transacción se completó con éxito, pero algunos de los cambios a lo largo de la ruta al archivo de datos se "perdieron".
La única explicación plausible que se me ocurrió fue una falla de la caché del subsistema de disco. Todos los datos se almacenaron en la memoria caché, luego parte de los registros del archivo de datos y del registro se escribieron en el disco, y luego alguien presionó el interruptor. Como resultado, los registros "horizontales" modificados no lograron ser registrados, pero la base de datos ya no sabía nada al respecto. (Y aquí, muchos lectores deberían apresurarse a revisar las baterías de sus controladores de disco industriales)
Qué hacer es mucho más claro. Para restaurar la conectividad de una tabla, es suficiente copiar sus datos a otra tabla para que se use la lectura horizontal al copiar. Para fidelidad, puede especificar explícitamente el método de acceso DBMS correcto utilizando la sugerencia FORCESCAN
select * into T_bca79e9e77c24cdc8bbb7cfd0ddc16fd_BKP from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd with (FORCESCAN)
Después de eso, puede borrar la tabla de origen y devolver allí los datos copiados.
Pero luego se perderán los cambios realizados en la página 20426.
Por lo tanto, antes de hacer una copia de la tabla, debe copiar las filas con identificadores de la página 20426. Y después de restaurar la tabla, corrija las entradas necesarias. El acceso a los registros de la página 20426 es necesario explícitamente mediante identificadores:
select * from T_bca79e9e77c24cdc8bbb7cfd0ddc16fd where C_PK_bca79e9e77c24cdc8bbb7cfd0ddc16fd = ''
Los identificadores se pueden obtener leyendo el contenido de la página con la misma PÁGINA DBCC. Como ya escribí, había exactamente la mitad de estas páginas 267203, es decir, 15 enlaces.
Haga una copia de seguridad de las páginas dañadas, vuelva a cargar la tabla y coincida con los registros coincidentes: después de un par de decenas de minutos, la tabla se restauró.
¡Hurra, ganamos! Pero es verdad?
La verdad es que Los datos fueron restaurados, CHECKDB dejó de verter errores, incluso el sol miró por la ventana. Puede elogiarse con seguridad, levantar un merecido vaso de bebida de húsar y ... recuerde que los datos en la base de datos están conectados no solo con enlaces a páginas. Entonces, es hora de recoger
DBCC CHECKCONSTRAINTS y sumergirse de
lleno en la lista de claves foráneas rotas. Pero esta es una historia completamente diferente ...
¿Qué más leer sobre el tema?
- Sintaxis del comando CHECKDB (¡Preste atención a las advertencias sobre los posibles riesgos de usar el comando!)
- La descripción no oficial de DBCC PAGE
- Un buen artículo sobre índices MS SQL que explica muchas cosas interesantes. Incluyendo cómo se almacenan físicamente los índices en la base de datos