Recuperacion son sp_resetstatus y DBCC DBRECOVER

Un problema típico en Administración de Bases de Datos SQL Server, es encontrar una Base de Datos Sospechosa (Suspect). Una base de datos está en estado Sospechosa (Suspect) cuando SQL Server no es capaz de garantizar la integridad de sus datos, siendo este un error habitualmente relacionado con problemas de acceso a disco, y con caídas no ordenadas de SQL Server (ej: pérdida del suministro eléctrico que pueda provocar corrupción o pérdida de información de los discos). Cuando una base de datos está en estado Sospechoso (Suspect), no es posible acceder a la misma ¿Cómo reparar una Base de Datos Sospechosa (Suspect)? ¿Qué hace sp_resetstatus? ¿Cómo ejecutar sp_resetstatus y DBCC DBRECOVER? ¿Es necesario reiniciar la instancia? ¿Cómo establecer el Modo de Emergencia para extraer datos?

Este artículo está orientado al problema de Base de Datos Sospechosa (Suspect) en SQL Server 2000, quedando fuera de alcance el problema de Base de Datos Sospechosa (Suspect) en SQL Server 2005, al existir diferencias en el procedimiento de recuperación. Bases de Datos en Estado Sospechoso (Suspect) ¿Qué es eso de "Sospechoso"? ¿A qué se debe? Cuando SQL Server detecta problemas de integridad en los ficheros de en una base de datos, marca dicha base de datos como Sospechosa (Suspect), de tal modo, que a partir de dicho momento no será posible acceder a dicha base de datos (digamos que es un método de autodefensa ;-). Este comportamiento (marcar la base de datos como sospechosa y prevenir que se pueda acceder a dicha base de datos), tiene carácter preventivo, debido a que habitualmente este tipo de problemas de integridad suelen venir motivados por problemas o errores de acceso a disco, y en estos escenarios lo mejor es parar los motores (madrecita, que me quede como estoy ;-), ya que mantener el acceso a una base de datos en dicho estado sólo podría generar aún más problemas. En este caso (fichero o ficheros corruptos), será necesario restaurar una copia de la base de datos, aunque quizás pueda interesar previamente poner la base de datos en modo de emergencia para realizar una descarga de los datos de las tablas (con la esperanza de poder salvar todo aquello susceptible de ser salvado, por si en un futuro, es necesario). Este no es el único escenario posible, existiendo otros escenarios como que durante el arranque de la instancia no se consigue acceso exclusivo a los ficheros de base de datos (ej: por la realización de un backup, por la eliminación de los ficheros, etc.). Otra razón por la que se puede poner una base de datos sospechosa, es por problemas de lectura de los ficheros (ej: problemas con drivers). En este caso, SQL Server puede pensar que realmente tiene algún fichero corrupto (poniendo la base de datos en estado sospechoso), cuando realmente no existe tal problema. En este caso, se deberá solucionar el problema de lectura de ficheros (ej: actualizar drivers de acceso a disco) y quitar la marca de sospechosa (suspect) de la base de datos (tuve un caso con un SQL Server 2000 con discos iSCSI por un Target Cisco que montaba discos de un Storage de HP, una EVA 5000). El aspecto que muestra una base de datos en estado Sospechoso (Suspect) en el Enterprise Manager (EM) es el siguiente: A continuación se muestra un ejemplo del ERRORLOG, durante el inicio de una Instancia de SQL Server con una Base de Datos Sospechosa (Suspect): 2008-04-02 17:50:26.54 server Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 2008-04-02 17:50:26.54 server Copyright (C) 1988-2002 Microsoft Corporation. 2008-04-02 17:50:26.54 server All rights reserved. 2008-04-02 17:50:26.54 server Server Process ID is 1680. 2008-04-02 17:50:26.54 server Logging SQL Server messages in file 'D:\ MSSQL\log\ERRORLOG'. 2008-04-02 17:50:26.59 server SQL Server is starting at priority class 'high'(4 CPUs detected). 2008-04-02 17:50:26.67 server SQL Server configured for thread mode processing. 2008-04-02 17:50:26.68 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks. 2008-04-02 17:50:26.74 server Attempting to initialize Distributed Transaction Coordinator. 2008-04-02 17:50:39.25 spid3 Starting up database 'master'. 2008-04-02 17:50:40.65 server Using 'SSNETLIB.DLL' version '8.0.2039'. 2008-04-02 17:50:40.65 spid5 Starting up database 'model'. 2008-04-02 17:50:40.70 spid3 Server name is 'VSQL01'. 2008-04-02 17:50:40.70 spid8 Starting up database 'msdb'. 2008-04-02 17:50:40.70 spid9 Starting up database 'GuilleSQL'. 2008-04-02 17:50:40.74 server SQL server listening on 192.168.69.61: 1433. 2008-04-02 17:50:40.74 server SQL server listening on 127.0.0.1: 1433. 2008-04-02 17:50:40.79 server SQL server listening on TCP, Shared Memory, Named Pipes. 2008-04-02 17:50:40.81 server SQL Server is ready for client connections 2008-04-02 17:50:41.42 spid5 Clearing tempdb database. 2008-04-02 17:50:42.01 spid5 Starting up database 'tempdb'. 2008-04-02 17:50:46.14 spid9 Bypassing recovery for database 'GuilleSQL' because it is marked SUSPECT. 2008-04-02 17:50:46.76 spid3 Recovery complete. 2008-04-02 17:50:46.76 spid3 SQL global counter collection task is created. 2008-04-02 17:50:53.70 logon Login failed for user 'NT AUTHORITY\SYSTEM'. 2008-04-02 17:50:54.23 spid51 Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'. 2008-04-02 17:50:55.54 logon Login failed for user 'NT AUTHORITY\SYSTEM'. En cualquier caso, si detectamos una base de datos en estado Sospechoso (Suspect) ¿Qué podemos hacer? ¿Cómo debemos actuar? ¿Cómo recuperar una base de datos en estado Sospechosa (Suspect)? Base de Datos en Modo de Emergencia, extracción masiva de datos como último recurso ante corrupción de datos Antes de nada, evidentemente deberemos verificar si tenemos o no problemas de acceso disco, tanto comprobando la existencia de errores en el Visor de Sucesos del Sistema, como asegurarnos si se ha subido de versión de Firmware o Drivers (ya sea el servidor, o alguno de los elementos de Almacenamiento, como ocurre con entornos de redes de almacenamiento SAN e iSCSI). Es importante, determinar: Si existen pérdidas momentáneamente del acceso a disco. Por ejemplo, por cortes de red en una Red de Almacenamiento SAN o iSCSI. Si no es posible el acceso a los ficheros de la base de datos. Puede que estén siendo accedidos por un software de copia de seguridad, se hubiesen renombrado o cambiado de ubicación, etc. Si existen evidencias de corrupción de los ficheros de base de datos. En cualquier caso, Vamos a empezar por el peor de los escenarios. Si por más que intentamos, no conseguimos reparar una Base de Datos en estado Sospechoso (Suspect) que tenemos identificada con problemas de corrupción en sus ficheros, será necesario recuperar la copia de seguridad más reciente. Previamente, podemos intentar poner la base de datos en modo de emergencia, de tal modo, que podamos intentar acceder a dicha base de datos para realizar una descarga del contenido de las tablas (ej: con la utilidad BPC.EXE, con DTS, con SSIS, etc.), generación de Scripts de base de datos, etc. De este modo, quizás podamos recuperar alguna información posterior a la última copia de seguridad, que pueda resultarnos de valor. En versiones anteriores a SQL Server 2005, era necesario modificar directamente las tablas del sistema (en particular, actualizar el campo status de la tabla sysdatabases de la base de datos master) para establecer el Modo de Emergencia (Emergency Mode) en una base de datos. De hecho, creo que no está soportado hasta SQL Server 2005. A continuación se muestra un ejemplo, de cómo establecer el Modo de Emergencia en una base de datos SQL Server 2000. USE master GO EXEC SP_CONFIGURE 'Allow updates', 1 GO RECONFIGURE WITH OVERRIDE GO UPDATE sysdatabases SET status = status | -32768 WHERE name='GuilleSQL' GO EXEC SP_CONFIGURE 'Allow updates', 0 GO RECONFIGURE WITH OVERRIDE GO Sin embargo, a partir de SQL Server 2005 es posible establecer el modo de emergencia en una base de datos con el comando ALTER DATABASE, para lo cual, puede utilizarse una sentencia del tipo ALTER DATABASE SET EMERGENCY (ej: ALTER DATABASE GuilleSQL SET EMERGENCY). ¿Qué es el Modo de Emergencia de una base de datos en SQL Server? Una base de datos en Modo de Emergencia sólo es accesible por los Inicios de Sesión miembros de SysAdmin. Además, en una base de datos en Modo de Emergencia sólo pueden realizarse accesos de sólo lectura (no es posible realizar modificaciones sobre una base de datos en Modo de Emergencia) y no utiliza el Log(este detalle es la caña, porque así podremos recuperar el acceso a una base de datos que halla perdido el Log, o lo tenga corrupto). En consecuencia, si tenemos una Base de Datos Sospechosa (Suspect), podemos intentar poner dicha base de datos en Modo de Emergencia (Emergency Mode), con la esperanza de conseguir acceder a su contenido, realizando descargas del contenido de sus tablas, etc. En las pruebas realizadas, tras poner la Base de Datos Sospechosa (Suspect) en Modo de Emergencia (Emergency Mode), mostraba el siguiente aspecto en el Enterprise Manager (EM) de SQL Server 2000. Ojo que hay truco. Vale, la base de datos sigue en Gris, y no tiene muy buena pinta, pero por el contrario, ahora si podemos acceder a su contenido, como se muestra en la siguiente pantalla capturada del Enterprise Manager (EM). Es más, una vez en el Modo de Emergencia (Emergency Mode), si tenemos certeza de que los único problemas de nuestra base de datos afectan al fichero o ficheros de Log (como es el caso de estas pantallas, pues para simular el Modo de Emergencia, renombré el fichero de Log), podríamos volver a regenerar los ficheros de Log con el comando no documentado DBCC REBUILD_LOG, ejecutando algo similar a lo siguiente: DBCC REBUILD_LOG(GuilleSQL,'D:\SQL2000\Data\GuilleSQL_Log.LDF') En las pruebas realizadas, tras la ejecución del comando DBCC REBUILD_LOG, la base de datos se quedó en modo de acceso restringido para DBO, como se muestra en la siguiente pantalla capturada del Enterprise Manager (EM) de SQL Server 2000. Recuperar una Base de Datos Sospechosa (Suspect) que no tiene corrupción de datos: sp_resetstatus y DBCC DBRECOVER A continuación, vamos explorar las posibilidades de quitar la marca de Sospechoso (Suspect) a la base de datos, una acción de interés cuando tenemos certeza de que la base de datos no tiene problemas de corrupción, por ejemplo, porque hemos detectado que el motivo del estado Sospechoso eran pérdidas eventuales del acceso a los discos, por problemas de drivers. En este caso, es posible quitar la marca de Sospechosa (Suspect) de la base de datos con el procedimiento almacenado sp_resetstatus. El problema de utilizar el procedimiento almacenado sp_resetstatus, es que requiere reiniciar la Instancia de SQL Server, como puede comprobarse en la documentación del producto (es decir, en los Libros en Pantalla ó BOL: Books On Line). El hecho de tener que reiniciar la instancia de SQL Server, es debido a que sp_resetstatus se limita a cambiar el estado de la base de datos, como bien puede verse al consultar el código fuente de dicho procedimiento almacenado (vamos, que hace un update de la tabla sysdatabases de master, a capón). El procedimiento sp_resetstatus no hace nada más, por lo tanto, si tenemos realmente un problema de integridad, nos estaremos engañando a nosotros mismos, y por eso, se requiere reiniciar la instancia completa de SQL Server, de tal modo que durante el inicio de la instancia al levantar las bases de datos se vuelva a comprobar el estado de integridad de la base de datos, y en caso de que se vuelvan a detectar problemas de integridad en dicha base de datos, se vuelva a establecer la base de datos en estado Sospechoso (Suspect). Con esto, el procedimiento a seguir para quitar el estado Sospecho (Suspect) de una Base de datos SQL Server, sería el siguiente: USE master GO EXEC SP_CONFIGURE 'Allow updates',1 GO RECONFIGURE WITH OVERRIDE GO EXEC sp_resetstatus 'GuilleSQL' GO -- OJO -- Reiniciar la instancia de SQL Server USE master GO EXEC SP_CONFIGURE 'Allow updates',0 GO RECONFIGURE WITH OVERRIDE GO Este procedimiento para quitar el estado Sospecho (Suspect) de una Base de datos SQL Server, nos puede resultar de gran ayuda, pero tiene un gran inconveniente: es necesario reiniciar la instancia. Bueno, el inconveniente realmente es relativo, es decir, si tenemos una instancia para un único uso (ej: SAP, MOSS, SMS, etc.), el reinicio de la Instancia no será problema si partimos de que tenemos la base de datos inaccesible (al estar la base de datos en estado sospechoso). Sin embargo,si tenemos una Instancia con múltiples y dispares bases de datos (típico servidor consolidado de SQL Server) nos enfrentaremos a un corte de servicio que no será de mucho agrado. En este caso, ¿Qué hacemos? ¿Tenemos alguna alternativa? En teoría, en este caso podemos hacer uso del comando no documentado DBCC DBRECOVER tras la ejecución del comando sp_resetstatus. El comando DBCC DBRECOVER permitirá levantar y recuperar la base de datos de forma similar a como se hace durante el inicio de la instancia, de tal modo que no sea necesario reiniciar la instancia de SQL Server. De este modo, el procedimiento a seguir sería el siguiente (bajo la responsabilidad de cada uno, que DBCC RECOVER es un comando no soportado ;-) USE master GO EXEC SP_CONFIGURE 'Allow updates',1 GO RECONFIGURE WITH OVERRIDE GO EXEC sp_resetstatus 'GuilleSQL' GO DBCC DBRECOVER('GuilleSQL') GO USE master GO EXEC SP_CONFIGURE 'Allow updates',0 GO RECONFIGURE WITH OVERRIDE GO Para mayor tranquilidad, podremos revisar la información de ERRORLOG de SQL Server, observando los mensajes mostrados, que será similares a los siguientes (si todo va bien): 2008-04-02 17:53:06.98 logon Login failed for user 'NT AUTHORITY\SYSTEM'. 2008-04-02 17:54:32.44 spid53 Error: 15457, Severity: 0, State: 1 2008-04-02 17:54:32.44 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.. 2008-04-02 17:54:32.50 spid53 Error: 15457, Severity: 0, State: 1 2008-04-02 17:54:32.50 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.. 2008-04-02 17:54:59.00 spid53 Error: 15457, Severity: 0, State: 1 2008-04-02 17:54:59.00 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.. 2008-04-02 17:54:59.07 spid53 Starting up database 'GuilleSQL'. 2008-04-02 17:55:51.62 spid53 Analysis of database 'GuilleSQL' (5) is 100% complete (approximately 0 more seconds) 2008-04-02 17:58:55.54 spid53 Recovery is checkpointing database 'GuilleSQL' (5) 2008-04-02 17:58:56.71 spid53 Error: 15457, Severity: 0, State: 1 2008-04-02 17:58:56.71 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.. Y poco más. Hasta aquí llegamos con este tema, que por gracia o desgracia, de vez en cuando nos toca pasar por él. Espero que la información que aquí os pongo, os resulte de ayuda e interés.

