recover suspect db in sql 2005

Ask a question

Monday, July 23, 2012 5:32 AM

0
Hi friends,

My Sql 2005 DB goes into suspect mode, while no transaction is being made in those DB's for 2-3 yrs.Clients only access those DB's for there reports.

what may be the issues. This happens more often now.

please suggest ways to avoid this situation, and to recover suspect DB.

Ravi Mishra
- Edited by ravi mishra Monday, July 23, 2012 5:36 AM
- Reply
- Quote

All Replies

Monday, July 23, 2012 8:32 AM

1

To know the issue for suspect mode: See the errorlog message, eventlog message, & check hardware etc.

Recommended actions for corrupt or suspect databases by Tibor Karaszi

To recover from suspect mode:

“EXEC sp_resetstatus ‘yourDatabasename’;
ALTER DATABASE yourDatabasename SET EMERGENCY
DBCC CHECKDB(’yourDatabasename’)
ALTER DATABASE yourDatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDatabasename’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDatabasename SET MULTI_USER”

Alternate: If above command does not work then try any Microsoft gold certified partner's product for SQL recovery.
Read my blog (Blog)
Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
- Reply
- Quote
Tuesday, July 24, 2012 12:01 PM

Moderator

0

I always recommend restoring from a healthy backup instead of attempting som repair routine...
Tibor Karaszi, SQL Server MVP | web | blog
- Reply
- Quote
Wednesday, July 25, 2012 6:14 AM

0
Thanks everyone, i have recovered the DB by your suggestion

But issue is that why a DB becomes suspect when there is no transaction being made for last 4 yrs.While analyzing sql error log there is message about log scan, i can share the error log message.

please suggest the way to avoid the DB becoming suspect.

Thanks very much.

Ravi
- Edited by ravi mishra Wednesday, July 25, 2012 6:14 AM
- Reply
- Quote
Wednesday, July 25, 2012 6:27 AM

0

Hi!

Please share the message from errorlog. As long as we don't know why the DB gets suspect in the first place it's hard to give suggestions on how to avoid it.
- Reply
- Quote
Wednesday, July 25, 2012 8:18 AM

0
Hello,

              following are the detailed error log of that day when the DB's become suspect. Please go through the colored portion and refer to the Note at the end of error log.

Error log message

07/23/2012 13:49:45,spid108,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database msale11.

07/23/2012 13:48:17,spid101,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database MFas11.

07/23/2012 13:13:03,spid64,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database msale11.

07/23/2012 13:11:49,spid64,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database msale11.

07/23/2012 13:01:32,spid88,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database msale11.

07/23/2012 12:57:59,spid76,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database MFas11.

07/23/2012 12:54:00,spid70,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database msale11.

07/23/2012 12:50:43,spid61,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database MFas11.

07/23/2012 12:41:17,spid54,Unknown,Using 'xplog70.dll' version '2005.90.1399' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.

07/23/2012 12:41:17,spid54,Unknown,Using 'xpsqlbot.dll' version '2005.90.1399' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.

07/23/2012 12:41:16,spid54,Unknown,Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

07/23/2012 12:39:46,Backup,Unknown,Database was restored: Database: msale05<c/> creation date(time): 2009/06/08(15:29:03)<c/> first LSN: 55219:13981:38<c/> last LSN: 55219:13999:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'H:\msale05_19122011'}). Informational message. No user action required.

07/23/2012 12:39:44,spid54,Unknown,Starting up database 'msale05'.

07/23/2012 12:39:40,spid54,Unknown,The database 'msale05' is marked RESTORING and is in a state that does not allow recovery to be run.

07/23/2012 12:39:40,spid54,Unknown,Starting up database 'msale05'.

07/23/2012 12:26:59,spid57,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database msale11.

07/23/2012 12:24:16,spid51,Unknown,Setting database option MULTI_USER to ON for database MSALE06.

07/23/2012 12:22:46,spid56,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database msale11.

07/23/2012 12:22:07,spid56,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database msale11.

07/23/2012 12:20:35,spid51,Unknown,EMERGENCY MODE DBCC CHECKDB (msale06<c/> repair_allow_data_loss) executed by raj0 found 0 errors and repaired 0 errors. Elapsed time: 0 hours 5 minutes 31 seconds.

07/23/2012 12:15:16,spid21s,Unknown,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.

07/23/2012 12:15:04,spid51,Unknown,Warning: The log for database 'msale06' 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.

07/23/2012 12:15:04,spid51,Unknown,Warning: The log for database 'msale06' 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.

07/23/2012 12:15:04,spid51,Unknown,Starting up database 'msale06'.

07/23/2012 12:15:04,spid51,Unknown,Starting up database 'msale06'.

07/23/2012 12:15:04,spid51,Unknown,The log scan number (228403:16:127) passed to log scan in database 'msale06' 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.

07/23/2012 12:15:04,spid51,Unknown,Error: 9003<c/> Severity: 20<c/> State: 9.

07/23/2012 12:15:03,spid51,Unknown,Starting up database 'msale06'.

07/23/2012 12:14:52,spid51,Unknown,Setting database option SINGLE_USER to ON for database MSALE06.

07/23/2012 12:14:21,spid53,Unknown,New log file 'H:\msale11_log.LDF' was created.

07/23/2012 12:14:21,spid53,Unknown,New log file 'H:\msale11_log.LDF' was created.

07/23/2012 12:14:21,spid53,Unknown,FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'h:\MSALE11_log.LDF'. Diagnose and correct the operating system error<c/> and retry the operation.

07/23/2012 12:14:21,spid53,Unknown,Error: 17207<c/> Severity: 16<c/> State: 1.

07/23/2012 12:14:21,spid53,Unknown,Starting up database 'msale11'.

07/23/2012 12:14:17,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.217]

07/23/2012 12:14:17,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 12:13:55,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.144]

07/23/2012 12:13:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 12:09:06,Backup,Unknown,Database was restored: Database: msale11<c/> creation date(time): 2012/01/15(21:03:22)<c/> first LSN: 516252:24114:14<c/> last LSN: 516252:24165:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'H:\backup2011\msale11_06062012'}). Informational message. No user action required.

07/23/2012 12:09:05,spid53,Unknown,Recovery is writing a checkpoint in database 'msale11' (13). This is an informational message only. No user action is required.

07/23/2012 12:09:02,spid53,Unknown,Starting up database 'msale11'.

07/23/2012 12:08:51,spid53,Unknown,The database 'msale11' is marked RESTORING and is in a state that does not allow recovery to be run.

07/23/2012 12:08:51,spid53,Unknown,Starting up database 'msale11'.

07/23/2012 12:06:00,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.151]

07/23/2012 12:06:00,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 12:05:57,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.151]

07/23/2012 12:05:57,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 12:05:54,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.151]

07/23/2012 12:05:54,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 12:05:10,spid55,Unknown,Error while closing database 'msale05'. Check for previous additional errors and retry the operation.

07/23/2012 12:05:10,spid55,Unknown,Error: 947<c/> Severity: 16<c/> State: 1.

07/23/2012 12:00:56,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.217]

07/23/2012 12:00:56,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:48:30,spid56,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database MFas11.

07/23/2012 11:48:15,spid56,Unknown,Setting database option RECOVERY to RECMODEL_70BACKCOMP for database MFas11.

07/23/2012 11:46:16,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.144]

07/23/2012 11:46:16,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:46:13,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.144]

07/23/2012 11:46:13,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:45:59,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.217]

07/23/2012 11:45:59,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:38:34,spid53,Unknown,Setting database option SINGLE_USER to ON for database msale11.

07/23/2012 11:37:46,spid51,Unknown,The database 'msale11' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.

07/23/2012 11:37:46,spid51,Unknown,Starting up database 'msale11'.

07/23/2012 11:37:46,spid51,Unknown,Setting database option EMERGENCY to ON for database msale11.

07/23/2012 11:28:43,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.144]

07/23/2012 11:28:43,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:28:42,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.144]

07/23/2012 11:28:42,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:28:30,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.144]

07/23/2012 11:28:30,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:24:33,Logon,Unknown,Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 210.210.210.144]

07/23/2012 11:24:33,Logon,Unknown,Error: 18452<c/> Severity: 14<c/> State: 1.

07/23/2012 11:24:33,Logon,Unknown,SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 210.210.210.144]

