SQL SERVER – Fix : Error Msg 1813, Level 16, State 2, Line 1 Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

Fix : Error Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

This errors happens when corrupt database log are attempted to attach to new server. Solution of this error is little long and it involves restart of the server. I recommend following all the steps below in order without skipping any of them.

Fix/Solution/Workaround:

SQL Server logs are corrupted and they need to be rebuilt to make the database operational.
Follow all the steps in order. Replace the yourdatabasename name with real name of your database.
1. Create a new database with same name which you are trying to recover or restore. (In our error message it is yourdatabasename). Make sure the name of the MDF file (primary data file) and LDF files (Log files) same as previous database data and log file.

2. Stop SQL Server. Move original MDF file from older server (or location) to new server (or location) by replacing just created MDF file. Delete the LDF file of new server just created.

3. Start SQL Server. Database will be marked as suspect, which is expected.

4. Make sure system tables of Master database allows to update the values.
USE MASTER
GO
sp_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

5. Change database mode to emergency mode.
–Following statement will return the current status of the database
SELECT *
FROM sysdatabases
WHERE name = 'yourdatabasename'

—-Following statement will update only one row in database
BEGIN
UPDATE
sysdatabases
SET status = 32768
WHERE name = 'yourdatabasename'
COMMIT TRAN

6. Restart SQL Server (This is must, if it is not done SQL Server will through an error)

7. Execute this DBCC command in query window of Management Studio, this will create new log file. Keep the name of this file same as LDF file just deleted from new server :
DBCC TRACEON (3604)
DBCC REBUILD_LOG(yourdatabasename,'c:\yourdatabasename_log.ldf')
GO

DBCC accepts two parameters : first parameter is database name and second parameter is physical path of the log file. Make sure the path is physical, if you put logical file name it will return an error.

8. Reset the database status using following command.
sp_RESETSTATUS yourdatabasename
GO

9. Turn off the update to system tables of Master database running following script.
USE MASTER
GO
sp_CONFIGURE 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO

This should be resolve the problem mentioned above. I always check consistence of the database as well as I reset the status of the database to original status.

10. Reset the database status to previous status
–Following statement will update only one row in database
BEGIN
UPDATE
sysdatabases
SET status = (value retrieved IN first query OF STEP 5)
WHERE name = 'yourdatabasename‘
COMMIT TRAN
GO'

Note : If during steps 8, 9 , 10 if there is error if database is in use.
Set the database to status single user.
sp_DBOPTION 'yourdatabasename', 'single user','true'
Once the steps 8,9,10 are completed if database is not already in multi user mode run this script.
sp_DBOPTION 'yourdatabasename', 'single user','false'
If there is any issue while doing above process to fix the error let me know. Make sure that you have done all the steps in order and restarted SQL Server where it is mentioned.

