SQL SERVER – Unable to Attach Database – File Activation Failure – The Log Cannot be Rebuilt

Once I was in a situation where there was a disaster faced by my client. They lost the drive which was having transaction log file. Since they did not have effective monitoring, they realized that the backup jobs were failing. So essentially they only have MDF files for user databases and they were left with option to rebuild the log. Let us learn about how file activation failure can create an interesting error.

They tried below command.

CREATE DATABASE UserDB
ON (FILENAME = 'E:\DATA\UserDB.mdf')
FOR ATTACH_REBUILD_LOG

But, this was the error received while attaching the database.

File activation failure. The physical file name “E:\LOG\UserDB_log.ldf” 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 environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘UserDB’. CREATE DATABASE is aborted.

The error is very clear and interesting. So, I thought of reproducing it

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE Foo (bar INT)
GO
BEGIN TRANSACTION
INSERT INTO Foo VALUES (1)

Yes, there is no rollback transaction because I wanted to leave open transaction, as mentioned in the error message. Once done, I stopped SQL Server, renamed MDF and LDF file for the database and started the SQL Server service. As expected, the database came to “Recovery Pending” State.

Here was the cause in ERRORLOG

2016-09-29 00:31:55.86 spid21s Starting up database ‘SQLAuthority’.
2016-09-29 00:31:55.87 spid21s Error: 17204, Severity: 16, State: 1.
2016-09-29 00:31:55.87 spid21s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
2016-09-29 00:31:55.87 spid21s Error: 5120, Severity: 16, State: 101.
2016-09-29 00:31:55.87 spid21s Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.
2016-09-29 00:31:55.87 spid20s [INFO] HkHostDbCtxt::Initialize(): Database ID: [4] ‘msdb’. XTP Engine version is 0.0.
2016-09-29 00:31:55.87 spid21s Error: 17207, Severity: 16, State: 1.
2016-09-29 00:31:55.87 spid21s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_log.ldf’. Diagnose and correct the operating system error, and retry the operation.
2016-09-29 00:31:55.87 spid21s File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_log.ldf” may be incorrect.

At this point, since I wanted to reproduce client situation, I dropped the database so that I should try to attach MDF file. I renamed file to “SQLAuthority_original.mdf’”

CREATE DATABASE SQLAuthority ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_original.mdf')
FOR ATTACH
CREATE DATABASE SQLAuthority ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_original.mdf')
FOR ATTACH_REBUILD_LOG

Both commands failed with the same error

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_log.ldf” 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 environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘SQLAuthority’. CREATE DATABASE is aborted.

SOLUTION/WORKAROUND

There is an undocumented option called as ATTACH_FORCE_REBUILD_LOG. Here is the command which worked in my lab.

CREATE DATABASE SQLAuthority ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_original.mdf')
FOR ATTACH_FORCE_REBUILD_LOG

Here was the message:

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_log.ldf” may be incorrect.
New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\SQLAuthority_log.ldf’ was created.

Restoring from backup is always a best solution and above is the last resort. Have you even been in such situation?

Reference: Pinal Dave (https://blog.sqlauthority.com)