Log record ID corruption

  • I see these error messages..As per my earlier experience, this looks like data corruption. Please throw some light on this

    During undoing of a logged operation in database 'Maximum', an error occurred at log record ID (112736:2730:213). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

    2012-03-04 04:17:28.17 spid416 Error: 9001, Severity: 21, State: 1.

    2012-03-04 04:17:28.17 spid416 The log for database 'Maximum' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

    2012-03-04 04:17:39.34 spid28s SQL Server has encountered 7 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

    2012-03-04 04:17:39.36 spid28s SQL Server has encountered 7 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

    2012-03-04 04:17:39.41 spid28s Starting up database 'Maximum'.

    2012-03-04 04:17:42.21 spid28s 1219 transactions rolled forward in database 'Maximum' (29). This is an informational message only. No user action is required.

    2012-03-04 04:17:42.35 spid28s 1 transactions rolled back in database 'Maximum' (29). This is an informational message only. No user action is required.

    2012-03-04 04:17:42.35 spid28s Recovery is writing a checkpoint in database 'Maximum' (29). This is an informational message only. No user action is required.

    2012-03-04 04:17:42.65 spid28s Error: 17053, Severity: 16, State: 1.

    2012-03-04 04:17:42.65 spid28s I:\Microsoft SQL Server\MSSQL.2\MSSQL\LOGS\Maximum.ldf: Operating system error 112(error not found) encountered.

    2012-03-04 04:17:42.66 spid28s Error: 9002, Severity: 17, State: 2.

    2012-03-04 04:17:42.66 spid28s The transaction log for database 'Maximum' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

  • What state is the database in at the moment? (state_desc in sys.databases)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/6/2012)


    What state is the database in at the moment? (state_desc in sys.databases)

    I am afraid, I don't find the state_desc column in sys.databases..

  • Status_desc

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/6/2012)


    Status_desc

    I see only the status and status2 columns and following are the values:

    Status - 16

    Status2 - 1090519040

  • execute the follow t-sql:

    select name, state_desc from sys.databases

    where name = 'maximum'


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • rfr.ferrari (3/7/2012)


    execute the follow t-sql:

    select name, state_desc from sys.databases

    where name = 'maximum'

    Error message:

    Msg 207, Level 16, State 1, Line 1

    Invalid column name 'status_desc'.

  • sorry, is state_desc!!!

    select name, state_desc

    from sys.databases

    where name = 'maximum'


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Query sys.databases, not the old sysdatabases view. I can't recall offhand if it's state_desc or status_desc, query it and see, post the value.

    The old sysdatabases view is for backward compatibility with SQL 2000 only and is missing a lot of information that the newer sys.databases view returns.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/7/2012)


    Query sys.databases, not the old sysdatabases view. I can't recall offhand if it's state_desc or status_desc, query it and see, post the value.

    The old sysdatabases view is for backward compatibility with SQL 2000 only and is missing a lot of information that the newer sys.databases view returns.

    state_desc is showing as online only..

  • Benki Chendu (3/7/2012)


    ...... state_desc is showing as online only..

    is database working fine?

    did you lost any data???


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • rfr.ferrari (3/7/2012)


    Benki Chendu (3/7/2012)


    ...... state_desc is showing as online only..

    is database working fine?

    did you lost any data???

    It seems to be working fine now.

    But, we had a P1 incident over the weekend when few websites went down which were using this database..

    SQL Instance was up and running. It seems the customer took the database offline and got it back online and the issue was resolved.

    When I was going through logs, I found these messages and hence, thought of taking some inputs here:-)

  • are they running administrative commands on server? (for example: DBCC) or the access only by website without admin commands?


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • rfr.ferrari (3/7/2012)


    are they running administrative commands on server? (for example: DBCC) or the access only by website without admin commands?

    They access only by website without admin commands as per my understanding

  • Check for any IO-related errors in the windows event log, SAN logs, etc.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply