infolink

Friday, September 16, 2011

How to solve Micrososft SQL Server 2008 R2 Error: 1813

Recently I had a machine go down. I was able to get into recovery mode and transfer off most of the files. When I rebuilt the machine and reinstalled SQL server, I tried to attached my old database from the backup I took.

When I went to attach my log file I got the following error:

Attach database filed for Server 'SERVERNAME\INSTANCE'. (Microsoft.SqlServer.Smo)

Additional Information:
An excepetion occured while excuting a Trasact-SQL statement or batch.
(Microsoft.SQLServer.ConnectionInfo)

Could not open new database 'YOURDATABASENAME'. CREATE DATABASE is aborted.
File activation failure. The physical file name 'LOG FILE LOCATION' may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environmental failure. (Microsoft SQL Server, Error: 1813)


Unfortunatly, the SQL log file was currupt and I didn't have a copy due to the server going down. So now what!?!?


The Solution:

Create a new database with the same exact name.

CREATE DATABASE YOURDATABASENAME
GO

Now stop Micrososft SQL Server.
Replace the MDF file with the old MDF file.
Start Micrososft SQL Server.

If everything went well the database should be in "Suspect" mode, which you check by running this:

SELECT DATABASEPROPERTYEX ('YOURDATABASENAME', 'STATUS') AS 'Status';
GO

Your database should now be in 'SUSPECT' mode.

Let's put her in emergency mode:

ALTER DATABASE YOURDATABASENAME SET EMERGENCY;
GO

Then single user mode:

ALTER DATABASE YOURDATABASENAME SET SINGLE_USER;
GO

And Finally:
DBCC CHECKDB (YOURDATABASENAME, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO



Buy Me a Beer

31 comments:

  1. OMG Ty, this was exactly what I needed to recover a DB that the Log file drive was lost.

    ReplyDelete
  2. I still get the same error when I try.
    DBCC CHECKDB (YOURDATABASENAME, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

    Error.
    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database,

    ReplyDelete
  3. Suraj, did you follow the steps laid out exactly?

    ReplyDelete
  4. Thanks James... a great help that saved me a lot of grief.

    ReplyDelete
  5. i have a 50GB database, how long does this usually take?

    ReplyDelete
    Replies
    1. Should not take too long at all. Is it hanging for you at a certain step?

      Thank you,
      James Campbell
      MCP, MCSA, MCSE, COMPTIA Security +

      Delete
  6. I'm trying to do this using SQL Express 2012, but the database was created using SQL Express 2008. I get an error telling me to 'upgrade the database' when attempting the DBCC. Any options to upgrade?

    ReplyDelete
  7. Anonymous,

    I would install SQL express 2008 and try it, they can run side by side. Let me know if you still have issues.

    ReplyDelete
  8. This did not work for me. I think my database is just corrupt. The system crashed one day, and the IT department copy and pasted the database, presumably without detaching it.

    ReplyDelete
    Replies
    1. John,

      I have had several corrupt databases and this method has worked for me every time. Make sure you are following directions exactly as written.

      Thanks,
      Jim

      Delete
  9. I got the message after last step:

    Msg 922, Level 14, State 1, Line 1
    Database 'databasename' is being recovered. Waiting until recovery is finished.

    What should I do?

    ReplyDelete
    Replies
    1. What version of SQL server are you running? Make sure it is up to date with all service packs for your version of SQL server.

      Delete
  10. Sorry, I am unclear as to what you mean by "replace MDF"

    Can you please explain this a bit.

    Thank you

    ReplyDelete
    Replies
    1. OK, what I meant by replace MDF is the following. In my steps I have you create a new database with the same name, when you do this it creates a database file which is a MDF file. I now have you stop SQL server. Now go into the data directory and delete the new MDF file you created and copy the database MDF file you are having an issue with in it's place, then . Any questions let me know and good luck. PS I had some messages that were stuck in the approval queue and I am sure you are no longer dealing with this issue, but figure if this can help someone else, than great.
      Now start SQL server and the database will be in suspect mode.

      Thank you,
      James Campbell

      Delete
  11. Hi,

    I was able perform all the steps, however at the last script it gave me this error:

    Msg 946, Level 14, State 1, Line 1
    Cannot open database 'DatabaseName' version 661. Upgrade the database to the latest version.

    ReplyDelete
    Replies
    1. Hi Masson,

      Sorry you are having issues. I am not sure why you are getting that error, but I would try to detach and reattach. If you can do this, see if you can then change the compatibility mode of the database to the current version of SQL server.

      Thanks,
      Jim

      Delete
  12. Hi,
    I am having SQL Server 2008 and got below error.

    Msg 946, Level 14, State 1, Line 1
    Cannot open database 'Nce' version 539. Upgrade the database to the latest version.

    ReplyDelete
    Replies
    1. Try a search for that error: http://www.bing.com/search?FORM=U161DF&PC=U161&q=Msg+946%2C+Level+14%2C+State+1%2C+Line+1&src=IE-SearchBox

      Delete
  13. Thank-you! Thank-you! Thank-you!!!! This was a life saver!

    ReplyDelete
  14. I still get the same error when I try.
    DBCC CHECKDB (YOURDATABASENAME, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

    Error.
    The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database

    please help!!!

    ReplyDelete
  15. Great, work for me.
    Thanks for your constribuitions.

    ReplyDelete
  16. am getting this error at the last step.
    Database is being recovered. Waiting until recovery is finished. help please

    ReplyDelete
    Replies
    1. I would try the steps again and see if that helps. There are situations where this may not solve the error as the database is beyond repair. - Jim

      Delete
  17. This is a time saver! Kudos

    ReplyDelete
    Replies
    1. I am glad I could help you. SQL Server error 1813 caused me a lot of grief, I am glad I could assit you in solving your issue. -Jim

      Delete
  18. My result to query "SELECT DATABASEPROPERTYEX ('[Transporte_Trafego]', 'STATUS') AS 'Status';
    GO" is NULL. How can i solve this?

    ReplyDelete

Amazon1