07/23/2012 11:24:33,Logon,Unknown,Error: 17806<c/> Severity: 20<c/> State: 2.

07/23/2012 11:22:19,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.217]

07/23/2012 11:22:19,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:22:17,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.217]

07/23/2012 11:22:17,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:22:11,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.217]

07/23/2012 11:22:11,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:20:25,spid51,Unknown,During upgrade<c/> database raised exception 945<c/> severity 14<c/> state 2<c/> address 0156C580. Use the exception number to determine the cause.

07/23/2012 11:20:25,spid51,Unknown,Error: 928<c/> Severity: 20<c/> State: 1.

07/23/2012 11:20:25,spid51,Unknown,SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x55555556). It occurred during a read of page (1:9) in database ID 33 at offset 0x00000000012000 in file 'h:\MSALE05.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.

07/23/2012 11:20:25,spid51,Unknown,Error: 824<c/> Severity: 24<c/> State: 2.

07/23/2012 11:20:23,spid51,Unknown,Starting up database 'msale05'.

07/23/2012 11:20:23,spid51,Unknown,Setting database option MULTI_USER to ON for database msale05.

07/23/2012 11:20:23,spid51,Unknown,During upgrade<c/> database raised exception 945<c/> severity 14<c/> state 2<c/> address 0156C580. Use the exception number to determine the cause.

07/23/2012 11:20:23,spid51,Unknown,Error: 928<c/> Severity: 20<c/> State: 1.

07/23/2012 11:20:23,spid51,Unknown,SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x55555556). It occurred during a read of page (1:9) in database ID 33 at offset 0x00000000012000 in file 'h:\MSALE05.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.

07/23/2012 11:20:23,spid51,Unknown,Error: 824<c/> Severity: 24<c/> State: 2.

07/23/2012 11:20:22,spid51,Unknown,Starting up database 'msale05'.

07/23/2012 11:20:22,spid51,Unknown,Setting database option SINGLE_USER to ON for database msale05.

07/23/2012 11:20:01,spid5s,Unknown,Recovery is complete. This is an informational message only. No user action is required.

07/23/2012 11:19:58,spid18s,Unknown,SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x55555556). It occurred during a read of page (1:9) in database ID 33 at offset 0x00000000012000 in file 'h:\MSALE05.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.

07/23/2012 11:19:58,spid18s,Unknown,Error: 824<c/> Severity: 24<c/> State: 2.

07/23/2012 11:19:58,spid51,Unknown,Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

07/23/2012 11:19:58,spid15s,Unknown,An error occurred during recovery<c/> preventing the database 'msale11' (database ID 13) 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.

07/23/2012 11:19:58,spid15s,Unknown,Error: 3414<c/> Severity: 21<c/> State: 1.

07/23/2012 11:19:57,spid15s,Unknown,During redoing of a logged operation in database 'msale11'<c/> an error occurred at log record ID (538657:4006:61). Typically<c/> the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup<c/> or repair the database.

07/23/2012 11:19:57,spid15s,Unknown,Error: 3313<c/> Severity: 21<c/> State: 2.

07/23/2012 11:19:57,spid15s,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.

07/23/2012 11:19:57,spid18s,Unknown,Starting up database 'msale05'.

07/23/2012 11:19:56,spid18s,Unknown,The database 'msale06' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.

07/23/2012 11:19:56,spid18s,Unknown,Starting up database 'msale06'.

07/23/2012 11:19:56,spid16s,Unknown,Starting up database 'msale07'.

07/23/2012 11:19:56,spid20s,Unknown,Starting up database 'msale08'.

07/23/2012 11:19:56,spid16s,Unknown,Starting up database 'mfas05'.

07/23/2012 11:19:55,spid21s,Unknown,Starting up database 'mfas06'.

07/23/2012 11:19:53,spid15s,Unknown,Stack Signature for the dump is 0xA0F99A90

07/23/2012 11:19:53,spid15s,Unknown,78132A36 Module(MSVCR80+00002A36)

07/23/2012 11:19:53,spid15s,Unknown,781329AA Module(MSVCR80+000029AA)

07/23/2012 11:19:53,spid15s,Unknown,010D83F0 Module(sqlservr+000D83F0)

07/23/2012 11:19:53,spid15s,Unknown,010D746E Module(sqlservr+000D746E)

07/23/2012 11:19:53,spid15s,Unknown,010D7B71 Module(sqlservr+000D7B71)

07/23/2012 11:19:53,spid15s,Unknown,010D764A Module(sqlservr+000D764A)

07/23/2012 11:19:53,spid15s,Unknown,010086E7 Module(sqlservr+000086E7)

07/23/2012 11:19:53,spid15s,Unknown,010089C5 Module(sqlservr+000089C5)

07/23/2012 11:19:53,spid15s,Unknown,0100889F Module(sqlservr+0000889F)

07/23/2012 11:19:53,spid15s,Unknown,020C5450 Module(sqlservr+010C5450)

07/23/2012 11:19:53,spid15s,Unknown,020C586C Module(sqlservr+010C586C)

07/23/2012 11:19:53,spid15s,Unknown,020BB8EF Module(sqlservr+010BB8EF)

07/23/2012 11:19:53,spid15s,Unknown,020CF21E Module(sqlservr+010CF21E)

07/23/2012 11:19:53,spid15s,Unknown,021A3AB9 Module(sqlservr+011A3AB9)

07/23/2012 11:19:53,spid15s,Unknown,02193C83 Module(sqlservr+01193C83)

07/23/2012 11:19:53,spid15s,Unknown,0219916B Module(sqlservr+0119916B)

07/23/2012 11:19:53,spid15s,Unknown,02130516 Module(sqlservr+01130516)

07/23/2012 11:19:53,spid15s,Unknown,01095FFE Module(sqlservr+00095FFE)

07/23/2012 11:19:53,spid15s,Unknown,010AC5CB Module(sqlservr+000AC5CB)

07/23/2012 11:19:53,spid15s,Unknown,02192F37 Module(sqlservr+01192F37)

07/23/2012 11:19:53,spid15s,Unknown,02192DDA Module(sqlservr+01192DDA)

07/23/2012 11:19:53,spid15s,Unknown,02192ED2 Module(sqlservr+01192ED2)

07/23/2012 11:19:53,spid15s,Unknown,02176BA0 Module(sqlservr+01176BA0)

07/23/2012 11:19:53,spid15s,Unknown,02172CE4 Module(sqlservr+01172CE4)

07/23/2012 11:19:53,spid15s,Unknown,77E4BEE7 Module(kernel32+0000BEE7)

07/23/2012 11:19:53,spid15s,Unknown,* Short Stack Dump

07/23/2012 11:19:53,spid15s,Unknown,* -------------------------------------------------------------------------------

07/23/2012 11:19:53,spid15s,Unknown,* *******************************************************************************

07/23/2012 11:19:53,spid15s,Unknown,*      SegSs: 78130023: 00000000 00000000 00000000 00000000 00000000 00000000

07/23/2012 11:19:53,spid15s,Unknown,*        Esp: 6262C4B8: 041887B0 000042AC 00000000 00000000 77E4BEE7 00000000

07/23/2012 11:19:53,spid15s,Unknown,*     EFlags: 00000246:

07/23/2012 11:19:53,spid15s,Unknown,*      SegCs: 0030001B:

07/23/2012 11:19:53,spid15s,Unknown,*        Ebp: 6262C50C: 6262C550 02172CE4 000042AC 00000000 00000000 00000000

07/23/2012 11:19:53,spid15s,Unknown,*        Eip: 77E4BEE7: 10C2C95E 01093D00 840FC000 00000095 F29DE950 9090FFFF

07/23/2012 11:19:53,spid15s,Unknown,*        Edx: 0000003D:

07/23/2012 11:19:53,spid15s,Unknown,*        Ecx: 6262CB2C: 00000000 00010007 00000000 0090008E 0013B00C 6262C4EC

07/23/2012 11:19:53,spid15s,Unknown,*        Ebx: 0000003F:

07/23/2012 11:19:53,spid15s,Unknown,*        Eax: 6262C4BC: 000042AC 00000000 00000000 77E4BEE7 00000000 7C82771B

07/23/2012 11:19:53,spid15s,Unknown,*        Esi: 00000000:

07/23/2012 11:19:53,spid15s,Unknown,*        Edi: 6262C5D8: 0013B008 00000000 6262C608 0102F47E 041700E8 00000000

07/23/2012 11:19:53,spid15s,Unknown,*

07/23/2012 11:19:53,spid15s,Unknown,* dbghelp                        63D30000 63E47FFF 00118000

07/23/2012 11:19:53,spid15s,Unknown,* msftepxy                       635F0000 63604FFF 00015000

07/23/2012 11:19:53,spid15s,Unknown,* SQLNCLIR                       007C0000 007F2FFF 00033000

07/23/2012 11:19:53,spid15s,Unknown,* comdlg32                       762B0000 762F8FFF 00049000

07/23/2012 11:19:53,spid15s,Unknown,* COMCTL32                       77530000 775C6FFF 00097000

07/23/2012 11:19:53,spid15s,Unknown,* sqlncli                        63150000 6336DFFF 0021e000

07/23/2012 11:19:53,spid15s,Unknown,* CLBCatQ                        777B0000 77832FFF 00083000

07/23/2012 11:19:53,spid15s,Unknown,* xpsp2res                       62E80000 63144FFF 002c5000

07/23/2012 11:19:53,spid15s,Unknown,* ntdsapi                        766F0000 76703FFF 00014000

07/23/2012 11:19:53,spid15s,Unknown,* SAMLIB                         7E020000 7E02EFFF 0000f000

07/23/2012 11:19:53,spid15s,Unknown,* NTMARTA                        77E00000 77E20FFF 00021000

07/23/2012 11:19:53,spid15s,Unknown,* wshtcpip                       71AE0000 71AE7FFF 00008000

07/23/2012 11:19:53,spid15s,Unknown,* hnetcfg                        62DA0000 62DF9FFF 0005a000

07/23/2012 11:19:53,spid15s,Unknown,* dssenh                         68100000 68126FFF 00027000

07/23/2012 11:19:53,spid15s,Unknown,* imagehlp                       76C10000 76C37FFF 00028000

07/23/2012 11:19:53,spid15s,Unknown,* WINTRUST                       76BB0000 76BDAFFF 0002b000

07/23/2012 11:19:53,spid15s,Unknown,* dbghelp                        629D0000 62AE7FFF 00118000

07/23/2012 11:19:53,spid15s,Unknown,* msfte                          62760000 629B7FFF 00258000

07/23/2012 11:19:53,spid15s,Unknown,* security                       62140000 62143FFF 00004000

07/23/2012 11:19:53,spid15s,Unknown,* rasadhlp                       76F80000 76F84FFF 00005000

07/23/2012 11:19:53,spid15s,Unknown,* WLDAP32                        76F10000 76F3DFFF 0002e000

07/23/2012 11:19:53,spid15s,Unknown,* winrnr                         76F70000 76F76FFF 00007000

07/23/2012 11:19:53,spid15s,Unknown,* DNSAPI                         76ED0000 76EF9FFF 0002a000

07/23/2012 11:19:53,spid15s,Unknown,* RESUTILS                       61CF0000 61D02FFF 00013000

07/23/2012 11:19:53,spid15s,Unknown,* CLUSAPI                       61CD0000 61CE1FFF 00012000

07/23/2012 11:19:53,spid15s,Unknown,* WSOCK32                        71BB0000 71BB8FFF 00009000

07/23/2012 11:19:53,spid15s,Unknown,* VERSION                        77B90000 77B97FFF 00008000

07/23/2012 11:19:53,spid15s,Unknown,* MTXCLU                         61CB0000 61CC8FFF 00019000

07/23/2012 11:19:53,spid15s,Unknown,* msvcp60                        61C40000 61CA4FFF 00065000

07/23/2012 11:19:53,spid15s,Unknown,* OLEAUT32                       77D00000 77D8AFFF 0008b000

07/23/2012 11:19:53,spid15s,Unknown,* MSDTCPRX                       61BC0000 61C38FFF 00079000

07/23/2012 11:19:53,spid15s,Unknown,* XOLEHLP                        61BB0000 61BB5FFF 00006000

07/23/2012 11:19:53,spid15s,Unknown,* COMRES                         77010000 770D5FFF 000c6000

07/23/2012 11:19:53,spid15s,Unknown,* schannel                       76750000 76776FFF 00027000

07/23/2012 11:19:53,spid15s,Unknown,* cryptdll                       766E0000 766EBFFF 0000c000

07/23/2012 11:19:53,spid15s,Unknown,* kerberos                       61B20000 61B77FFF 00058000

07/23/2012 11:19:53,spid15s,Unknown,* iphlpapi                       76CF0000 76D09FFF 0001a000

07/23/2012 11:19:53,spid15s,Unknown,* msv1_0                         76C90000 76CB6FFF 00027000

07/23/2012 11:19:53,spid15s,Unknown,* ole32                          77670000 777A8FFF 00139000

07/23/2012 11:19:53,spid15s,Unknown,* MSCOREE                        340B0000 340F4FFF 00045000

07/23/2012 11:19:53,spid15s,Unknown,* AUTHZ                          76C40000 76C53FFF 00014000

07/23/2012 11:19:53,spid15s,Unknown,* rsaenh                         68000000 68034FFF 00035000

07/23/2012 11:19:53,spid15s,Unknown,* SQLOS                          344D0000 344D4FFF 00005000

07/23/2012 11:19:53,spid15s,Unknown,* sqlevn70                       4F610000 4F7A0FFF 00191000

07/23/2012 11:19:53,spid15s,Unknown,* instapi                        48060000 48069FFF 0000a000

07/23/2012 11:19:53,spid15s,Unknown,* psapi                          76B70000 76B7AFFF 0000b000

07/23/2012 11:19:53,spid15s,Unknown,* comctl32                       77420000 77522FFF 00103000

07/23/2012 11:19:53,spid15s,Unknown,* SHLWAPI                        77DA0000 77DF1FFF 00052000

07/23/2012 11:19:53,spid15s,Unknown,* SHELL32                        7C8D0000 7D0CDFFF 007fe000

07/23/2012 11:19:53,spid15s,Unknown,* NETAPI32                       71C40000 71C96FFF 00057000

07/23/2012 11:19:53,spid15s,Unknown,* opends60                       333E0000 333E6FFF 00007000

07/23/2012 11:19:53,spid15s,Unknown,* USERENV                        76920000 769E1FFF 000c2000

07/23/2012 11:19:53,spid15s,Unknown,* WS2HELP                        71BF0000 71BF7FFF 00008000

07/23/2012 11:19:53,spid15s,Unknown,* WS2_32                         71C00000 71C16FFF 00017000

07/23/2012 11:19:53,spid15s,Unknown,* MSWSOCK                        71B20000 71B60FFF 00041000

07/23/2012 11:19:53,spid15s,Unknown,* MSASN1                         76190000 761A1FFF 00012000

07/23/2012 11:19:53,spid15s,Unknown,* CRYPT32                        761B0000 76242FFF 00093000

07/23/2012 11:19:53,spid15s,Unknown,* GDI32                          77C00000 77C47FFF 00048000

07/23/2012 11:19:53,spid15s,Unknown,* USER32                         77380000 77410FFF 00091000

07/23/2012 11:19:53,spid15s,Unknown,* Secur32                        76F50000 76F62FFF 00013000

07/23/2012 11:19:53,spid15s,Unknown,* RPCRT4                         77C50000 77CEEFFF 0009f000

07/23/2012 11:19:53,spid15s,Unknown,* ADVAPI32                       77F50000 77FEAFFF 0009b000

07/23/2012 11:19:53,spid15s,Unknown,* MSVCP80                        7C420000 7C4A6FFF 00087000

07/23/2012 11:19:53,spid15s,Unknown,* msvcrt                         77BA0000 77BF9FFF 0005a000

07/23/2012 11:19:53,spid15s,Unknown,* MSVCR80                        78130000 781CAFFF 0009b000

07/23/2012 11:19:53,spid15s,Unknown,* kernel32                       77E40000 77F41FFF 00102000

07/23/2012 11:19:53,spid15s,Unknown,* ntdll                          7C800000 7C8BFFFF 000c0000

07/23/2012 11:19:53,spid15s,Unknown,* sqlservr                       01000000 02BA7FFF 01ba8000

07/23/2012 11:19:53,spid15s,Unknown,* MODULE                          BASE      END       SIZE

07/23/2012 11:19:53,spid15s,Unknown,*

07/23/2012 11:19:53,spid15s,Unknown,*

07/23/2012 11:19:53,spid15s,Unknown,*

07/23/2012 11:19:53,spid15s,Unknown,* HandleAndNoteToErrorlog: Exception raised<c/> major=34<c/> minor=56<c/> severity=21

07/23/2012 11:19:53,spid15s,Unknown,*

07/23/2012 11:19:53,spid15s,Unknown,*   07/23/12 11:19:53 spid 15

07/23/2012 11:19:53,spid15s,Unknown,* BEGIN STACK DUMP:

07/23/2012 11:19:53,spid15s,Unknown,*

07/23/2012 11:19:53,spid15s,Unknown,* *******************************************************************************

07/23/2012 11:19:53,spid15s,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0009.txt

07/23/2012 11:19:53,spid15s,Unknown,**Dump thread - spid = 15<c/> PSS = 0x041887B0<c/> EC = 0x041887B8

07/23/2012 11:19:53,spid15s,Unknown,Using 'dbghelp.dll' version '4.0.5'

07/23/2012 11:19:53,spid21s,Unknown,Starting up database 'mfas07'.

07/23/2012 11:19:52,spid15s,Unknown,Could not redo log record (538657:4006:61)<c/> for transaction ID (0:0)<c/> on page (1:4296176)<c/> database 'msale11' (database ID 13). Page: LSN = (538656:14432:68)<c/> type = 1. Log: OpCode = 18<c/> context 2<c/> PrevPageLSN: (538657:3974:224). Restore from a backup of the database<c/> or repair the database.

07/23/2012 11:19:52,spid15s,Unknown,Error: 3456<c/> Severity: 21<c/> State: 1.

07/23/2012 11:19:52,spid15s,Unknown,Analysis of database 'msale11' (13) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.

07/23/2012 11:19:51,spid18s,Unknown,Starting up database 'mfas08'.

07/23/2012 11:19:51,spid17s,Unknown,Starting up database 'mfas09'.

07/23/2012 11:19:49,spid16s,Unknown,Starting up database 'msale09'.

07/23/2012 11:19:48,spid20s,Unknown,Starting up database 'vsale11'.

07/23/2012 11:19:44,spid22s,Unknown,Starting up database 'jsale11'.

07/23/2012 11:19:43,spid16s,Unknown,Starting up database 'GFAS11'.

07/23/2012 11:19:42,spid20s,Unknown,Starting up database 'bfas11'.

07/23/2012 11:19:33,spid17s,Unknown,Starting up database 'BSALE11'.

07/23/2012 11:19:32,spid21s,Unknown,Starting up database 'GSALE11'.

07/23/2012 11:19:31,spid22s,Unknown,Starting up database 'mfas10'.

07/23/2012 11:19:30,spid17s,Unknown,Starting up database 'clist07'.

07/23/2012 11:19:29,spid17s,Unknown,Starting up database 'msale10'.

07/23/2012 11:19:29,spid19s,Unknown,Starting up database 'employee'.

07/23/2012 11:19:29,spid15s,Unknown,Starting up database 'msale11'.

07/23/2012 11:19:29,spid16s,Unknown,Starting up database 'mfas11'.

07/23/2012 11:19:27,spid21s,Unknown,Starting up database 'mrgdb'.

07/23/2012 11:19:27,spid22s,Unknown,Starting up database 'kfas11'.

07/23/2012 11:19:27,spid15s,Unknown,Starting up database 'AdventureWorksDW'.

07/23/2012 11:19:27,spid19s,Unknown,Starting up database 'hnssrv'.

07/23/2012 11:19:27,spid20s,Unknown,Starting up database 'jcck11'.

07/23/2012 11:19:27,spid18s,Unknown,Starting up database 'ksale11'.

07/23/2012 11:19:27,spid17s,Unknown,Starting up database 'AdventureWorks'.

07/23/2012 11:19:27,spid16s,Unknown,Starting up database 'msdb'.

07/23/2012 11:19:27,Logon,Unknown,Login failed for user 'raj0'. [CLIENT: 210.210.210.144]

