SQL SERVER – Transaction Log Backup Failing After Rebuilding Database Log File

Speaking at community events get me really energized. It is wonderful to connect with people who build some of the greatest code ever for their respective organizations. Last weekend, I was at a conference in Delhi and it was an awesome opportunity for me to meet a number of you. I would take a moment to Thank all who took a minute to walk to me and talk to me about your experience. These experiences are what make me who I am.

Coming out of the conference I had this one moment that I think just made me work on something quick and I thought this is worth a share as a blog. This was a corridor conversation during the first day end that this DBA walked up to me and said, if I was up for a challenge as they were facing some problem recently in their deployment. After doing a couple of sessions, I was a bit tired but this challenge got me thinking. I stopped and said, I am game to hear you. I will try to answer and if I can’t, meet me first thing tomorrow morning and we should have something.

He started by saying – “Pinal, our transaction log backups are failing.”

Pinal: That is a start, you need to let me know what the error is.

DBA: Well, it is part of our test box. We have a maintenance plan and it started to fail. So there is no error that I can see yet.

Pinal: Was it working in the past?

DBA: Oh yeah!! It used to work and now the maintenance plans are not working I think.

Pinal: Did you guys do anything different on this box before this error started surfacing?

DBA: Hmmm … Last week we had a bad hard disk corruption. We detached the database and then we replaced the HDD. Then we attached the DB back.

Pinal: Is there anything that you folks did differently while attaching?

DBA: Oh yeah, when the HDD crashed – we lost the Log file and so when we attached we just rebuilt the Log.

Pinal: Well, you have the answer with you. That is the problem. Did you know that?

DBA: Is it? It was configured as Full recovery model. Everything was fine Pinal.

Pinal: You are correct, I want to show you what happened behind the scene. Meet me tomorrow.

That evening I crashed early and got up early to bring a repro to this problem. I love such challenges that make me learn.

So here is the steps to reproduce this situation.

Step 1: Create Database and make it FULL recovery model

This is the simple part.

CREATE DATABASE [RebuildLogDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'RebuildLogDB', FILENAME = N'C:\Rebuild\RebuildLogDB.mdf', 
SIZE = 10240KB, FILEGROWTH = 1024KB) 
LOG ON
( NAME = N'RebuildLogDB_log', FILENAME = N'C:\Rebuild\RebuildLogDB_log.ldf', 
SIZE = 5120KB, FILEGROWTH = 1024KB)
GO
ALTER DATABASE [RebuildLogDB] SET RECOVERY FULL
GO

-- Take a FULL backup to make it truly Full Recovery Model
BACKUP DATABASE [RebuildLogDB] TO DISK = N'NUL'
WITH NOFORMAT, NOINIT, NAME = N'RebuildLogDB-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 2: Detach the Database

We will detach the Database and then we will go ahead and delete the Log file manually from the folder.

-- Detach the DB
USE [MASTER]
GO
ALTER DATABASE [RebuildLogDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [MASTER]
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'RebuildLogDB'
GO

Step 3: Attach the DB without Log file

-- Attach without the Log file.
USE [master]
GO
CREATE DATABASE [RebuildLogDB] ON
( FILENAME = N'C:\Rebuild\RebuildLogDB.mdf') 
FOR ATTACH
GO


On executing the above, we will be given a warning message like below:

File activation failure. The physical file name “C:\Rebuild\RebuildLogDB_log.ldf” may be incorrect.

New log file ‘C:\Rebuild\RebuildLogDB_log.ldf’ was created.

The database would have been created. To test the scenario my DBA friend was saying, I went to SSMS and tried to take a Transaction Log Backup. But was surprised we didn’t have the option.

SQL SERVER - Transaction Log Backup Failing After Rebuilding Database Log File rebuildlog-simple-recovery-01

The catch here is hidden in the “Recovery Model” that is shown above. If you notice, it is showing as “SIMPLE” – which means we willnot be able to take a TLog backup without changing the same.

The resolution for this is simple, change the recovery model to FULL and take a Full backup for TLog backups to succeed now.

SQL SERVER - Transaction Log Backup Failing After Rebuilding Database Log File rebuildlog-simple-recovery-02

Once we do this, everything will come back to normal status. This was an example to show you what happens when we rebuild the Log without knowing it changes the Recovery Model to Simple in the process.

PS: Please DONOT do this in your production environment.

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

Previous Post
Interview Question of the Week #014 – How to DELETE Duplicate Rows
Next Post
SQL SERVER – What is Filtered Statistics?

Related Posts

1 Comment. Leave new

  • Minette Del Mundo
    October 2, 2018 5:33 am

    Hi, i’m wondering how did you create a mdf and ldf file because I get this error when I run the first part.

    Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “C:\Rebuild\RebuildLogDB.mdf” failed with the operating system error 2(The system cannot find the file specified.).
    Msg 1802, Level 16, State 1, Line 1
    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
    Msg 5011, Level 14, State 5, Line 1
    User does not have permission to alter database ‘RebuildLogDB’, the database does not exist, or the database is not in a state that allows access checks.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.

    Reply

Leave a Reply

Menu