none
recover suspect db in sql 2005

    General discussion

  • 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


    Monday, July 23, 2012 5:32 AM

All replies

  • 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

    Monday, July 23, 2012 8:32 AM
  • I always recommend restoring from a healthy backup instead of attempting som repair routine...

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, July 24, 2012 12:01 PM
  • 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
    Wednesday, July 25, 2012 6:14 AM
  • 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.

    Wednesday, July 25, 2012 6:27 AM
  • 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
    Wednesday, July 25, 2012 8:18 AM
  • 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.

    Friday, September 21, 2012 7:37 PM
  • Run a hardware check of your disks... They might be close to some major failure

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, September 21, 2012 7:45 PM