the BiTTer and SwEEt of Me
tarian jari-jemari diatas tuts keyboard…

Restoring a ‘SUSPECT’ database on SQL Server 2005

The story begun with an unexpected server problem,  my database crashed, its status changed into ‘SUSPECT’, if we would trace back further, this accident seems to be an impact of the electrical failure.

I remember once in the past, Anastasia guided me how to handle SQLServer, and if there’s a ‘suspect’ database there are steps to be done:
1. Turn off the SQL Server’s service
2. Copy and backup .MDF and .LDF file of the ‘suspect’ database
3. Turn on the service again
4. Detach the database, this might delete the master .MDF and .LDF files
5. Re-attach the .MDF file using an SQL script: sp_attach_single_file_db. A complete resource can be found in here: http://msdn2.microsoft.com/en-us/library/ms174385.aspx

6. After the .MDF is attached, the .LDF or log file will be created automatically.

The solution can also be applied when your database log files is/are beginning to explode.

But on my case yesterday, that solution didn’t work. Perhaps the solution didn’t work on SQL Server 2005, under MS. Windows 2003 x64.

When i tried this script:

sp_attach_single_file_db [ @dbname= ] 'dbname', [ @physname= ] 'physical_name'

then the result was:

File activation failure. The physical file name “C:\xxxxxx\xxxxxx_log.ldf” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘XXXXXX’. CREATE DATABASE is aborted.

grmmbll…how can it be, i thought…then i tried another weapon:

sp_attach_db [ @dbname= ] 'dbname'        , [ @filename1= ] 'filename_n' [ ,...16 ]

Alas!!..enemies were still alive..
Msg 1813, Level 16, State 2, Line 1Could not open new database ‘XXXXXX’. CREATE DATABASE is aborted.
Msg 3316, Level 21, State 1, Line 1
During undo of a logged operation in database ‘XXXXXX’, an error occurred at log record ID (257:73855:17). The row was not found. Restore the database from a full backup, or repair the database.

I got more headaches, so i used my ultimate missile, which means, using the SQLServer 2005’s wizard:
But there came yet another error:

TITLE: Microsoft SQL Server Management Studio
——————————

Attach database failed for Server ‘QQQQQQQQQ’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
During undo of a logged operation in database ‘XXXXXX’, an error occurred at log record ID (257:73855:17). The row was not found. Restore the database from a full backup, or repair the database.
Could not open new database ‘XXXXXX’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 3316)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3316&LinkId=20476
——————————
BUTTONS:
OK
——————————

Oh no, so what should i do?..i began to scratch my head as if i got my head itchy..
after lurking around the web, i found some links which potentially have the solutions:
1. http://www.webservertalk.com/archive132-2006-3-1445098.html
2. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=630182&SiteID=1
3. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22465639.html

MOre or less, the solutions are alike:

1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:

   Use Master
   Go
   sp_configure ‘allow updates’, 1
   reconfigure with override
   Go

3. Set the database in Emergency (bypass recovery) mode:

   select * from sysdatabases where name = ‘<db_name>’
   — note the value of the status column for later use in # 6
   begin tran
   update sysdatabases set status = 32768 where name = ‘<db_name>’
   — Verify one row is updated before committing
   commit tran

4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a “blank” log file based on the
suspected db.
      The syntax for DBCC REBUILD_LOG is as follows:

   DBCC rebuild_log(‘<db_name>’,'<log_filename>’)

   where <db_name> is the name of the database and <log_filename> is
   the physical path to the new log file, not a logical file name. If you
do not
   specify the full path, the new log is created in the Windows NT system
root
   directory (by default, this is the Winnt\System32 directory).

6. Set the database in single-user mode and run DBCC CHECKDB to validate
   physical consistency:

   sp_dboption ‘<db_name>’, ‘single user’, ‘true’
   DBCC checkdb(‘<db_name>’)
   Go
   begin tran
   update sysdatabases set status = <prior value> where name = ‘<db_name>’
   — verify one row is updated before committing
   commit tran
   Go

7. Turn off the updates to system tables by using:

   sp_configure ‘allow updates’, 0
   reconfigure with override
   Go

A big BUT come…but…it didn’t work too..the server shouted at me…

“Ad hoc updates to system catalogs are not allowed.”

I found that in SQL 2005 we can’t update System Tables directly

 (http://www.webservertalk.com/archive132-2006-3-1445098.html)

What should I do then?!

8 Responses to “Restoring a ‘SUSPECT’ database on SQL Server 2005”

  1. I’ve been looking for this precise information on this subject for a while.

  2. we have a problem in one of our database the error is occured when, we run checkdb command or any alter statement.

    Error is given below: Please help me

    Msg 211, Level 23, State 51, Line 1
    Possible schema corruption. Run DBCC CHECKCATALOG.
    Msg 0, Level 20, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discarded.

  3. Msg 7986, Level 16, State 1, Line 1
    System table pre-checks: Object ID 4 has cross-object chain linkage. Page (1:660) points to (1:13648) in alloc unit ID 72057610633871360 (should be 262144). Check statement terminated due to unrepairable error.
    DBCC results for ‘wizappsk’.
    CHECKDB found 0 allocation errors and 0 consistency errors in database ‘wizappsk’.

  4. I always emailed this blog post page to all my associates, for the reason that if like to read it after that my links
    will too.

  5. Fine way of explaining, and good paragraph to get data concerning
    my presentation subject, which i am going to present in academy.

  6. Thank you for every other informative website. Where else may just I get that
    kind of info written in such a perfect approach? I’ve a mission that I am simply now working on, and I have been at the glance out for such info.

  7. Hmm is anyone else having problems with the pictures on this blog loading?

    I’m trying to determine if its a problem on my end or if it’s the blog.
    Any suggestions would be greatly appreciated.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: