Day 24: Attach database errors

Day 24 Attach database errors

Microsoft recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach. ALTER DATABASE (Transact-SQL). “

If you do not have permission to attached the database :

Msg 5120, Level 16, State 101, Line 1

Unable to open the physical file “c:\xxx.mdf”. Operating system error 5: “5(Access is denied.)”.

If some transaction was happening and you detached the database and tried to attached the database without log file.

Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘xxx’. CREATE DATABASE is aborted.
Msg 3456, Level 21, State 1, Line 1
Could not redo log record (000:0:0), for transaction ID (000:0:0), on page (000:00:0000)., database ‘xxx’ (database ID y). Page: LSN = (00:0:0), type = 2. Log: PrevPageLSN: (000:0:0). Restore from a backup of the database, or repair the database.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database ‘xxx’, an error occurred at log record ID (000:00:0000). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

Resolution:

                Create a temporary database and attaché on that database with only mdf. –

*the active transaction will be lost.

If database was not properly detached (without detached) trying to attached you may get following error, here file location on other server is different as well:

Msg 1813, Level 16, State 2, Line 1

Could not open new database ‘xxx’. CREATE DATABASE is aborted.

File activation failure. The physical file name “xxx.ldf” may be incorrect.

The log cannot be rebuilt because the database was not cleanly shut down.

If path of attached db is not correct:

Msg 5123, Level 16, State 1, Line 15
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘F:\xxx.LDF’.

If ldf is already exists from different db

Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Log file ‘G:\xxx.LDF’ does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.

http://msdn.microsoft.com/en-us/library/ms190794.aspx

For attaching the database you should properly detach the database.

Advertisements
This entry was posted in Troubleshooting and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s