Suspect SQL Server 2000 Database

By:   |   Updated: 2007-03-19   |   Comments (3)   |   Related: More > SQL Server Configurations

Problem
I have a SQL Server 2000 database that has the wrong database status.  For some reason it is in the 'Suspect\Offline' mode.  I just need to correct the problem quickly and get my database back online.  How can I do so?   Once the status is correct, do I need to take any further steps? 

Solution
To cut to the chase, the script below can be used to correct the SQL Server 2000 database status, but in reality this is only a third of the equation.  We also need to correct any sort of corruption and understand why this occurred in the first place. 

Database Status Correction Script

The script below will change the database to be in simple recovery mode, which may or may not be the needed configuration for your database.  As such, it is necessary to review your database configurations once this script has been executed.  In addition, it is necessary to change the 'YourDatabaseName' to your database name in single quotes.

USE Master
GO

-- Determine the original database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO

-- Enable system changes
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO

-- Update the database status
UPDATE master.dbo.sysdatabases
SET Status = 24
WHERE [Name] = 'YourDatabaseName'
GO

-- Disable system changes
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO

-- Determine the final database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO

Check for Corruption

The next step in this process is very key.  It is necessary to determine if the database has any corruption and ensure that the database will be able to support the users.  If the database has corruption, you may be exposing yourself to more issues by just changing the database status without correcting the underlying issue.  To identify the underlying issue, execute the following commands:

  • DBCC CHECKDB - Validate the overall database integrity
  • DBCC CHECKCATALOG - Validate the system catalog integrity
  • DBCC CHECKTABLE - Validate the integrity for a single table

To resolve the issue, you may need to do one or more of the following:

  • Drop and Recreate Index(es)
  • Move the recoverable data from an existing table to a new table
  • Update statistics
  • DBCC UPDATEUSAGE
  • sp_recompile

To ensure the issue is corrected, it is a good idea to re-run the identification commands listed above and validate that they do not have any issues.

To address these items, check out the following MSSQLTips.com:

Determine the Root Cause

In the long term, it is imperative to understand what caused the suspect/offline database.  At a minimum the following questions should be addressed:

  • What has recently changed in your environment?
  • Review your SQL Server logs to see if you can determine when the error occurred.
  • Talk to your team members to ask them what changes have been made.
  • Review your change management and auditing processes to see what has changed in SQL Server or at a systems level.
  • See if the issue has occurred on any other databases in your environment.

Next Steps



Last Updated: 2007-03-19


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, May 21, 2014 - 1:51:24 AM - JSanz Back To Top

great tips - thank you.


Monday, October 08, 2012 - 5:24:57 AM - Kale Balkrishna Back To Top

 

Thankyou very much, I am able to recover suspect database after using the commands above.


Monday, November 10, 2008 - 2:22:03 PM - Dominique Back To Top

Hello,

These articles are great as they solved my issues..

But as a local administrator I was not able to see any databases in SQL Enterprise Manager having a login failed for any account I was using.

Only a Domain Administrator was able to access it and let me correct the issue... which permissions was missing for me to be able to do the same correction on SQL without being a domain administrator?

I am dbo on several databases and also sysadmin role on them.

Should I be dbo on master? will it be enough?

Thank you,
Dominique



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools