Emergency!!! "Suspect" Database

I have a database that is no longer working.  When I go into Management Studio it says "Suspect" next to the db.

How do I proceed?
hrolsonsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tj aCommented:
Hi hrolsons,

I think this might be a useful article for you,
http://www.sql-server-performance.com/2012/recovery-sql-server-suspect-mode/

Hope it helps.
0
PadawanDBAOperational DBACommented:
Paul Randall's material on this topic is fantastic: http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/.

Before you immediately start doing stuff to it, here's a snippet of *really* useful information from that blog: never detach a suspect database.

Edit: Also.  The safest way, if you can't just reset the status and bring it back online is to restore it from backups.  Putting a db in emergency mode and running a checkdb with repair_allow_data_loss is your very last resort.
0
hrolsonsAuthor Commented:
I issued this command with success:
ALTER DATABASE
Photos SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

Open in new window


But got an error on this:
DBCC checkdb('Photos','Repair_Rebuild')

Open in new window


Error was:
Msg 924, Level 14, State 1, Line 1
Database 'Photos' is already open and can only have one user at a time.

Open in new window

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.

PadawanDBAOperational DBACommented:
Try and find out where the connection is coming from so you can gracefully terminate it:

select
     session_id,
     host_name,
     program_name,
     host_process_id
from
     sys.dm_exec_sessions
where
     db_name( database_id ) = N'Photos';

Open in new window


The host process ID is the PID on the remote machine, which will help you find out what process is occupying your single connection to that database.  Try and stop it as gracefully as possible.
0
hrolsonsAuthor Commented:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'database_id'.

Open in new window

0
PadawanDBAOperational DBACommented:
Whoops.  database_id wasn't added until 2012.  try this:

select
     spid,
     hostname,
     program_name,
     hostprocess
from
     sys.sysprocesses
where
     db_name( dbid ) = N'Photos';

Open in new window


sysprocesses is deprecated, but it's a little easier to get at the dbid and the session id in the same view that way.
0
hrolsonsAuthor Commented:
sysprocesses code above returned 0 Rows, but still get error on:

DBCC checkdb('Photos','Repair_Rebuild')

Open in new window


Msg 924, Level 14, State 1, Line 1
Database 'Photos' is already open and can only have one user at a time.

Open in new window

0
hrolsonsAuthor Commented:
Ended up re-booting and ran the rebuild and got:

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.photo_detail_new' and the index name 'PK_photo_detail_new_try'. The duplicate key value is (2004707587).
DBCC results for 'Photos'.

Open in new window

0
PadawanDBAOperational DBACommented:
Is that index new or has it been there for a while? (i only ask because the naming is kinda weird).  Is the database in a state where you can start looking at scripting out the create for the index and primary key, drop the index and pk, and then investigate whether or not there are duplicate primary key values, resolve that, and then re-create the index and pk?  I am assuming that it's complaining about duplicate values for the PK, based on the name (since there's no schema info).  Also, is that the clustered index that its referring to?  If this is a large table, dropping that could be a very bad idea from a performance standpoint (removing the clustered index turns it into a heap, which is going to get ugly for large tables).  Also, what's your backup situation looking like - that may honestly be the fastest method?  It also kinda determines how aggressive you can be with this and I honestly can't say I'm comfortable recommending anything without knowing that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hrolsonsAuthor Commented:
It is an index that I created, but it was over a year ago.  Not sure what you consider large so I've incuded a screenshot below.

Screenshot
0
hrolsonsAuthor Commented:
I am the only one with access to the db right now while I work on it.
0
PadawanDBAOperational DBACommented:
What is your backup situation like?
0
hrolsonsAuthor Commented:
I was able to delete the index, clear up the duplicate keys and re-create the indexes.  Not sure how the db allowed a double unique index in the first place but things seem to be back to normal.
0
PadawanDBAOperational DBACommented:
Well there you go!  And it shouldn't have allowed that.  Make sure you run another checkdb and take a full backup ASAP (I would also recommend trying to store your backups on a different set of drives than the SQL Server's).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.