Firstly a brief introduction to the SQL Server Transaction Log and a process called write ahead logging.
It is this file (or sometimes files) that guarantees transactional consistency within SQL Server. A committed transaction will always write all transaction log records relating to that transaction to the physical log file before returning a committed notification to the client.
- Transaction starts
- DML operations occur and data starts being changed in the buffer pool
- Transaction is committed
- Transaction log records are written to disk
- All as yet unwritten transaction log records in the log buffer are written to disk here and not just those relating to this transaction.
- Commit success notification sent to the client
- As some point thereafter, the checkpoint process copies the dirty data pages from the buffer pool to the physical data file(s).
- The chronology described here could be different. The data pages could be written to disk prior to the transaction committing. SQL Server always guarantees however, that the log records will be written to disk prior to the data pages.
- The occurrence of a checkpoint can be controlled by specifying the recovery interval at either the server or database level. Checkpoints can also be issued manually or internally by SQL Server.
- All dirty pages are written to disk by the checkpoint process and not just those pertaining to a specific transaction.
Next, we will have some fun and investigate what happens when a log file is deleted.
If I create a database like so
CREATE DATABASE LogDeleteTest ON PRIMARY (NAME = N'LogDeleteTest_Data', FILENAME = 'C:\SQL\LogDeleteTest_Data.mdf', SIZE = 10 MB,FILEGROWTH=10 MB, MAXSIZE = UNLIMITED) LOG ON (NAME = N'LogDeleteTest_Log', FILENAME = 'C:\SQL\LogDeleteTest_Log.ldf', SIZE = 10 MB,FILEGROWTH=10 MB, MAXSIZE = UNLIMITED);
Simply deleting the log file in windows explorer is not possible and gives this error
If I take the database offline however, by issuing
ALTER DATABASE LogDeleteTest SET OFFLINE;
Then the delete works just fine. Bringing the database online
ALTER DATABASE LogDeleteTest SET ONLINE;
Then gives this: –
File activation failure. The physical file name “C:\SQL\LogDeleteTest_log.ldf” may be incorrect.
New log file ‘C:\SQL\LogDeleteTest_log.ldf’ was created.
The log file has been automatically created at C:\SQL\LogDeleteTest_log.ldf and the database is online and available.
However, we shut the database down cleanly prior to deleting the log file. This means a checkpoint occurred and all uncommitted transactions were rolled back and all committed were hardened to disk. This means the transaction log was rebuilt with no risk of data loss.
The fun happens when the database is not shut down cleanly prior to deleting the log file. Do this by performing an DDL or DML action to create a log record, stopping the SQL Server service on a test box and delete the log file as before. This then results in
We then have to make use of emergency mode like so
USE master; ALTER DATABASE LogDeleteTest SET SINGLE_USER; ALTER DATABASE LogDeleteTest SET EMERGENCY; DBCC CHECKDB (N'LogDeleteTest',REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS; ALTER DATABASE LogDeleteTest SET MULTI_USER;
This is in the output, the log file has been created and the database is online and available
File activation failure. The physical file name “C:\SQL\LogDeleteTest_Log.ldf” may be incorrect.
Warning: The log for database ‘LogDeleteTest’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
If you consider what happened here, this is a really bad situation for the database to now be in. At the start while talking about write ahead logging, we described how SQL Server relies on the transaction log to roll back uncommitted transactions and roll forward committed transactions. This ensures data page updates for a transaction that was never committed are removed, and data page updates that were not previously performed for transactions that were committed are now done. Without the transaction log, this process has been completely omitted, meaning our database could now very well be in a transactionally inconsistent state where partially performed transactions are now within the database. At least your database is now up and available however if that was your primary goal.
Using emergency mode should be a last resort. You should always have a robust, documented disaster recovery solution in place to recover from such situations by other means with less exposure to data loss. If your last good log backup was 30 minutes before the log file was deleted however, you would have to make a judgement call. Either restore from backup and lose 30 minutes of data or use emergency mode and your last checkpoint could have only been 10 seconds before the log file was dropped. Maybe an option here would be to use both methods and then resolve manually by comparing the two. Always keep a copy of the files prior to taking any action, so you can revert to that if you make things worse.