Volver a: [SQL Server FAQ :: Preguntas y Respuestas Frecuentes de SQL Server :: Manual SQL Server]

[Fecha del Artículo (UTC): 04/06/2009]
[Autor: GuilleSQL]

Comentarios

leikeze - 20/09/2011 (UTC) hola buen dia, no se si me puedan ayudar, yo tengo una base de datos en sql server 2005, a la cual hago consultas desde todos los equipos de mi red pero al querer hacer un cambio, ya sea guargar o eliminar un registro de la BD me marca el siguiente error, cabe mencionar que desde algunos equipos si puedo hacer dichos cambios y en otros no, los equipos que si pueden son equipos con xp sp3 y los que no se pueden conectar son xp sp3,win7, xp 64 bits, de antemano gracias y ojala se pueda jeje

Gatubela - 08/11/2011 (UTC) Hola buena tarde, tengo una BD en suspect, pero al ejecutar las sentencias use master go exec sp_configure 'Allow updates',1 go reconfigure with override go update sysdatabases set status = 32768 where name = 'my_base' GO exec sp_configure 'ALLOW updates',0 go reconfigure with override go manda los siguientes mensajes: Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78 The configuration option 'Allow updates' does not exist, or it may be an advanced option. Valid configuration options are: Server: Msg 259, Level 16, State 2, Line 1 Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this. Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78 The configuration option 'ALLOW updates' does not exist, or it may be an advanced option.