MS-SQL Database Reported as “Suspect”

by John - Published: November 28th, 2008

Yesterday I received a support call to say that my program was not working. Great, I thought… that tells me a lot!

Finally the user told me which screen he was on and what the message was displayed. It was “Cannot Connect to Database”. This is a program with a MS-SQL 2000 database. Other programs connecting to different databases were working fine, so the Server was running and they could connect OK.

I managed to get hold of the on-site office manager by phone. I managed to get him to re-locate to the server room, and start the Enterprise Manager. He had no SQL skills and had never used any SQL tools before yesterday. After coaching him for what seemed like an eternity, I was able to have him work in the left-hand pane, and navigate the tree structure there in a half useful manner.

At first attempt, we found a SQL Server Group, and under that there were two SQL Server instances running. He reported the first had no databases, and apart from the master model temp etc.. the second had a few that he half recognised. Things were not looking good, I thought.

It looked like I would need to drop by in person.

I got some stuff ready and phoned him again before I dropped by. I had him go back to the server and get me a few more details. In the meantime, I think he had practiced navigating a windows directory tree.. Now he was able to find all my other databases showing under the 1st listed server. Why he could not see these an hour earlier was a mystery.

Now the problem was evident.. The database in question was flagged “Suspect” within MS-SQL Enterprise Manager. So I did a few on-line searches and found several suggested “fixes”. The one that I had on-site manager try was the one involving sp_resetstatus dbnamehere

That did not work!

Then I read that a critical step was to review the SQL log to see what reason shows up there for the “Suspect” database.

On-site this morning, and I hunted down the appropriate log and quickly found the problem.

The data .MDF file was not where MS-SQL expected to find it. Some moron had moved it!

The Fix:

I got permission to STOP the SQL Server, moved the file to the correct folder, and re-started the SQL Server.

Voila… All working again :-)

John Griffiths

 

 

 

 

 

Comments: No Comment - Category: Programming