Draconian (7/24/2014)
That was confusing. On the Database list it was showing Recovery Pendig. When I checked status it was in suspect mode.SELECT DATABASEPROPERTYEX (N'<dbName>', N'STATUS');
I made enough space on server by moving other databases to different disk. I brought the db in emergency mode and then tried to make it online. and IT WORKED!!
ALTER DATABASE <dbName> SET EMERGENCY;
ALTER DATABASE <dbName> SET ONLINE;
So, I learnt a lesson. Always query database status and dont trust what enterprise manager shows by default along with the database name 😀
Lesson 2, never use DatabasePropertyEx to show a database's status, as it shows the statuses from SQL 2000 and before. Hence why I specifically asked about the status in sys.databases.
From your description of what you did, it was in the recovery_pending state. What you did would not have fixed a suspect database. Setting to emergency wasn't necessary, a simple set ofline, set online would have worked once space was freed up.
It's way too easy to do more damage by doing stuff randomly. Hence why I ask very specific things to be done bit by bit when a DB is damaged or unavailable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability