I had fun deleting transaction logs today

I’ve read about people deleting their transaction log files but I have never tried it myself (for good reasons). Today, with some time on my hands I thought it would be fun to delete a transaction log and try to recover a database from that state.

I set up a test environment for this exercise:

use master
go

if @@TRANCOUNT > 0 
    rollback
go

if db_id('accident') is not null 
    drop database accident
go

create database accident 
on 
(
    name = 'accident'
    , filename = 'c:\temp\accident.mdf'
) 
log on 
(
    name = 'accident_log'
    , filename = 'c:\temp\accident_log.ldf'
)
go

use accident
go

create table critical 
(
    data varchar(10)
)
go

insert critical values ('good')
go

begin tran

insert critical values ('bad')

checkpoint -- flush dirty pages to disk

Once it was done, I opened another query window and had a look at the database options:

select 
    name
    , user_access_desc
    , state_desc
    , recovery_model_desc
    , databasepropertyex('accident', 'updateability') as 'updateability'
from sys.databases
where name = 'accident'

image

I knew if I just tried to delete the transaction log file while the database was still online, I would get a “file in use” error message from Windows.

image

In order to delete the file, I need to tell SQL Server to let go of the file, there are three ways of achieving it:

  1. Take database offline.
  2. Detach database.
  3. Shut down SQL Server.

1. Take database offline

According to Books Online, if the database is set to offline, it will shut down cleanly. I’d also found the following statement from Paul Randal’s blog post:

…there’s a feature in SQL Server 2005 that if you attach or startup a database without a transaction log file, and the database was cleanly shut down, SQL Server will create a new log file automatically.

Armed with the knowledge, I executed:

alter database accident set offline

To make it work, I had to:

  • Rollback the open transaction.
  • Change the current database context from “accident” to something else.

In other words, I had to make sure the database was not in use before issuing the command. Eventually I managed to switch off the database and deleted the transaction log file:

image

I was ready to bring the database back online again:

alter database accident set online

Unexpectedly, I received the following error messages:

image

I turned to the error log:

sp_readerrorlog

and found:

image

During upgrade? It didn’t make any sense to me. I googled and googled. I went back and read Paul Randal’s blog posts over and over again. After literally several hours of trying different combination of statements, I’d made one observation which led me to a solution. Pity that I still don’t understand why the statement failed in the first place.

The observation was that the database would be set to the simple recovery model after executing “dbcc checkdb” with the repair_allow_data_loss option. I will talk about this statement later. For now, I just needed to issue the following commands to bring the database back online:

alter database accident set recovery simple
alter database accident set online

image

Bingo! The database options were:

image

Recap:

  1. Take database offline.
  2. Delete transaction log file.
  3. Set recovery model to simple.
  4. Bring database online.

2. Detach database

I returned the database back to the initial state and confirmed the database options:

image

I tried to detach the database:

sp_detach_db 'accident'

After 20 seconds:

image

Just like before, I had to make sure the database was not in use before I could successfully detach it. Once the database was detached, I could delete the transaction log file.

After that, I then tried to attach the database with only the primary data file:

create database accident 
on 
(
    name = 'accident'
    , filename = 'c:\temp\accident.mdf'
) 
for attach

image

Because the database was cleanly shutdown, the log file was recreated. Have you noticed something? This time, I wasn’t required to set the recovery model to simple. I took a look at the database options:

image

The create database with attach statement had set the recovery model to simple itself.

Recap:

  1. Detach database.
  2. Delete transaction log file.
  3. Attach database.

3. Shut down SQL Server

I returned the database back to the initial state and confirmed the database options:

image

I was ready to shut down SQL Server:

image

Once the service was stopped successfully, I deleted the transaction log file and then started SQL Server:

image

Everything seemed to be fine, I quickly had a look at the database options:

image

SQL Server could not recover the database because the log file was missing. I decided to bring the database online and see what happens:

alter database accident set online;

image

Since the database wasn’t shutdown cleanly, neither setting it online or attaching data file would work, but I had another card up my sleeve:

