Post reply

Database not marked as suspect

  • October 20, 2011 at 7:06 am

    #390666

    Hi this is just an observation really. I was practising using the Emergency mode and noticed that when I corrupted a log file and went back into SSMS 2008 R2 I noticed the database was not marked as suspect yet if I ran

    SELECT DATABASEPROPERTYEX ('ForEmergency', 'STATUS') AS 'DBStatus'

    GO

    I could see it was. Do other people experience the same with SSMS?

    Thanks

  • October 20, 2011 at 7:12 am

    #1397312

    I assume you hit refesh on the list of dbs?

    That's the only thing I can think that would cause this.

  • October 20, 2011 at 7:18 am

    #1397317

    I did yes - many times :s

  • October 20, 2011 at 7:22 am

    #1397325

    Since I assume this is a test server, what happened when you rebooted?

    What's in the log for that DB?

  • October 20, 2011 at 7:28 am

    #1397334

    I have only done a service restart not a machine reboot, but in any case my log says things like this, and there is also a dump.

    10/20/2011 11:37:58,spid33s,Unknown,File activation failure. The physical file name "E:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\ForEmergency_log.LDF" may be incorrect.

    10/20/2011 11:37:58,spid33s,Unknown,A file activation error occurred. The physical file name 'E:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\ForEmergency_log.LDF' may be incorrect. Diagnose and correct additional errors<c/> and retry the operation.

    10/20/2011 11:37:58,spid33s,Unknown,Error: 5105<c/> Severity: 16<c/> State: 1.

    10/20/2011 11:37:58,spid33s,Unknown,An inconsistency was detected during an internal operation. Please contact technical support.

    10/20/2011 11:37:58,spid33s,Unknown,Error: 5243<c/> Severity: 22<c/> State: 8.

  • October 20, 2011 at 7:29 am

    #1397337

    Rather than databasepropertyex, query sys.databases. Check the status there.

    My guess, it was recovery_pending. That looks like suspect to database property but won't put the (suspect) tag on the DB. The DB will still be inaccessible

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 20, 2011 at 7:31 am

    #1397338

    1 last shot from me.

    Disconnect from that server and reconnect.

    If that doesn't "fix" it then I have no idea what is the problem.

    Gail is good with those weird trivias... maybe she knows.

    Edit : First documented case of DBCC TIMEWARP(). :hehe:

  • October 20, 2011 at 7:41 am

    #1397349

    Kwisatz78 (10/20/2011)

    I10/20/2011 11:37:58,spid33s,Unknown,File activation failure. The physical file name "E:\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\ForEmergency_log.LDF" may be incorrect.

    Yeah, that looks like recovery_pending, not suspect. So database inaccessible, won't have the (suspect) marker on it, will be RECOVERY_PENDING in sys.databases

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 20, 2011 at 7:51 am

    #1397367

    So it does - thanks guys and gals.

  • October 20, 2011 at 7:59 am

    #1397373

    p.s. The reason those two states are distinct is because they have different causes and (often) different resolutions.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply