SQL Server Performance

Database gone into Suspect mode after the execution of an SP

Discussion in 'SQL Server 2005 General DBA Questions' started by sqlserverscrap, Apr 10, 2013.

  1. sqlserverscrap New Member

    SQL Server Version: 2005 Standard RTM version 9.00.1406.00

    A Stored Procedure(This SP Runs everyday but has never caused suspect mode) was running which does a large amount of data transfer within a transaction. The transaction might remain active for about 15 to 20 minutes. There might also be other processes accessing the table which is under transaction. So blocking might have been occurring. The Database went into suspect Mode with huge number of Dump files.

    Some error data from Dump files:

    First File Dump File:

    * 04/04/13 01:18:16 spid 95
    * Location: lckmgr.cpp:9421
    * Expression:NULL == m_lockList.Head ()

    Second Dump File:

    * 04/04/13 01:18:20 spid 95
    * Exception Address = 0000000000000000 Module(sqlservr+FFFFFFFFFF000000)
    * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
    * Access Violation occurred writing address 0000000000000000

    Third Dump File:

    * 04/04/13 01:18:23 spid 95
    * Location:"xact.cpp":2630
    * Expression:!m_updNestedXactCnt
    * SPID:95
    * Process ID:268
    * Description:Trying to use the transaction while there are 1 parallel nested xacts outstanding

    Fourth Dump File:

    * 04/04/13 01:18:43 spid 95
    *
    * ex_raise2: Exception raised, major=52, minor=42, state=1, severity=22

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Begin transaction log dump for database 'DB_Name'; for rollback failure - filter XdesId.

    --Huge Number of Transaction Log Entries Here--

    Fifth Dump File:

    * 04/04/13 01:21:40 spid 18
    * Locationpage.cpp:4245
    * Expression:spaceNeeded <= spaceContig && spaceNeeded <= space_usable

    Sixth Dump File:

    * 04/04/13 01:21:40 spid 18
    *
    * HandleAndNoteToErrorlog: Exception raised, major=34, minor=48, severity=21

    Next Dump File:

    * 04/04/13 01:21:57 spid 21
    * Locationpage.cpp:4100
    * Expression:!pageFull
    * SPID:21

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Begin transaction log dump for database 'DB_Name'; for REDO failure - filter on PageId.

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Begin transaction log dump for database 'DB_Name'; for REDO failure - filter XdesId.

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Begin transaction log dump for database 'DB_Name'; for recovery failure - no filter.

    Seventh Dump File:

    * 04/04/13 01:24:39 spid 98
    * Exception Address = 000000000288FC14 Module(sqlservr+000000000188FC14)
    * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
    * Access Violation occurred reading address 0000000000000000

    Eighth Dump File:

    * 04/04/13 01:24:41 spid 98
    * Location:T:\Yukon\sql\ntdbms\storeng\dfs\manager\lckmgrp.h:675
    * Expression:m_iteratorInSafeState

    Nineth Dump File:

    * 04/04/13 01:24:41 spid 98
    * ex_terminator - Last chance exception handling
  2. Luis Martin Moderator

    First suggestion is to install sp4. Your vesion had some problems.
  3. davidfarr Member

    SP4 is a good update, but that update alone will not restore your database from Suspect to operational status.
    The active transaction on it's own, with or without blocking, is very unliklely to have placed your database in a Suspect state.
    In my opinion there is almost certainly an actual corruption of data resulting from a hardware issue, probably I/O problems from bad sectors on the disk or interrupted memory caching.

    If it was my system; I would attempt a full restore of the database from the last successful backup set that you had prior to the problem.
    My own systems run with full recovery mode and transaction log backups every 30 minutes, and so a full restore with minimal data loss is always an option for me.

    If you have no recent, reliable backups then you can try the following;

    It often happens with databases in a Suspect state that you cannot access the database at all. If this happens then you might need to place the database in 'emergency' mode to access.
    To do that; log on to the master database as a sysadmin role and run this query;
    UPDATE sysdatabases set [status] = 32768 where [name] = <suspect database name>
    This should allow a sysadmin user to have partial access to run commands on the database, depending on the severity of the data files integrity.

    You should then run DBCC CHECKDB ('<database name>') to determine the severity of the integrity problem.
    Depending on the output of that query, you can then consider running CHECKDB again, but with repair parameters such as DBCC CHECKDB ('<database name>', REPAIR_ALLOW_DATA_LOSS)
    The 'ALLOW_DATA_LOSS' option is more effective at returning the database to operational status, but as the name implies it will delete all unrecoverable data records, which might be a serious loss (and take a long time) depending on the extent of the corruption problem.
    Consult Books Online or MSDN for the various repair options that you have for the DBCC CHECKDB command.
    Once that all repairs are done and DBCC CHECKDB is reporting no problems then you can use the ALTER DATABASE command to bring the repaired database online again.

    Again I will say; First prize would be a database restore from a recent backup if you have one.
    If you need to recover without a backup, using only repair commands, then you're probably going to lose at least a little data along the way.
  4. sqlserverscrap New Member

    Thanks for the Reply David. I have already restored the DB with the Full backups and Transaction Logs. The Corruption has been happening At least Once a year. I do think the same that It is some Hardware issue but not able to prove it as the System teams does not find any problem with the Hardware.

Share This Page