dbcc checkdb('accident', repair_allow_data_loss) 
with no_infomsgs, all_errormsgs

Something wasn’t quite right:

image

No worries, Books Online to the rescue:

When a database has been set to emergency mode by using the ALTER DATABASE statement, DBCC CHECKDB can perform some special repairs on the database if the REPAIR_ALLOW_DATA_LOSS option is specified. These repairs may allow for ordinarily unrecoverable databases to be brought back online in a physically consistent state. These repairs should be used as a last resort and only when you cannot restore the database from a backup. When the database is set to emergency mode, the database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.

I couldn’t wait to try the emergency mode:

alter database accident set emergency

The database options showed:

image

So far so good, I then executed the repair command again:

dbcc checkdb('accident', repair_allow_data_loss) 
with no_infomsgs, all_errormsgs

image

Ok, no worries, I switched the database into single user mode and did it again:

alter database accident set single_user

dbcc checkdb('accident', repair_allow_data_loss) 
with no_infomsgs, all_errormsgs

image

Mmm, juicy, I like detailed error/warning messages. However, I’d made a few puzzling observations.

Observation 1

The third paragraph contradicts the second one, but that’s ok, my guess is the checkdb command encapsulates a few other statements underneath and it has no control over the output of those statements.

Observation 2

The database has been put in dbo-only mode? According to Books Online:

Transact-SQL Reference (SQL Server 2000) RESTORE

RESTRICTED_USER
Restricts access for the newly restored database to members of the db_owner, dbcreator, or sysadmin roles. In SQL Server 2000, RESTRICTED_USER replaces the DBO_ONLY option. DBO_ONLY is available only for backward compatibility.

Furthermore, since the DATABASEPROPERTY function has been superseded by DATABASEPROPERTYEX and the new function mentions RESTRICTED_USER but not DBO_ONLY, I was comfortable in assuming that :

dbo-only = restricted_user

The database options somehow disagreed:

image

The database was still in SINGLE_USER mode. I tried another command:

sp_helpdb 'accident'

image

Did you see that? UserAccess=MULTI_USER. From memory I knew the data in the status column was gathered from DATABASEPROPERTYEX, so I double checked:

select databasepropertyex('accident', 'useraccess')

image

It did return MULTI_USER. What is going on?

  • CHECKDB (or something ran by CHECKDB) said the database was in DBO-ONLY (aka RESTRICTED_USER) mode.
  • SYS.DATABASES said the database was in SINGLE_USER mode.
  • DATABASEPROPERTYEX said the database was in MULTI_USER mode.
  • The database was actually in SINGLE_USER mode.

image

Anyway, back to the topic. There was one more thing to note:

select * from accident..critical

image

Funny that I’d used REPAIR_ALLOW_DATA_LOSS, and instead of losing data, I’d got more data. Smile

Recap:

  1. Shut down SQL Server.
  2. Delete transaction log file.
  3. Start SQL Server.
  4. Set database to emergency mode.
  5. Set database to single user mode.
  6. Run dbcc checkdb with repair_allow_data_loss.

Summary

I have proven above that a database with a missing or corrupt transaction log file can be recovered. However, depending on the database state before the removal or corruption of the log file and depending on the methods used during recovery, there will be different changes made to the database after it is brought back online.

  Database shutdown cleanly Recovery model after database is back online User access after database is back online
1. Take database offline Yes Full -> Simple MULTI_USER -> MULTI_USER
2. Detach database Yes Full -> Simple MULTI_USER -> MULTI_USER
3. Shut down SQL Server No Full -> Simple MULTI_USER -> SINGLE_USER

Other side effects
When a database is detached, all its metadata is dropped. If the database was the default database of any login accounts, master becomes their default database.
Transactional consistency has been lost.

–oo00oo–

For more info on CHECKDB and EMERGENCY mode, please refer to the following blog posts:
TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database
Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database
CHECKDB From Every Angle: EMERGENCY mode repair – the very, very last resort