Suspect database on SQL Server cluster

How do I recover a database that is marked as suspect on a SQL Server cluster? I don't know how to unmark the database and restart the service on the primary cluster (active/passive) without failing it over. The error that I see in the logs is:

Error while redoing logged operation in database 'LIVEDB'. Error at log record ID (483:395:139)


Can someone help right away?!?
LVL 1
itagent007Asked:
Who is Participating?
 
lokeshgm7Commented:
One more suggestion. (Its more like something is better than nothing.

After you start your SQL in single user mode(i have mentioned the steps in the previous post), only one user can connect to SQL.  So open your Query analyzer (Make sure all the other connections like EM or other client connections are closed when you start SQL in single user mode)

1. Run the "sp_resetstatus" stored procedure to clear the SUSPECT database bits.  
2. Run the DBCC DBRECOVER Transact-SQL command by using the IGNOREERRORS option.
DBCC DBRECOVER (‘LiveDB’, IGNOREERRORS)
3. Exit the command prompt once the execution completes. Bring the SQl online from the cluster admin.
4. Check the SQL Server error log and database status to verify that the database is marked SUSPECT.  
5. If the database is not marked SUSPECT, run the DBCC CHECKDB Transact-SQL command to determine if any physical inconsistencies exist, and then repair the physical inconsistencies.

Note Logical inconsistencies may exist even if the DBCC CHECKDB Transact-SQL command uses a repair option to repair the physical inconsistencies. You must determine the data that may be logically inconsistent.  
0
 
itagent007Author Commented:
One more thing to add, I don't have backup of this database, which makes this worse.
0
 
imran_fastCommented:
Did you try restarting the server?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
itagent007Author Commented:
No I have not. I'm new to clusters and have a general idea of how they work. If I restart the server, will it failover to the second node?
0
 
imran_fastCommented:
try
===
EXEC sp_resetstatus 'databasename'

This will reset the suspect flag. However, if it was marked suspect for a reason, SQL Server may just mark it as suspect again
0
 
itagent007Author Commented:
In reading about the sp_resetstatus command, it says I need to restart the SQL Service. How do I do that in cluster administrator without failing over the service to the second node?
0
 
imran_fastCommented:
if the above does not work then

there is a useful trick

1.Detach Your Database.
2.Rename your log file.
3.Create a  blank text document  and rename it as your Log file
4.Then attach the database, point the log as new log file we just created.
0
 
itagent007Author Commented:
If I detach the database and it won't reattach, what other options do I have?
0
 
imran_fastCommented:
>>it says I need to restart the SQL Service

You need to restart sql service not the node it self.

simply go to the services and in control panel administrator tool on the active node and on MSSQLSERVER right click and restart.

0
 
imran_fastCommented:
>>If I detach the database and it won't reattach

why won't?
0
 
itagent007Author Commented:
I was always told never to stop and start services through computer management or sql enterprise manager in a clustered environment, but to do things in cluster administrator. But I've never restarted a service through there before.
0
 
itagent007Author Commented:
Thanks, I'll give all these things a try and let you know what happens.
0
 
lokeshgm7Commented:
Here is how you can restart SQL Server on the same machine.

1. If you have installed Windows cluster admin, Go to start-> Run on any of the two nodes.
2. Type 'Cluadmin' and click Ok. This happens the cluster administrator.
3. Type the name of your windows cluster in the box that will come up (or just type the '.'(period) symbol to enter the cluster adminstrator.
4. Under groups, you will find you SQL group and all the resources.
5. Right click on SQL Server resource on the right pane->Choose Take Offline.
{The database went into suspect mode because it was not able to recover. You generally are in trouble if your database is in suspect and you do not have a previous good back up(Unfortunately this might be true in your case in which case you have corruption and your hopes of getting the database back is simmered).}
6. Now once the SQL Server and SQL agent resource are offline on the cluster, you need to start SQL in a single user mode. (Check SQL Books online on how to start SQL in single user mode)
7. Open the command prompt, navigate to your binn directory in the SQL Server installation folder on your local drive.
8. Run the command SQLSERVR.EXE -c. (This will start SQL in single user mode.)
9. You can check if SQL Server tries to recover the user database which is in suspect mode. If it doesnt, you might as well forget using that database ever.
10. Once the recovery completes, you can exit from the command prompt by typing CTRL+C.
11. Go to the cluster admin -> Right click on SQL and Click Bring Online. This has the same effect as restarting SQL Server on the local Node.


Alternatively, If you wish to start SQL Server from the Enterprise manager or Service manager on the same Node here is what you can do inorder to avoid it from failing over to the second node.

1. Go to cluster admin->Right click on SQL Server resource in the SQL group.
2. Choose properties ->Go to Advanced tab-> Choose the radio button "Do not restart".
3. Once you do that if you start SQL Server using services or EM, it will restart on its own node and not fail over to the second node.

The next you post a question, copy and paste the output  of the SQL error log which contains information about the database in suspect. We can tell you if at all you can recover that database.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.