Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Why a database becomes inaccessible and is not recognized by the SQL Server?

Hi:

When we restarted the SQL server, under the tree of Databases, we found that one of our databases, say DB_1,  is not lighted with "yellow" color. It is gray. Beside its name, there is the text "(suspect)". When we tried to open this database, it is empty. When we tried to add 1 new user, there  is a warning saying that there is one or more databases that are inaccessible.

This database, DB_1, is pretty big: ~17 GB of data. We tried to recover it but could not.

If you know what caused this problem and/or how to recover the database, please help. Any help would be appreciated.

Thanks a lot.

Somits


0
somits
Asked:
somits
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
MikeWalshCommented:
First things first....

On that link, don't go right to detaching the database. Save that for later in the process.. Search Books Online for "Suspect" and look at the topics there.

Try and stop and restart the server once as well. It may be a fluke and sometimes that stop and restart is what you need.

Then try the suggestions in Books Online. Detaching a suspect database can be dangerous, but the accepted answer in that link obviously worked for at least one person out there, so it is an option if needed.
0
 
MikeWalshCommented:
Also tell us how much free space exists on the drives where the data files and/or log files reside?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
imran_fastCommented:
There are many cause for the database to be suspect but in your case it could be space problem
Check Physical space on server.try to delete some of the files. beware not to delete log or mdf files for you database
and then restart the sql services.
0
 
MikeWalshCommented:
That is why I asked for free space. Let us know the free space, and if you have other drives. There are other ways to solve the space problem including a great reccomendation in Books Online under the topics I discussed.
0
 
somitsAuthor Commented:
Hi all:

I've checked the space and found that the drive where the data file is stored has a lot of free space (70 GB). So, maybe the space is not the problem.

Thanks,

Somits
0
 
MikeWalshCommented:
Somits, it could also be an issue with the drive the Log Files are on (unless it is the same drive)..

So as I said, first try and stop and restart SQL server's services. See if it is still suspect, sometimes this actually solves the issue.

If that has failed and the log file device has space and the drive is working fine for both (in other words other databases are using same drive and functioning fine) then you need to move up a step.

Look in the event lgo and application log and SQL Server logs from the time the database was suspect. Look for any issues that may have caused the database to go suspect. See if there is anything solvable there.

Do you have database backups of this database? Is it in Full Recovery mode? If so, attempt a transaction log backup on the database. Then restore in the normal method with Transaction Logs (look up backup restore in Books Online).. If you are not in Full Recovery model and don't have a good history of T-Log backups to revert to, you may have to restore from your last good backup.

If you absolutely can't go to the last backup and don't have a great backup strategy in place that allows you to do a T-Log backup and attempt a restore to before it went suspect, then you have a couple options. The safest is to call Microsoft PSS because they deal with this the most and can walk you through situations and if it is a dangerous situation they will fully warn you and have you sign a waiver potentially.

One thing you can try if the backup/recover routine won't work is this: Try and reboot the machine, when the database comes up use the SP_resetstatus stored procedure to attempt to reset the status of the database in question (look this proc up in Books Online to read details about it). Then stop and restart again. See if this has taken database out of suspect mode. If it has, back it up right then and there, and I would restore it again. I would really investigate the logs for the cause here so you can avoid it happening in the future. It could be a fluke or it could be something serious. You might find some talk about Emergency Mode on the web. I would avoid trying this on your own, and if the data is that necessary, no backups exist and you have to have it to the exact point prior to going suspect, call MS PSS and have them walk you through it.

Do you have an 3rd party backup software backing up your SQL databases? I had this happen once when a backup job hung in the middle of a SQL backup through the 3rd party (which is why I always try to force my will on managers and backup databases within sql or one of the trusted 3rd parties specializing in SQL, then let the network backup products take the files to tape or backup device).. The solution that time was a reboot of both SQL and a stop/start of the backup processes Services.







http://support.microsoft.com/kb/889266
0
 
MikeWalshCommented:
That kb article talks about a situation solved by hotfix, if your error message looks similar this may help, but see if you can follow the steps above first. I would not try and play the detach reattach route though, which is why I didn't mention it. You may be able to stop and start or reboot and be fine. Restore if you can, or sp_resetstatus may help also.. Let us know how this all goes.
0
 
MikeWalshCommented:
Sorry one more. If you see error messages about Recoverynot working like you mentioned in the beggining, remember this does not mean you can't restore your backups. Recovery is the internal process of recovering a database when SQL is brought back up, it is (really basically) bringing the database back up to available for use, it is not restoring a backup. It is simply recovering to the point before shutdown, committing anything that needs committing, rolling back anything that needs rolling back. So don;t let that message scare you from attempting a Restore.

Especially if you are in Full Recovery mode, have been making regular transaction log backups, are abl eto take a current transaction log backup (or are willing to just restore to the last t-log backup and give up the data from then to failure) and the stopping/restarting or reboot don't make a difference.
0
 
MikeWalshCommented:
Somits - have you accessed this database yet? Not sure if you are still hanging out there waiting for help or if some of the answersr helped you out because the question is still in an open state.
0
 
somitsAuthor Commented:
Hi all:

Sorry that I could not involve this for several days because I was on a business trip even though I was very eager to touch back the db server to see what I could do.

Thanks a lot for all the hints, suggestions, and comments you provided. Last night, when I could have some time to look into the db server and had some chance to talk with some guys who has some involvement of this db before (but they're not the admin and I just took over this server), I found out that one of these guys have a backup of this db. So, at least, that seems good news to me - at least I've something to restore.

For questions of MikeWalsh: the drive of the log file --> everything in the same drive, so it should not be problem with the space for the log file.

With the fact: I only have one backuped file of the db. MikeWalsh, you mentioned about "Full Recovery Mode".  How can check if the backup file is in "Full Recovery Mode" or not?

Thanks,

Somits



0
 
MikeWalshCommented:
Well you can check that by right clicking on the database within enterprise manager, and choose properties then look at the options tab, but it sounds like you have not been doing log backups. If you have a backup you can restore I would do that. Then from that point on implement a better backup strategy. Take more frequent backups store them on tape.

Also if your database will have a lot of data and users and performance is a concern you may want to check out http://www.sql-server-performance.com/ they have some great info and tips there. One thing you should do is at least seperate your data files from log files for some performance increase. Check the site out for tips.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now