SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


My database marked as suspect ... What should I do ? (log inside)


My database marked as suspect ... What should I do ? (log inside)

Author
Message
dronov
dronov
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 11
Hello friends,

I hope you can help me. This morning, MS SQL marked one of my database as suspect. According a log, What should I do to solve a problem ?

Date,Source,Severity,Message
05/31/2010 10:13:37,spid51,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'S8'.
05/31/2010 10:13:37,spid51,Unknown,Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
05/31/2010 10:05:11,Logon,Unknown,Login failed for user 'Admin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
05/31/2010 10:05:11,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
05/31/2010 10:05:11,Logon,Unknown,Login failed for user 'Admin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
05/31/2010 10:05:11,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
05/31/2010 10:00:52,spid1s,Unknown,Server resumed execution after being idle 1791 seconds. Reason: timer event.
05/31/2010 09:15:42,Logon,Unknown,Login failed for user 'Admin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
05/31/2010 09:15:42,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
05/31/2010 09:15:41,Logon,Unknown,Login failed for user 'Admin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
05/31/2010 09:15:41,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
05/31/2010 09:12:30,Logon,Unknown,Login failed for user 'Admin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
05/31/2010 09:12:30,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
05/31/2010 09:12:30,Logon,Unknown,Login failed for user 'Admin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
05/31/2010 09:12:30,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
05/31/2010 09:12:24,Logon,Unknown,Login failed for user 'Admin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
05/31/2010 09:12:24,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
05/31/2010 09:12:24,Logon,Unknown,Login failed for user 'Admin'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
05/31/2010 09:12:24,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
05/31/2010 09:10:12,spid52,Unknown,Using 'xpstar.dll' version '2007.100.1600' to execute extended stored procedure 'xp_enumerrorlogs'. This is an informational message only; no user action is required.
05/31/2010 09:10:12,spid52,Unknown,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
05/31/2010 09:02:11,spid52,Unknown,An error occurred during recovery<c/> preventing the database 'InProSys' (database ID 8) from restarting. Diagnose the recovery errors and fix them<c/> or restore from a known good backup. If errors are not corrected or expected<c/> contact Technical Support.
05/31/2010 09:02:11,spid52,Unknown,Error: 3414<c/> Severity: 21<c/> State: 1.
05/31/2010 09:02:11,spid52,Unknown,The log scan number (326134:306:1) passed to log scan in database 'InProSys' 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<c/> re-create the publication. Otherwise<c/> restore from backup if the problem results in a failure during startup.
05/31/2010 09:02:11,spid52,Unknown,Error: 9003<c/> Severity: 20<c/> State: 1.
05/31/2010 09:02:07,spid52,Unknown,Starting up database 'InProSys'.

05/31/2010 08:55:39,spid5s,Unknown,Recovery is complete. This is an informational message only. No user action is required.
05/31/2010 08:55:39,spid13s,Unknown,Service Broker manager has started.
05/31/2010 08:55:39,spid13s,Unknown,The Database Mirroring protocol transport is disabled or not configured.
05/31/2010 08:55:39,spid13s,Unknown,The Service Broker protocol transport is disabled or not configured.
05/31/2010 08:55:35,spid10s,Unknown,Starting up database 'tempdb'.
05/31/2010 08:55:30,spid10s,Unknown,Clearing tempdb database.
05/31/2010 08:55:30,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.
05/31/2010 08:55:30,Server,Unknown,The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ws-141.stalt.ru:2124 ] for the SQL Server service.
05/31/2010 08:55:30,Server,Unknown,The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ws-141.stalt.ru:S8 ] for the SQL Server service.
05/31/2010 08:55:29,Server,Unknown,Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection<c/> restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
05/31/2010 08:55:29,Server,Unknown,Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$S8\sql\query ].
05/31/2010 08:55:29,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\S8 ].
05/31/2010 08:55:29,Server,Unknown,Server is listening on [ 'any' <ipv4> 2124].
05/31/2010 08:55:29,Server,Unknown,A self-generated certificate was successfully loaded for encryption.
05/31/2010 08:55:28,spid5s,Unknown,Starting up database 'msdb'.
05/31/2010 08:55:28,spid5s,Unknown,Informational: No full-text supported languages found.
05/31/2010 08:55:27,Server,Unknown,Virtual Interface Architecture protocol is not supported for this particular edition of SQL Server.
05/31/2010 08:55:27,spid5s,Unknown,Server name is 'WS-141\S8'. This is an informational message only. No user action is required.
05/31/2010 08:55:27,spid10s,Unknown,Starting up database 'model'.
05/31/2010 08:55:25,spid5s,Unknown,The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
05/31/2010 08:55:25,spid5s,Unknown,Starting up database 'mssqlsystemresource'.
05/31/2010 08:55:24,spid5s,Unknown,SQL Trace ID 1 was started by login "sa".
05/31/2010 08:55:21,spid5s,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'S8'.
05/31/2010 08:55:18,spid5s,Unknown,Recovery completed for database master (database ID 1) in 2 second(s) (analysis 236 ms<c/> redo 218 ms<c/> undo 863 ms.) This is an informational message only. No user action is required.
05/31/2010 08:55:17,spid5s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
05/31/2010 08:55:15,spid5s,Unknown,Starting up database 'master'.
05/31/2010 08:55:15,Server,Unknown,Node configuration: node 0: CPU mask: 0x00000003 Active CPU mask: 0x00000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
05/31/2010 08:55:15,Server,Unknown,Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
05/31/2010 08:55:15,Server,Unknown,Detected 2 CPUs. This is an informational message; no user action is required.
05/31/2010 08:55:15,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
05/31/2010 08:55:14,Server,Unknown,Registry startup parameters: <nl/> -d C:\Program Files\Microsoft SQL Server\MSSQL10.S8\MSSQL\DATA\master.mdf<nl/> -e C:\Program Files\Microsoft SQL Server\MSSQL10.S8\MSSQL\Log\ERRORLOG<nl/> -l C:\Program Files\Microsoft SQL Server\MSSQL10.S8\MSSQL\DATA\mastlog.ldf
05/31/2010 08:55:14,Server,Unknown,This instance of SQL Server last reported using a process ID of 2168 at 28.05.2010 17:28:22 (local) 28.05.2010 13:28:22 (UTC). This is an informational message only; no user action is required.
05/31/2010 08:55:14,Server,Unknown,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.S8\MSSQL\Log\ERRORLOG'.
05/31/2010 08:55:14,Server,Unknown,Authentication mode is MIXED.
05/31/2010 08:55:14,Server,Unknown,Server process ID is 2156.
05/31/2010 08:55:14,Server,Unknown,All rights reserved.
05/31/2010 08:55:14,Server,Unknown,(c) 2005 Microsoft Corporation.
05/31/2010 08:55:14,Server,Unknown,Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) <nl/> Jul 9 2008 14:43:34 <nl/> Copyright (c) 1988-2008 Microsoft Corporation<nl/> Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)

Group: General Forum Members
Points: 469528 Visits: 47358
dronov (5/31/2010)
Otherwise<c/> restore from backup if the problem results in a failure during startup.


I think the error's pretty clear what you need to do. Restore from your last good backup, plus any log backups that you have.

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


dronov
dronov
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 11
I don't have backup. Maybe I can try something else ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)

Group: General Forum Members
Points: 469528 Visits: 47358
Why the hell don't you have a backup?

Try setting the DB to emergency mode. If that gives any errors, post them here. Don't try anything else for the moment.

ALTER DATABASE InProSys SET EMERGENCY



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


dronov
dronov
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 11
I did what you wrote.

Command(s) completed successfully.

Here is a part of log:

Date,Source,Severity,Message
05/31/2010 14:31:06,spid51,Unknown,The database 'InProSys' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.
05/31/2010 14:31:06,spid51,Unknown,Starting up database 'InProSys'.
05/31/2010 14:31:04,spid51,Unknown,Setting database option EMERGENCY to ON for database InProSys.


In Object Explorer, Yellow triangle dissappeared. Instead of it, red cylinder and label [Emergency]. What is next ?

PS:
GilaMonster (5/31/2010)
Why the hell don't you have a backup?


It's my test base, so I have never done any backups (i hope i wrote it right).

GilaMonster
GilaMonster
SSC Guru
SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)

Group: General Forum Members
Points: 469528 Visits: 47358
Now for an emergency mode repair.
DBCC CheckDB('InProSys', REPAIR_ALLOW_DATA_LOSS)


Post any output.

I assume, since it's a test database, if it's not recoverable there's no big problem?

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


dronov
dronov
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 11
GilaMonster (5/31/2010)
Now for an emergency mode repair.
DBCC CheckDB('InProSys', REPAIR_ALLOW_DATA_LOSS)


Post any output.


I got this message:

Repair statement not processed. Database needs to be in single user mode.


I assume, since it's a test database, if it's not recoverable there's no big problem?


hmm, well, there's some useful but not importnant data in it. So if there's a chance to recoiver it, why not to try ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)

Group: General Forum Members
Points: 469528 Visits: 47358
Odd, emergency should imply single user. Did you do anything at all to the DB since setting it to emergency?

Try and set it single user.
Alter Database InProSys SET Single_user

If there was important data in the database, there should have been backups. No excuses.

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


dronov
dronov
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 11
Log:

Date,Source,Severity,Message
05/31/2010 16:23:37,spid51,Unknown,EMERGENCY MODE DBCC CHECKDB (InProSys<c/> repair_allow_data_loss) executed by sa terminated abnormally due to error state 0. Elapsed time: 0 hours 0 minutes 34 seconds.
05/31/2010 16:23:37,spid51,Unknown,Unable to read and latch page (1:1400) with latch type SH. 23(Ошибка в данных (CRC).) failed.
05/31/2010 16:23:37,spid51,Unknown,Error: 8966<c/> Severity: 16<c/> State: 2.
05/31/2010 16:23:37,spid51,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
05/31/2010 16:23:31,spid51,Unknown,Stack Signature for the dump is 0x00000168
05/31/2010 16:23:31,spid51,Unknown,* Short Stack Dump
05/31/2010 16:23:31,spid51,Unknown,* -------------------------------------------------------------------------------
05/31/2010 16:23:31,spid51,Unknown,* *******************************************************************************
05/31/2010 16:23:31,spid51,Unknown,*
05/31/2010 16:23:31,spid51,Unknown,* ProSys'<c/> REPAIR_ALLOW_DATA_LOSS)
05/31/2010 16:23:31,spid51,Unknown,* --Alter Database InProSys SET Single_user DBCC CheckDB('In
05/31/2010 16:23:31,spid51,Unknown,* Input Buffer 212 bytes -
05/31/2010 16:23:31,spid51,Unknown,*
05/31/2010 16:23:31,spid51,Unknown,* DBCC database corruption
05/31/2010 16:23:31,spid51,Unknown,*
05/31/2010 16:23:31,spid51,Unknown,* 05/31/10 16:23:31 spid 51
05/31/2010 16:23:31,spid51,Unknown,* BEGIN STACK DUMP:
05/31/2010 16:23:31,spid51,Unknown,*
05/31/2010 16:23:31,spid51,Unknown,* *******************************************************************************
05/31/2010 16:23:31,spid51,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10.S8\MSSQL\LOG\SQLDump0001.txt
05/31/2010 16:23:31,spid51,Unknown,**Dump thread - spid = 0<c/> EC = 0x05BC39E0
05/31/2010 16:23:31,spid51,Unknown,Using 'dbghelp.dll' version '4.0.5'
05/31/2010 16:23:05,spid51,Unknown,Warning: The log for database 'InProSys' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken<c/> and the server no longer has context on the previous log files<c/> so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use<c/> you will need to reset database options and delete any extra log files.
05/31/2010 16:23:04,spid51,Unknown,Starting up database 'InProSys'.
05/31/2010 16:23:04,spid51,Unknown,Starting up database 'InProSys'.
05/31/2010 16:23:03,spid51,Unknown,The log scan number (326134:306:1) passed to log scan in database 'InProSys' 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<c/> re-create the publication. Otherwise<c/> restore from backup if the problem results in a failure during startup.
05/31/2010 16:23:03,spid51,Unknown,Error: 9003<c/> Severity: 20<c/> State: 1.
05/31/2010 16:23:03,spid51,Unknown,Starting up database 'InProSys'.
05/31/2010 16:22:50,spid51,Unknown,Setting database option SINGLE_USER to ON for database InProSys.




As message output, it gives:

Warning: The log for database 'InProSys' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Msg 7985, Level 16, State 2, Line 3
System table pre-checks: Object ID 3. Could not read and latch page (1:1400) with latch type SH. Check statement terminated due to unrepairable error.
DBCC results for 'InProSys'.
Msg 8966, Level 16, State 2, Line 3
Unable to read and latch page (1:1400) with latch type SH. 23(Ошибка в данных (CRC).) failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'InProSys'.
is the minimum repair level for the errors found by DBCC CHECKDB (InProSys, repair_allow_data_loss).


Now, my database marked as [Single-User].

What's next ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)SSC Guru (469K reputation)

Group: General Forum Members
Points: 469528 Visits: 47358
Those errors don't look promising.
Log's rebuilt, but it looks like there's data file corruption too. Whatever the IO subsystem did here to cause the corruption, it did a thorough job.


DBCC CheckDB('InProSys') WITH NO_INFOMSGS, ALL_ERRORMSGS



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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search