May 31, 2010 at 12:24 am
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)
May 31, 2010 at 1:29 am
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
May 31, 2010 at 1:38 am
I don't have backup. Maybe I can try something else ?
May 31, 2010 at 1:43 am
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
May 31, 2010 at 4:39 am
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).
May 31, 2010 at 4:47 am
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
May 31, 2010 at 5:30 am
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 ?
May 31, 2010 at 6:06 am
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
May 31, 2010 at 6:29 am
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 ?
May 31, 2010 at 6:48 am
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
May 31, 2010 at 7:20 am
Message output:
Msg 7985, Level 16, State 2, Line 5
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.
Msg 8966, Level 16, State 2, Line 5
Unable to read and latch page (1:1400) with latch type SH. 23(?????? ? ?????? (data error)(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).
Does it mean i have no hope ?
May 31, 2010 at 7:25 am
dronov (5/31/2010)
Does it mean i have no hope ?
Correct.
You may be able to extract data/objects from the database as it is now, that's the best you're going to be able to do.
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
May 31, 2010 at 7:26 am
Anyway, thanks for your help, GilaMonster 🙂
June 15, 2010 at 10:06 pm
@Gail,
Odd, emergency should imply single user. Did you do anything at all to the DB since setting it to emergency?
To quote books online
When EMERGENCY is specified, database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.
Probably 2 sysadmin connections where present.
Regards,
Raj
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply