none
Suspect SQL database

    Question

  • Deall All

    I am getting this error message and my SQL 2005 database is showing "SUSPECT". I need to recover the transcations in the latest transcation log.

    The log scan number (195881:359:1) passed to log scan in database 'XXXXXX' 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, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

    Kindly help 

    Wednesday, October 27, 2010 11:35 AM

Answers

  • Looks like the Transaction Log is corrupt. The best option is to restore from the last good backups. Restore the FULL backup WITH NORECOVERY and then all the transaction log backups but the last one WITH NORECOVERY. Restore the last transaction log backup WITH RECOVERY. More information here


    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Wednesday, October 27, 2010 12:53 PM

All replies

  • Looks like the Transaction Log is corrupt. The best option is to restore from the last good backups. Restore the FULL backup WITH NORECOVERY and then all the transaction log backups but the last one WITH NORECOVERY. Restore the last transaction log backup WITH RECOVERY. More information here


    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Wednesday, October 27, 2010 12:53 PM
  • Hi there,

    Please follow one of these two articles to resolve.

    1. How to repair a Suspect Database in SQL Server

    2. Recommended actions for corrupt or suspect databases

     

    Thanks,

    Nimit 


    Thank you, Nimit P Parikh
    • Proposed as answer by retracement Wednesday, April 18, 2012 11:33 PM
    Thursday, October 28, 2010 12:58 AM
  • Hi,

    I am having the same problem ("... The log scan number passed to log scan in database 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, re-create the publication. ..."  (I use the SQL Server 2008 Express edition).

    I see that quite a lotof people have already had this problem, but so far - to my surprise - no real solution has been offered apart from restoring the corrupted database:  After having searched through the forum here, this seems to be the only recommendation /suggestion. 

    However, this is clearly NOT the solution.  This error is happening again and again, and it seems that somehow when the database (mdf-file) is accessed from an application in Visual Studio 2010 (I am using C#), and I thereafter try to open the same database in SQL Server Management Studio, then this message appears, and after this when I try to open it with the VS2010 application I am also getting a similar message there.  Sure I can restore the database , but that does not stop the problem from reoccurring. 

    I have already removed and re-installed VS2010 Pro and updated it with the latest updates so some corrupted files in VS can also also not be the reason.

    So clearly, something is going wrong as a result of the database being accessed from the VS2010 application.  It seems that most likely the root cause is that the connection from VS is not updating the log file simultaneously with the mdf file, thus causing this problem.   BUT IF SO, WHY IS THE LOG FILE NOT BEING UPDATED? 

    I suspect the problem and its solution will be know to experienced SQL2008 users.  Help would be greatly appreciated!

    ---------------------------------

    THE SOLUTION SEEMS TO BE (this solved this problem for me):

    1) make sure the mdf and log files are in the SQL Servers standard DATA folder :

    (C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA) If they are not, then detach the database from the server (SSMS), move the files to this data folder, and then attach the database again with SQL Server Management Studio (SSMS).

    2) keep the server running

    3) when setting up the connection string (with the data source configuration wizard), choose the option "Microsoft SQL Server".  (In other words, do not choose the option: "Microsoft SQL Server Database File").

    4) In the next dialogue box, choose "Select or enter a database name" and select the name of your database (which you attached and which is up and running after steps 1 and 2 above) and which should appear in the listbox.  (In other words: Do not choose the option "Attach a database file").  Complete the wizard; and reply no to the option of copying the database file if this comes up.

    Keep the sql server running.

    5) Now develop/ run the application, you should no longer get the error messages above.

    I leave it to you to figure out why this solved the problem :-)

    Akdbtan


    • Proposed as answer by akdbtan Saturday, April 14, 2012 2:25 AM
    • Unproposed as answer by akdbtan Saturday, April 14, 2012 2:25 AM
    • Proposed as answer by akdbtan Saturday, April 14, 2012 2:49 AM
    • Edited by akdbtan Saturday, April 14, 2012 2:51 AM
    Friday, April 13, 2012 11:56 PM
  • Wednesday, April 18, 2012 9:22 PM