.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Database goes to suspect mode

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :Sql Server

Hi Team,


Two days back i went one DBA interview he is asked some dba questions 


Q. we have 25 database in this one database is goes to suspect mode. How do you know x database is goes to suspect mode.


      i said we got one alert or mail x database is goes to suspect mode 


my be my answer is wrong could you any body please give me exact answer for this.


Q. we have 14 instances one instance is correpected or it goes to suspect mode how can we know x instance goes to suspect mode ?



Can you any body please guide me for this one.. i m bit confue on this my expectation instance could not goes to suspect mode right can you correct me.






View Complete Post

More Related Resource Links

error 3316 and database in suspect mode


Hi all, 

my database fell into suspect mode. I fixed database using dbcc checkdb in emergency mode and now it seems ok. But I dont know why this incident occurs. does anybody know possible reason?

In error log I found these records:

ex_raise2: Exception raised, major=52, minor=42, state=13, severity=22, attempting to create symptom dump... followed by dump information ... and then errors 3316, 3314, 9001 (in this order)

cluster manager does not register any event about SAN outage

thanks for any answer.


SQL SERver 2000 database is in offline\suspect\Emergency mode



I m having sql server 2000 database and 4-5 database is in offline\suspect\Emergency .

I have tried that to take online throgh following step:

EXEC sp_resetstatus 'DBName'

DBCC checkdb('DBname')
DBCC CheckDB ('DBName')





From this command :

but its not coming online and multiuser then i run this command : UPDATE SYSDATABASES SET STATUS=24 WHERE NAME='DBNAME' and it comes online but whenever sql sever services get restarted it again goes into suspect mode.


Please help somebody?


Database is looking as (Suspect/readonly/offline/emergency/)

Could not open FCB for invalid file ID 0 in database 'mssc_admin' and database is looking as (Suspect/readonly/offline/emergency/) Actually log file is not visibiling and I was trying to restore it.   PLease PLease help"SQLSERVER DBA" "INDIA"

My Sql server 2005 Database in recovery mode for 3 -4 hours every day

Hi , Some of our Sql Server 2005 databases remain in recovery mode after midnight for almost 3 to 4 hours. So when my application access them error "Databse in recovery" and for same database this happens again and again every night. Please suggest why and how this can be solved?

Accessing database in STANDBY mode while log shipping

Hi,     I am testing with Log shipping, I have it setup and it is working just fine. The secondary database was restored with Standby mode and is readonly. I have no problem accessing and querying data from the secondary database but I noticed if changes are made to the primary and I am accessing the database when the restore step to the secondary runs it fails with the following error: Message2006-07-31 09:40:54.33 *** Error: Could not apply log backup file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\CI_REPLICATION_TEST_20060731131501.trn' to secondary database 'CI_REPLICATION_TEST'.(Microsoft.SqlServer.Management.LogShipping) ***2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *** This appears to be because you can not restore to a database that is in use? How do you get around this? Is it possible? How do I query data from the secondary db without worrying about causing log shipping to fail? Do I not worry about it because at somepoint when Exclusive access is obtainable all the logs will be rolled forward, but this could create a wide gap in sychronization between the primary and secondary db's, say if someone or something does not properly log off. I guess I could disconnect all users prior to applying tlogs but that kind

SQL Server 2008 Windows Authentication Mode fails for Database Engine, error 18456

I installed SQL Server 2008 Developer Edition (10.0.1600.22.080709-1414) on a Windows Vista 32-bit development machine with Windows Authentication Mode only (no SQL Server Authentication).   Since this is a development machine, I saw no need for Mixed Mode when I did the install.  The SQL Server Management Studio allows me to login to Analysis Services, Integration Services, Reporting Services, BUT NOT the Database Engine!Windows Authentication for the Database Engine gives the following error: EventID: 18456Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]The 'fix' I have found online is to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine.  Unfortunately, I can't since I didn't install SQL Server Authentication mode (only Windows Authentication Mode).  It appears my only recourse is to uninstall, then reinstall in Mixed mode, then to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine. Before I do so, does anyone know of a better approach?Scott D Duncan

MSSQL 2008 R2 - database in restoring mode after backup - forever..

Hi folks,   i have this problem that occourred today with one instance of MSSQL 2008 R2 running on a WIN x64 R2 enterprise server.   i have run a backup maintainance job and now i have two databases in restoring mode for over 4hrs.   my questions are: why are these databases restoring when i was backing up? and why are they not being logged anywhere - i can not find anything on logs, activty monitor, or even Windows Resource Monitor.   i can not see any work been done by MSSQL to restore these databases, and yet they are set as 'RESTORING'.   any suggestion from the community here will be much appreciated as i am banging my head against the wall already.   thanks,   Nicolas    data warehouse | data migration | ETL/ELT | UK | http://www.brainpowered.net

Database peaks CPU after recovery. Stuck in recovery mode

Rather simple problem. The database is stuck in recovery mode and I can't use it.. mssqlserver process want 100% CPU. I left it in recovery mode for almost a week with no luck. My idea was that it would repair itself. But it doesn't help and now I'm about to give up on that database. It seems quite odd that the last log entries in the ERRORLOG says: "Recovery complete..  phase 2 of 3" and then it says "Recovery 97% complete ..phase 3 of 3" After that, CPU peaks. Any ideas? Thank you! ERRORLOG: 2009-09-14 15:52:26.89 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)     Nov 24 2008 13:01:59     Copyright (c) 1988-2005 Microsoft Corporation     Express Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 2009-09-14 15:52:26.89 Server      (c) 2005 Microsoft Corporation. 2009-09-14 15:52:26.89 Server      All rights reserved. 2009-09-14 15:52:26.89 Server      Server process ID is 8860. 2009-09-14 15:52:26.89 Server      Authentication mode is WINDOWS-ONLY. 2009-09-14 15:52:26.89 Server      Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. 2009-09-14 15:52:26.89 Server      This instance of SQL Server last reported using a process ID of 7996 at 9/14/2009 3:15:48 PM (local) 9/14/2009 1:15:48 PM (UTC). This is an informational message only; no user action is required. 2009-0

Repair SQL database that has been marked suspect



I woke up this morning to find my company's SQL database inaccessible and marked suspect.


I have used various t-sql commands to bring it into Emergency mode.


Now it is labelled Loading\Offline\Read-Only\Emergency Mode.


I'm using SQL Server 2000


I do not have  a backup and I need to get the database up and running as soon as yesterday.



Suspect SQL database


Deall All

I am getting this error message and my SQL 2005 database is showing "SUSPECT". I need to recover the transcations in the latest transcation log.

The log scan number (195881:359:1) passed to log scan in database 'XXXXXX' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Kindly help 

Database Corrupted - Can't get db in single user mode

I have two SQL 2008 R2 databases running on a 64-bit development Machine :
I have created Synonyms in MyDatabase (Table1, Table2, Table3) for three tables in the IMS database.
I am experiencing very poor performance using these Synonyms from my application so I am trying to troubleshoot the problem.
I tried to run "DBCC CHECKDB (IMS,  REPAIR_ALLOW_DATA_LOSS)" by performing the following steps
  1. Used Services to stop the processes (Instance, Agent, Browser, Integration Services 10.0, VSS Writer)
  2. Ran cmd.exe "As Administrator"
  3. cd C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn
  4. ran sqlservr.exe -m -s SQLEXPRESS (output says it started in single user mode)


  1. Ran cmd.exe "As Administrator"
  2. cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn
  3. ran sqlcmd.exe -S D

database stuck in emergency mode error 952


Please help.  Database is stuck in emergency mode and cannot enter into single_user.  Please help!!

Dr. William Sewell

SQL Server 2000 Database in "single user mode"


Hi all,

I've an old production database server with SQL Server 2000 SP4, that runs from 6 years. Now sometimes I find that the production database goes in "single user mode". Why? My error logs don't help me.

Corrupted database , Emergency mode Sql 2000


Dear all

i'm in a situation where my database is in a suspect mode ,and that was happen after restoring a system ghost and trying to reattach the database .after some googling .. i had turn the mode into emergency mode and tried to run the command


but it throws error that DB must be in a single mode , the thing is i have already run "sp_dboption mydb, single, true" before the DBCC command,

i have also tried to create a DTS and copy sql objects but that didn't work too.

last thing that i have run DBCC CHECKDB(MYDB) and return that error

Table error: Database ID 8, object ID 2, index ID 0. Chain linkage mismatch. (1:33)->next = (1:37), but (1:37)->prev = (1:9976).

which i don't know what that mean .. so any sugesting


Is realtime SQL Server Replicated database usable realtime (not offline / single user mode)


Is SQL Server replicated (destination) database replicated by SQL Server Replication fully usable? Meaning it's not just for Disaster Recovery but is fully usable from various say BI / Reporting purposes.

In the essence it is exactly same as other replication engines and the destination database is very much usable and not just offline / single user mode.



SMO Database in Single User mode issues


I am trying to use the SMO library and setting the single user mode. Here is a simple example illustrating my issue.

The below code will fail the first time. Saying "Invalid object name TestTable". Eventhough that table does exist and so does the DB. However, if you run it again (now the DB is already in single user mode) everything works fine. Also, if you remove the code that alters the db user mode everything works fine. I am assuming the problem must exist with the single user mode and utilizing the correct db connection. I have tried flipping the AutoDisconnectMode and NonPooledConnection but that hasn't help. Any help would be appreciated.

Sample code

SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(); 

sb.Password = "P@ssword!"; 

sb.UserID = "testuser"; 

sb.DataSource = @".\mssql2008"; 

sb.InitialCatalog = "TestDB"; 

ServerConnection serverConn = new ServerConnection(); 

//serverConn.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect; 


'Suspect' Database



If a database is marked suspect and good backup copies of the database and TLog are available, what steps should be taken to successfully recover the database by restoring the database and Tlog backups?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend