Or “Why is my database now inaccessible?”
This is another things that I’ve seen a few things recently on various forums. A DB’s transaction log fills the disk, probably due to a lack of log backups, and then either SQL is stopped and the log file deleted or the database is detached and the log file deleted. Either way it’s not a particularly good thing to do.
No, I’ll be blunt, it’s a appallingly bad thing to do. The transaction log is not an optional piece of the database. It’s not like an installation log or activity log where the entries are there just in case anyone’s interested. The transaction log is what SQL uses to ensure, at all times, that the data in the database is transactionally consistent. It’s what gives SQL databases the consistency and durability properties that are required from a relational database engine.
The transaction log has a number of uses within SQL.
- In SQL 2000 and before, the inserted and deleted tables were materialised from the transaction log. This was changed in SQL 2005 and they’re now materialised from the row version store in TempDB.
- A transaction rollback uses the transaction log to determine what needs to be undone.
- In transactional replication, the log reader uses the transaction log to determine what changes need to be replicated
- Used by Change Data Capture in SQL 2008 to extract changes made to registered tables
- During restart-recovery to ensure that transactions that had committed when the service stopped but whose changes had not been written to the data file are replayed and to ensure that transactions that hadn’t completed are rolled back.
For now, I’m just going to consider the last one.
When the SQL Service is stopped or a database detached, SQL will try to shut the database down cleanly. That means running a checkpoint and writing all dirty data pages to disk. The checkpoint has to write into the log that it has run. If there’s no more space in the log, then the checkpoint cannot run and the database cannot be cleanly shut down. This is not usually a problem. When the database is attached or the service restarted SQL will run restart-recovery on it, the transaction log will be used to bring the database back to a transactionally consistent state. That’s great, but what happens if the transaction log’s not there?
To see exactly what will happen, I’m going to set up a database that’s got a very small max size for the log file (because I don’t want to have to fill the drive to get results), set it to full recovery and take a backup. Taking a backup will start the log chain and ensure that the log will grow until it runs out of space. Reuse of the log will not happen as I’m not taking any log backups. Once the database is backed up, I’m going to run inserts until the log fills up.
[source:sql]CREATE DATABASE TestingLogDeletion
ON (NAME = Test_dat,
FILENAME = ‘D:\Develop\Databases\TestingLogDeletion.mdf’,
SIZE = 10,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5 )
LOG ON ( NAME = Test_log,
FILENAME = ‘D:\Develop\Databases\TestingLogDeletion.ldf’,
SIZE = 5MB,
MAXSIZE = 10MB);
GO
ALTER DATABASE TestingLogDeletion SET RECOVERY FULL;
GO
USE TestingLogDeletion
GO
CREATE TABLE Filler (
ID INT IDENTITY PRIMARY KEY,
FillerStr CHAR(300)
)
GO
BACKUP DATABASE TestingLogDeletion TO DISK = ‘D:\Develop\Databases\Backups\TestingLogDeletion.bak'[/source]
Right, so that’s the setup all done. Now to fill the log up.
[source:sql]DECLARE @i INT;
SET @i = 0;
WHILE (@i < 10000)
BEGIN
INSERT INTO Filler (FillerStr) VALUES (CAST(@i AS VARCHAR(10)))
SET @i = @i+1;
END
GO[/source]
Doesn’t take long either. After just 4778 inserts, this popped up.
Msg 9002, Level 17, State 2, Line 6
The transaction log for database ‘TestingLogDeletion’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Trying to run a checkpoint gives the same error. So there’s not enough space in the log to do a clean shutdown. First I’m going to try shutting SQL down, deleting the log and then restarting SQL. See how the database handles that. Note, the highest identity value in that table was 4778 before the shutdown.
Once SQL restarts, I’m going to query that table, see what’s in there.
[source:sql]select MAX(ID) from TestingLogDeletion.dbo.Filler[/source]
Msg 945, Level 14, State 2, Line 1
Database ‘TestingLogDeletion’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
That’s not good. The state of the database, according to sys.databases, is RECOVERY_PENDING. Restart recovery has not run and cannot run because the log file’s missing. The error log says the following
Error: 17207, Severity: 16, State: 1.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘D:\Develop\Databases\TestingLogDeletion.ldf’. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name “D:\Develop\Databases\TestingLogDeletion.ldf” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
There are two ways to fix this. Restore from backup or attempt a log rebuild and repair. If there is a current database backup, the first option is by far the best one. The repair should be a last resort, for when there is no backup.
In this case, for demonstration purposes, I’m going to pretend there’s no backup and go for a repair. This technique is given in more detail on Paul Randal’s blog – Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database
Step 1 – Set the database into Emergency mode
Fortunately in 2005 and later, using Emergency mode is documented and supported and anyone with sysadmin permissions can set a database into Emergency mode.
[source:sql]ALTER DATABASE TestingLogDeletion SET EMERGENCY[/source]
Now that the DB is in Emergency mode I can access it again. So what’s in that table that I was populating? Remember that the highest identity value before the shutdown and log deletion was 4778.
I’ve lost almost 3300 records. Imagine if this was a sales system, or stock market trading or something similar.
Because the DB was not shut down cleanly, all the changed data pages were not written to disk. Normally those changes would be replayed from the transaction log during the restart-recovery but because the log was deleted and restart-recovery can’t run, those changes have been lost, and there is no way to get them back.
Step 2 – Emergency mode repair
To bring the DB back online I need to run a repair. This will fix any structural problems caused by the missing log and bring the database online.
[source:sql]ALTER DATABASE TestingLogDeletion SET SINGLE_USER
DBCC CHECKDB(TestingLogDeletion, REPAIR_ALLOW_DATA_LOSS)[/source]
CheckDB complained about the missing log then went and rebuilt the log with a whole bunch of extra warnings
File activation failure. The physical file name “D:\Develop\Databases\TestingLogDeletion.ldf” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Warning: The log for database ‘TestingLogDeletion’ 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.
DBCC results for ‘TestingLogDeletion’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘TestingLogDeletion’.
In this case there was no corruption. That will not always be the case. Because recovery acts on both user data and the DB’s structure there may be physical corruption that has to be repaired. It’s even possible that there’s irreparable corruption to system tables or allocation pages and if Emergency mode repair fails there’s no other way to bring the DB back online
After running repair if I check sys.databases the state of the database is Online, so I can bring it out of restricted user mode and it’s usable again.
Now that was bad enough. Lost data and a database that was unavailable for however long CheckDB takes to repair, and on big databases that could be many hours. Let’s see how bad things are when I detach the database and then delete the log file.
Same setup, but instead of shutting SQL down, I’ll detach the database.
Interesting point, if I do the detach through Object Explorer the detach dialog comes back with an error saying that detach failed because the transaction log is full. However the detach actually succeeds, the database is not longer there.
So, DB detached. Now I’m going to delete the log and use the CREATE DATABASE syntax to reattach it.
[source:sql]CREATE DATABASE TestingLogDeletion
ON (FILENAME = ‘D:\Develop\Databases\TestingLogDeletion.mdf’)
FOR ATTACH_REBUILD_LOG [/source]
That should work fine, right. Right?
Wrong.
File activation failure. The physical file name “D:\Develop\Databases\TestingLogDeletion.ldf” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘TestingLogDeletion’. CREATE DATABASE is aborted.
Much the same as the error that we got when stopping SQL, deleting the log file and restarting SQL but with one very big difference. I can’t set the DB into emergency mode and run repair. The attach failed, the database is not there.
So this is a bit of a catch-22. I can’t rebuild the log because the database isn’t attached and I can’t attach because the log is missing. Nasty situation. Same as in the first case, the recommended approach here is to restore backups (and we all have backups, right?). If there are no backups, there is a way to hack the DB back into the server, but it isn’t pretty.
To get that DB back into SQL I need to create a new database with the same number of files and the same (as much as possible) size of files.
[source:sql]CREATE DATABASE TestingLogDeletion_2
ON (NAME = Test_dat,
FILENAME = ‘D:\Develop\Databases\TestingLogDeletion_2.mdf’,
SIZE = 10,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5 )
LOG ON ( NAME = Test_log,
FILENAME = ‘D:\Develop\Databases\TestingLogDeletion_2.ldf’,
SIZE = 5MB,
MAXSIZE = 5MB);
GO[/source]
Then stop SQL, delete both of the files of the newly created database and rename the mdf file of the database I detached earlier to match the mdf file of this DB. Once that’s done, restart SQL. When SQL starts it will find that the log file is missing and the DB not shut down cleanly and it will put the DB into the RECOVERY_PENDING state. From here the steps to get the DB back online are the same as in the first case. Set it into Emergency mode and run CheckDB with a repair option. Again there will be downtime while checkDB repairs and rebuilds the log, there may be some data loss and there’s always the chance that emergency mode repair will fail.
So, in conclusion. Don’t delete the transaction log. Sometimes SQL can rebuild it without too many problems, sometimes it can’t.
Thanks for pushing this point, Gail. Can’t be emphasized enough. I feel physically ill whenever I see somebody lose data, and deleting a log file is a good way to loose data. Or, as I like to think of it, playing Russian Roulette.
It is worth noting that damages can be very severe, in the sense that recovery not only manages user data, but also meta-data. Imagine some page allocation stuff (involving GAM pages, IAM pages and such) cannot be recovered to a conistent state. I would not want to try to make sese out of such a database. And DBCC repair would probably wipe out a very large portion out of such a database, if it would touch it at all.
Ohh Great…. Gail, Nice Explanation. Realy helpfull…
Thank you so much for sharing the Info.
Repair wouldn’t touch it at all. Repair cannot and will not fix corruption to the system tables or to any of the allocation pages.
Thanks for that, I’ll edit the post and add a warning about that.
Very good explanation Gali. Thanks for so valuable info.
Thanks for the very good article.
Pardon my n00bness, but what should we do then if the transaction log is full?
Depends on the scenario. If the log size is restricted, grow the file. If it’s filled the drive, either back it up (full recovery) or run a checkpoint (simple recovery). If those don’t help, query sys.databases to see why the transaction log space is not been reused.
Worst case, detach the database, move the log to a drive that does have space, reattach and then rectify the problem.
I wrote a bit about tran log maintenance on SQLServerCentral – http://www.sqlservercentral.com/articles/64582/
If the log has got to the state of filling the entire drive, either there’s been a massive, unusual amount of activity or regular maintenance (log backups) isn’t been done. It should never happen on a well maintained database.
Question? If you didn’t get the checkpoint (for whatever reason) and you restore the database from a backup, do you still have all the transactions from the active log?
Maybe. If the transaction committed before the backup completed, it might. It doesn’t have to do with checkpoint, the full backup backs just enough of the log up to ensure a consistent database on restore.
I’m not sure whether transactions that start after the backup starts and finish before it completes will be included or not.
There’s some posts on backups and what they include on Paul Randal’s blog. Maybe check that out.
http://www.sqlskills.com/blogs/paul
*experiment experiment* Ah, backing up the transaction log truncates it afterwards. Great, thanks.
Backing up the transaction log allows the log records that were included in the backup to be discarded and the space they occupied to be marked as reusable.
That happens when a checkpoint runs in Simple recovery model.
Linked to this post from SQLServerCentral and skimmed through it. I was too busy to really absorb it so I came back today and read it carefully. Much thanks for the clearly written content!
Very strang, I created the testinglogdeletion db in sql 2008 developer edition. The db is in full recovery mode. I then created the filler table and ran your code to populate the data but all I see is the mdf file gets bigger and bigger and log file size stays the same. I was trying to blow the log.
That’s because until you take a full database backup, the log is in auto-truncate mode, just as if the DB was in simple recovery model.
Take a full backup and try again.
You are truly the best! Thanks a million, your post helped us recover our database!
Now take some backups and leave the log file alone from now.
I will do that. Thanks for the advice. Now I am having a different problem.. now my .mdf file has grown almost five times its original size. What could have caused that?
Offhand, no idea.
can I shrink the database? or will I lose the data?
https://www.sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
So is it then realistic if you have a very bad log file situation that you are trying to correct (min size stuck at 30GB – cannot shrink below this point – a second log file resulting from attempt to migrate and Remove the 30GB log file – which doesnt work either btw) and you really do just want to start over with just the mdf and a reasonably defined ldf, to go through any series of steps that will not lead to the db crashing or long recovery process like is described?
I still wouldn’t delete the log file. Too much risk of the DB not coming back.
Set the DB to simple recovery. Check log usage once that’s done. If lots of the log is in use, check why (log_reuse_wait_desc in sys.databases). Fix whatever’s preventing log reuse.
Once the log is mostly empty, you can shrink and eventually remove the second file.
On SQL 2000 it could happen that the log file wouldn’t shrink if the active portion was at the end of the file. That’s fixed in SQL 2005+
Well I surely can keep the log around until the database can be brought back online, but inevitably the 30GB file using only a few GB has to go. The problem will be redefining the log file minimum size to a new, lower size. Shrinking is not going to shrink the file below the min size, or is it?
What I meant to clarify is that the log file min size is set to 30GB, but there is approx 99 pct free. It is a problem with the min size of the first log file, and then the problem of the additional log file. Matter of fact, if I could just migrate to the second file and remove the first I would be happy with that approach.
Mike, I suggest that you post this problem on a forum. SQLServerCentral, SQLTeam or the MSDN forums. A blog comments form is not a good place to help you with a problem.
I still would not detach and delete the ldf in this case.
very nice article Gail keep it up i have gone thru many articles of gail which are very help full and morly about the indexes.
Thanks for the advice. I got tired of mucking around with recovering the Log file.. but really, didn’t care. So after setting the database to EMERGENCY, I SSISed all the objects and data to a new database… backed it up , and deleted the old one.
Thanks heaps!!
Mike, you have to understand what the log file does and why before you attempt a solution.
—-Well I surely can keep the log around until the database can be brought back online, but inevitably the 30GB file using only a few GB has to go. The problem will be redefining the log file minimum size to a new, lower size. Shrinking is not going to shrink the file below the min size, or is it?—-
What are the negative impacts of deleting a transaction log if:
– The database is detached under normal circumstances (no errors, log not full, no other problems…)
– .MDF is moved to a new server
– .LDF is deleted for some reason
– Can the database be attached in the new location without the ‘ldf ? Without problems ?
Thanks
Maybe. It should but there’s no guarantee.
Pingback: On the 10th day of SQL… « Crys's Crap
Great Article to save DBAs in most worse situation
Gail, in the detached db situation, have you tried offlining\onlining the dummy database rather than a stop \start of SQL. If that got you to the same situation would be less intrusive on a multi database server
Not sure what you mean? Offline to replace the data and log files? Should work, haven’t tried
Good article …
Gail thanks a lot
Gail, We can do a single file db attach using sp_attach_single_file_db when the log file is deleted isnt it?
As I pointed out in this post, no, not always. If the database was not cleanly shut down, the attach will fail. See the second example in this post.
The sp_attach procs are deprecated, the replacement is the CREATE DATABASE … FOR ATTACH or CREATE DATABASE … FOR ATTACH_REBUILD_LOG that I used
Gail, great article! I always love reading your blog.
One question I do have though as I am trying to educate myself on Transaction Logs as much as I can, what is the best practice for managing Transaction logs in the Simple recovery mode? I use this on my reporting datawarehouse that supports SSAS Cubes and SSRS reporting. It is only loaded once a night at 2am in the morning and no modifications are made to it during the day so no point in time restores would be necessary. However during the night the transaction log does grow during the ETL process. I leave it alone as I am in no danger of running out of space and it is not that big, but none the less I wonder if I should be truncating it by backing it up? What is the best practice in this situation? I can’t seem to find much of that info on the net 😀
Short answer: You can’t back a log up if the DB is in simple recovery, and log truncation happens every time a checkpoint runs.
If you want to go into more detail, maybe post a question over at SQLServerCentral.com and you’ll get lots of good (and probably some not-so-good) advice.
Ok thanks Gail, will do!
Thanks again for this article, Gail. Came in handy for me tonight, dealing with a log file corrupted during a server move.
Big Thank You! Worked perfect! A little data loss, but way better than all lost!
Please note that restoring from backup should always be considered first and is a much safer way of getting the database back than rebuilding the log.
I had lost my ldf file and neither had the DB, nor the backup. The workaround/hack you mentioned here worked successfully from the recovery mode. Muchas gracias!
PS: On a sidenote, this is exactly why I hate closed/proprietary systems. Who in their frigging mind ties the workability/dependence of a complete database with just its transaction log!
‘Just’ the transaction log? The log is one of the more critical portions of the database because it is what ensures durability and consistency. Any database platform that supports durability has some form of transaction log
It is not an audit log or just record of what happened. It’s every bit as important (if not more) than the data files
Thanks for this post, it’s nice to have such a well researched and documented description.
Regarding “just” the transaction log, I laughed out loud on reading that. I spent quite a while working in MS support and found it both incredible and frustrating how many people would have the same attitude and be indignant that their deleting the log file “broke” their database. Like you, I would try to explain how integral the transaction log file was – it’s “half” of the database, not an insignificant file! – but was always fighting a losing battle with the entrenched views people already had. I would love to change the name of the transaction log file, my current choice is the “integrity” file. It would then be more obvious when someone says “I deleted my integrity file” exactly why their database was now inaccessible.
Anyway thanks for a quality blog, I always enjoy it.
@RJ
As for why you hate closed/proprietary systems, I encourage you to read about MySQL Transaction Logging.
Hi
I went through the steps to simulate this but I seem to be having a problem with the emergency mode repair.
Firstly, I stopped SQL (nowait) while there was an open transaction, deleted the LDF and started SQL. Obviously the database would not come online. I then detached the database, attempted a re-attach with attach_rebuild_log which obviously didn’t work. I then created a database with the same number of files etc etc…..
The problem comes in when I try to do the emergency mode repair. It says “Msg 922, Level 14, State 1, Line 1
Database ‘db’ is being recovered. Waiting until recovery is finished.”
Any Ideas?
I’ve had a similar thing with an open transaction and it was just one of the scenarios where the DB cannot go into emergency mode and hence can’t be fixed. Emergency mode is a last resort, it’s not guaranteed to work in all cases.
Thanks
what a fantastic information Gail. Every point is clear to me and I only expected this from you.