07/23/2012 11:19:27,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.

07/23/2012 11:19:27,spid12s,Unknown,Service Broker manager has started.

07/23/2012 11:19:26,spid12s,Unknown,The Database Mirroring protocol transport is disabled or not configured.

07/23/2012 11:19:26,Server,Unknown,SQL Server is now ready for client connections. This is an informational message; no user action is required.

07/23/2012 11:19:26,Server,Unknown,The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

07/23/2012 11:19:26,spid12s,Unknown,The Service Broker protocol transport is disabled or not configured.

07/23/2012 11:19:26,Server,Unknown,Dedicated admin connection support was established for listening locally on port 1434.

07/23/2012 11:19:26,Server,Unknown,Server is listening on [ 127.0.0.1 <ipv4> 1434].

07/23/2012 11:19:26,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].

07/23/2012 11:19:26,Server,Unknown,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].

07/23/2012 11:19:26,Server,Unknown,Server is listening on [ 'any' <ipv4> 1433].

07/23/2012 11:19:26,Server,Unknown,A self-generated certificate was successfully loaded for encryption.

07/23/2012 11:19:26,spid9s,Unknown,Starting up database 'tempdb'.

07/23/2012 11:19:26,spid9s,Unknown,Clearing tempdb database.

07/23/2012 11:19:25,spid9s,Unknown,Starting up database 'model'.

07/23/2012 11:19:25,spid5s,Unknown,Server name is 'KCSERVER1'. This is an informational message only. No user action is required.

07/23/2012 11:19:25,spid5s,Unknown,Starting up database 'mssqlsystemresource'.

07/23/2012 11:19:25,spid5s,Unknown,SQL Trace ID 1 was started by login "sa".

07/23/2012 11:19:25,spid5s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

07/23/2012 11:19:25,spid5s,Unknown,Starting up database 'master'.

07/23/2012 11:19:25,Server,Unknown,Database Mirroring Transport is disabled in the endpoint configuration.

07/23/2012 11:19:25,Server,Unknown,Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

07/23/2012 11:19:22,Server,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

07/23/2012 11:19:22,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.

07/23/2012 11:19:22,Server,Unknown,Detected 2 CPUs. This is an informational message; no user action is required.

07/23/2012 11:19:22,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

07/23/2012 11:19:22,Server,Unknown,-l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

07/23/2012 11:19:22,Server,Unknown,-e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

07/23/2012 11:19:22,Server,Unknown,-d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

07/23/2012 11:19:22,Server,Unknown,Registry startup parameters:

07/23/2012 11:19:22,Server,Unknown,This instance of SQL Server last reported using a process ID of 872 at 7/23/2012 11:17:46 AM (local) 7/23/2012 5:47:46 AM (UTC). This is an informational message only; no user action is required.

07/23/2012 11:19:22,Server,Unknown,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

07/23/2012 11:19:22,Server,Unknown,Server process ID is 3056.

07/23/2012 11:19:22,Server,Unknown,All rights reserved.

07/23/2012 11:19:22,Server,Unknown,(c) 2005 Microsoft Corporation.

07/23/2012 11:19:22,Server,Unknown,Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) <nl/>               Oct 14 2005 00:33:37 <nl/>     Copyright (c) 1988-2005 Microsoft Corporation<nl/>        Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Note:

              Need to check following sections and your valuable suggestions, comments on the same.
1. Error log message against Spid15s. what does it signify.
2. Error log message against Spid51s
3. Log message for DB’s msale06,msale05 and msale11 need to be checked mainly.
4. DB’s msale06 and msale11 was suspect and later on it was recovered or restored from healthy backup.
5. DB msale05 was not responding (it was not suspect). I have restored it from fresh backup.
Ravi
- Edited by ravi mishra Wednesday, July 25, 2012 8:19 AM
- Reply
- Quote
Friday, September 21, 2012 7:37 PM

0

Make use of repair for sql server crashed by malware, errors in transferring via Internet, incorrect user actions, hard drive failures

Tool restores system/user tables, stored procedures, functions, etc. data.
- Reply
- Quote
Friday, September 21, 2012 7:45 PM

0

Run a hardware check of your disks... They might be close to some major failure
Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu
- Reply
- Quote