somits
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also tell us how much free space exists on the drives where the data files and/or log files reside?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
ASKER
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
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
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.
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.
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.