I will continue in this part, detailing the case of a database failure with SUSPECT status when the Log file is corrupted or deleted. As I mentioned in part 2, you can easily find out why your database can’t be successfully recovered and is marked with SUSPECT status from the SQL Server Log file. The error will point to the exact problem – whether data file (.mdf) or log file (.ldf) is inaccessible. In part 2, I described the disaster recovery method if you lost your data file (.mdf). In that case you absolutely needed at least a Full backup to recover the database for all types of recovery models (SIMPLE, FULL, BULK-LOGGED).
Things are different if you lost only the Log file. If you have a Full backup and one/more Log backups than the recovery process for databases set in Simple/Full/Bulk-Logged recovery model is the same as in part 2 except that for Full/Bulk-Logged models you can’t backup the tail of the log it being corrupted or deleted.
If you don’t have a Full backup and your Log file can’t be opened but the Data file is intact, then – good news… you can bring the database online for users with minimal loss of data. The main idea is that you can only recover the database to a partially consistent state – having lost every transaction from the Log File which where not written to disk (after a Checkpoint) before the disaster.
So let’s have some fun recovering a SUSPECT database with a corrupted Log file.
-- Create database CREATE DATABASE [recovery_test_2] -- Create Test tables CREATE TABLE Products (Product nvarchar(100), Price INT); GO CREATE TABLE Invoices ([Id] INT IDENTITY, Product nvarchar (100), Value INT); GO -- Make some transactions INSERT INTO Products VALUES ('T-shirt',200); INSERT INTO Products VALUES ('Boots', 150); INSERT INTO Products VALUES ('Skirt', 500); INSERT INTO Products VALUES ('Belt', 300); -- Make one uncommitted transaction written to Disk with forced Checkpoint. Begin transaction Update Test2 set price= 1000 where product= 'Boots' Checkpoint Go -- From another user session make one uncommitted transaction with no forced Checkpoint. Begin transaction INSERT INTO Invoices VALUES ('T-shirt', 200); INSERT INTO Invoices VALUES ('Skirt', 500); Go
I just updated the price of Boots and sold two products.
-- Simulate failure SHUTDOWN WITH NOWAIT; GO -- Physically Rename or Delete LOG File of recovery_test_2 database -- Re-start SQL Server -- Check database status SELECT DATABASEPROPERTYEX ('recovery_test_2' , 'STATUS') AS 'Status'; Status -------- SUSPECT -- Try to re-open the database ALTER DATABASE recovery_test_2 SET ONLINE Error: File activation failure. The physical file name "C:Program Files Microsoft SQL ServerMSSQL10.TESTMSSQLDATArecovery_test_2_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 945, Level 14, State 2, Line 1 Database 'recovery_test_2' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. ALTER DATABASE statement failed.
As a GOLD rule is to never detach a SUSPECT database if you don’t have backups to recover from lately. In SQL 2008 there is a very wise restriction which prohibits to detach a SUSPECT database. In SQL 2005 and SQL 2000 however this is possible but strongly NOT RECOMMENDED . If you do this, even if your Log file is missing and is not actually corrupted – you will not be able to use the ATTACH_REBUILD_LOG option on CREATE DATABASE because SQL knows there where 2 active transactions and can’t create a new Log file.
You will have to fool SQL into believing that it is attached by creating a new dummy database (same database name, same file locations and sizes) –> bring database offline –> delete dummy’s database files –> replace them with the original Data Files (Log file is already missing) –> bring database online –> and obtain again the SUSPECT database’s status.
Now comes the fixing part.
The only way to access a SUSPECT database is to use EMERGENCY mode.
ALTER DATABASE recovery_test_2 SET EMERGENCY; GO ALTER DATABASE recovery_test_2 SET SINGLE_USER; GO DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS; GO Result: File activation failure. The physical file name "C:Program Files Microsoft SQL ServerMSSQL10.TESTMSSQLDATArecovery_test_2_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. Warning: The log for database 'recovery_test_2' 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. SELECT DATABASEPROPERTYEX ('recovery_test_2' , 'STATUS') AS 'Status'; Status -------- ONLINE
Now let’s take a look at Products and Invoices tables:
-- Set database with multi-user access ALTER DATABASE recovery_test_2 SET MULTI_USER; select * from Products Product Price -------------------- T-shirt 200 1000 150 Boots 1000 Belt 300 select * from Invoices ID Product Value ---------------------------- (0 row(s) affected)
It can be noticed that ‘Boots’ product has the price changed to 1000, while the transaction was not committed before the crash but it was however written to disk. It means that SQL had no information stored in the newly created Log file about the transaction executed before the crash in order to decide whether to Rollback or to Roll-forward. So, transactions that where written to disk (due to self manageable SQL process known as Checkpoint) but not committed must be manually identified and rolled forward or rollbacked by the functional teams. It is a risk that must be considered while recovering from disaster with a lost Log file using the described method. Sometimes this is the only option you have.
On the other hand the insert before the crash into Invoices table that was not written to disk and not committed, can’t be found in the table after recovery. It was contained only in the corrupted Log file. The recovery process without backups has good parts and bad parts. This is the reason, why Full, Differential and Log backups are a mandatory step to do in the big chain of administrative tasks.