Reference : Pinal Dave (https://blog.sqlauthority.com), BOL (many topics)

, ,
Previous Post
SQL SERVER – Fix : Error Msg 4214 – Error Msg 3013 – BACKUP LOG cannot be performed because there is no current database backup
Next Post
SQLAuthority News – FQL – Facebook Query Language

Related Posts

86 Comments. Leave new

  • Thank you so much for this fix it tip, my client could not work for over a week because of the database crash, six months of data loss was at at stake.

    Reply
  • There are some diffrences in SQL Server 2012. For example you should use ALTER … EMERGENCY code instead of 32xxx number. But it works :D Thanks..

    Reply
  • Hi Pinal,

    I am using SQL Server 2008 R2.

    I do not have any backup. due to some hardware issue the server got shut down abruptly. now that the server is up but there is issue with SQL server.
    i have the .mdf file but unable to connect. I followed the above mentioned steps but no success…

    infact during step 5 while updating the status i get the following message.
    “Ad hoc updates to system catalogs are not allowed.”.

    Kindly help..my butt is on fire now..

    Reply
  • on step 5, get an error:
    “Ad hoc updates to system catalogs are not allowed.”.
    i couldn’t advance…help please…

    Reply
  • Priscila Caceres
    November 14, 2014 1:19 am

    Very very very helpful information. Just recovered an old but large database with this directions. Thank you so much!

    Reply
  • “I/O error (bad page ID) detected during read at offset 0x0000040bd80000 in file ‘d:MSSQLdataKHKD_Data.MDF’.”
    what?

    Reply
    • Van – database MDF file has corruption – mostly due to hardware issues. Restore from backup.

      Reply
  • i am getting this error while executing ”
    BEGIN
    UPDATE sysdatabases
    SET status = 32768
    WHERE name = ‘yourdatabasename ‘
    COMMIT TRAN”

    Msg 102, Level 15, State 1, Line 6
    Incorrect syntax near ‘TRAN’.

    Can anyone help

    Reply
  • Cant’ thank you enough for this solution… I looked everywhere until I found this wonrderful way, which worked perfectly, the operation guys thank me and I thank you for saving 680 GB of unbacked up data on SQL Server 2000.

    Reply
  • SQL Server 2012 Database does not mark as suspect instead go into restoring and get stuck there. Could not change it to suspect or emergency mode. Deleting files for db does not put it into suspect mode. How could this be done in sql server 2012

    Reply
  • Hello
    While using

    BEGIN TRAN
    UPDATE sysdatabases
    SET status = 32768
    WHERE name = ‘wid_kku’
    COMMIT TRAN

    it shows an error:
    Msg 259, Level 16, State 1, Line 2
    Ad hoc updates to system catalogs are not allowed.

    I am using SQL Server 2012
    Any Help please

    Reply
  • Stefan Kleinhans
    July 19, 2017 6:39 pm

    BEGIN TRAN
    UPDATE sysdatabases
    SET status = 32768
    WHERE name = ‘IntouchCS’
    COMMIT TRAN

    Error
    Msg 259, Level 16, State 1, Line 2
    Ad hoc updates to system catalogs are not allowed.

    Used

    Reply
  • Stefan Kleinhans
    July 19, 2017 6:42 pm

    BEGIN TRAN
    UPDATE sysdatabases
    SET status = 32768
    WHERE name = ‘IntouchCS’
    COMMIT TRAN

    Error
    Msg 259, Level 16, State 1, Line 2
    Ad hoc updates to system catalogs are not allowed.

    Used
    EXEC sp_resetstatus IntouchCS;
    ALTER DATABASE IntouchCS SET EMERGENCY

    DB is Emergency mode

    DBCC TRACEON (3604)
    DBCC REBUILD_LOG (‘IntouchCS’,’F:\IntouchCS_log.ldf’)
    GO

    Error
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Msg 2526, Level 16, State 3, Line 2
    Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.

    Please Advice
    I have a 400+ log file that i removed and would like to create a new log file.
    I don’t care about the data as i have a back up, and need to get the DB to a working fresh starting point

    followed all steps but no luck.

    really desperate

    Reply
  • To my best friend Dave, this is the sql 2016 version (2012+?) hope others find it helpful!!!

    /* Modified step tips:
    a. rename the old log file if present like blah_log_OLD just to be safe
    b. make sure to rename the dbs carefully to not mix them up or accidently delete
    c. once confirmed everything is working, delete the fake database files and the unutilized older log file if no longer needed
    */

    /*
    SQL Server logs are corrupted and they need to be rebuilt to make the database operational.
    Follow all the steps in order. Replace the yourdatabasename name with real name of your database.
    1. Create a new database with same name which you are trying to recover or restore. (In our error message it is yourdatabasename). Make sure the name of the MDF file (primary data file) and LDF files (Log files) same as previous database data and log file.

    2. Stop SQL Server. Move original MDF file from older server (or location) to new server (or location) by replacing just created MDF file. Delete the LDF file of new server just created.

    3. Start SQL Server. Database will be marked as suspect, which is expected.

    4. Make sure system tables of Master database allows to update the values.

    */

    USE MASTER
    GO
    sp_CONFIGURE ‘allow updates’, 1
    RECONFIGURE WITH OVERRIDE
    GO

    /*
    5. Change database mode to emergency mode.
    –Following statement will return the current status of the database
    */

    /* 2005-2008 */
    /*
    BEGIN
    UPDATE sysdatabases
    SET status = 32768
    WHERE name = ‘yourdatabasename’
    COMMIT TRAN
    */

    /* this is replaced in sql2012+ */
    ALTER DATABASE yourdatabasename SET EMERGENCY;

    /*
    6. Restart SQL Server (This is must, if it is not done SQL Server will through an error)

    7. Execute this DBCC command in query window of Management Studio, this will create new log file. Keep the name of this file same as LDF file just deleted from new server :
    DBCC TRACEON (3604)
    DBCC REBUILD_LOG(yourdatabasename,’c:\yourdatabasename_log.ldf’)
    GO

    */

    DBCC TRACEON (3604)

    /* sql 2005-2008 command */
    /*
    DBCC REBUILD_LOG(yourdatabasename,’c:\yourdatabasename_log.ldf’)
    GO
    */

    ALTER DATABASE yourdatabasename REBUILD LOG ON (NAME=yourdatabasename_log,FILENAME=’A:\sql_log\yourdatabasename_log.ldf’)

    /*
    DBCC accepts two parameters : first parameter is database name and second parameter is physical path of the log file. Make sure the path is physical, if you put logical file name it will return an error.

    8. Reset the database status using following command.
    */

    sp_RESETSTATUS yourdatabasename

    /* 9. Turn off the update to system tables of Master database running following script. */
    USE MASTER
    GO
    sp_CONFIGURE ‘allow updates’, 0
    RECONFIGURE WITH OVERRIDE
    GO

    /* Take db out of emergency mode and back to MULTI_USER */
    ALTER DATABASE yourdatabasename SET MULTI_USER;

    GO

    Reply
  • DBCC TRACEON (3604)
    DBCC REBUILD_LOG(yourdatabasename,’c:yourdatabasename_log.ldf’)
    GO
    can not create ldf file with command wrong syntac in line 2

    Reply

Leave a Reply

Menu