{"id":250,"date":"2009-06-09T23:45:52","date_gmt":"2009-06-09T21:45:52","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=250"},"modified":"2009-06-09T23:45:52","modified_gmt":"2009-06-09T21:45:52","slug":"deleting-the-transaction-log","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2009\/06\/09\/deleting-the-transaction-log\/","title":{"rendered":"Deleting the transaction log"},"content":{"rendered":"

Or “Why is my database now inaccessible?<\/em>”<\/p>\n

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.<\/p>\n

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.<\/p>\n

The transaction log has a number of uses within SQL.<\/p>\n

    \n
  1. 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.<\/li>\n
  2. A transaction rollback uses the transaction log to determine what needs to be undone.<\/li>\n
  3. In transactional replication, the log reader uses the transaction log to determine what changes need to be replicated<\/li>\n
  4. Used by Change Data Capture in SQL 2008 to extract changes made to registered tables<\/li>\n
  5. 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.<\/li>\n<\/ol>\n

    For now, I’m just going to consider the last one.<\/p>\n

    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?<\/p>\n

    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.<\/p>\n

    [source:sql]CREATE DATABASE TestingLogDeletion
    \nON (NAME = Test_dat,
    \nFILENAME = ‘D:\\Develop\\Databases\\TestingLogDeletion.mdf’,
    \nSIZE = 10,
    \nMAXSIZE = UNLIMITED,
    \nFILEGROWTH = 5 )
    \nLOG ON ( NAME = Test_log,
    \nFILENAME = ‘D:\\Develop\\Databases\\TestingLogDeletion.ldf’,
    \nSIZE = 5MB,
    \nMAXSIZE = 10MB);
    \nGO<\/p>\n

    ALTER DATABASE TestingLogDeletion SET RECOVERY FULL;
    \nGO<\/p>\n

    USE TestingLogDeletion
    \nGO<\/p>\n

    CREATE TABLE Filler (
    \nID INT IDENTITY PRIMARY KEY,
    \nFillerStr CHAR(300)
    \n)
    \nGO<\/p>\n

    BACKUP DATABASE TestingLogDeletion TO DISK = ‘D:\\Develop\\Databases\\Backups\\TestingLogDeletion.bak'[\/source]<\/p>\n

    Right, so that’s the setup all done. Now to fill the log up.<\/p>\n

    [source:sql]DECLARE @i INT;
    \nSET @i = 0;<\/p>\n

    WHILE (@i < 10000)
    \nBEGIN
    \nINSERT INTO Filler (FillerStr) VALUES (CAST(@i AS VARCHAR(10)))
    \nSET @i = @i+1;
    \nEND
    \nGO[\/source]<\/p>\n

    Doesn’t take long either. After just 4778 inserts, this popped up.<\/p>\n

    Msg 9002, Level 17, State 2, Line 6
    \nThe 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<\/span><\/p>\n

    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.<\/p>\n

    Once SQL restarts, I’m going to query that table, see what’s in there.<\/p>\n

    [source:sql]select MAX(ID) from TestingLogDeletion.dbo.Filler[\/source]<\/p>\n

    Msg 945, Level 14, State 2, Line 1
    \nDatabase ‘TestingLogDeletion’ cannot be opened due to inaccessible files or insufficient memory or disk space.\u00a0 See the SQL Server errorlog for details.<\/span><\/p>\n

    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<\/p>\n

    Error: 17207, Severity: 16, State: 1.<\/p>\n

    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.<\/p>\n

    File activation failure. The physical file name “D:\\Develop\\Databases\\TestingLogDeletion.ldf” may be incorrect.<\/p>\n

    The log cannot be rebuilt because the database was not cleanly shut down.<\/p><\/blockquote>\n

    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.<\/p>\n

    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<\/a><\/p>\n

    Step 1 – Set the database into Emergency mode<\/strong><\/p>\n

    Fortunately in 2005 and later, using Emergency mode is documented and supported and anyone with sysadmin permissions can set a database into Emergency mode.<\/p>\n

    [source:sql]ALTER DATABASE TestingLogDeletion SET EMERGENCY[\/source]<\/p>\n

    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.<\/p>\n

    \"\"<\/p>\n

    I’ve lost almost 3300 records. Imagine if this was a sales system, or stock market trading or something similar.<\/p>\n

    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.<\/p>\n

    Step 2 – Emergency mode repair<\/strong><\/p>\n

    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.<\/p>\n

    [source:sql]ALTER DATABASE TestingLogDeletion SET SINGLE_USER
    \nDBCC CHECKDB(TestingLogDeletion, REPAIR_ALLOW_DATA_LOSS)[\/source]<\/p>\n

    CheckDB complained about the missing log then went and rebuilt the log with a whole bunch of extra warnings<\/p>\n

    File activation failure. The physical file name “D:\\Develop\\Databases\\TestingLogDeletion.ldf” may be incorrect.<\/p>\n

    The log cannot be rebuilt because the database was not cleanly shut down.<\/p>\n

    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.<\/p>\n

    DBCC results for ‘TestingLogDeletion’.
    \nCHECKDB found 0 allocation errors and 0 consistency errors in database ‘TestingLogDeletion’.<\/p><\/blockquote>\n

    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<\/p>\n

    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.<\/p>\n

    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.<\/p>\n

    Same setup, but instead of shutting SQL down, I’ll detach the database.<\/p>\n

    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.<\/p>\n

    So, DB detached. Now I’m going to delete the log and use the CREATE DATABASE syntax to reattach it.<\/p>\n

    [source:sql]CREATE DATABASE TestingLogDeletion
    \nON (FILENAME = ‘D:\\Develop\\Databases\\TestingLogDeletion.mdf’)
    \nFOR ATTACH_REBUILD_LOG [\/source]<\/p>\n

    That should work fine, right. Right?<\/p>\n

    Wrong.<\/p>\n

    File activation failure. The physical file name “D:\\Develop\\Databases\\TestingLogDeletion.ldf” may be incorrect.
    \nThe log cannot be rebuilt because the database was not cleanly shut down.
    \nMsg 1813, Level 16, State 2, Line 1
    \nCould not open new database ‘TestingLogDeletion’. CREATE DATABASE is aborted.<\/span><\/p><\/blockquote>\n

    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.<\/p>\n

    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.<\/p>\n

    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.<\/p>\n

    [source:sql]CREATE DATABASE TestingLogDeletion_2
    \nON (NAME = Test_dat,
    \nFILENAME = ‘D:\\Develop\\Databases\\TestingLogDeletion_2.mdf’,
    \nSIZE = 10,
    \nMAXSIZE = UNLIMITED,
    \nFILEGROWTH = 5 )
    \nLOG ON ( NAME = Test_log,
    \nFILENAME = ‘D:\\Develop\\Databases\\TestingLogDeletion_2.ldf’,
    \nSIZE = 5MB,
    \nMAXSIZE = 5MB);
    \nGO[\/source]<\/p>\n

    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.<\/p>\n

    So, in conclusion. Don’t delete the transaction log. Sometimes SQL can rebuild it without too many problems, sometimes it can’t.<\/p>\n","protected":false},"excerpt":{"rendered":"

    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… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[20,22,15,16],"tags":[],"class_list":["post-250","post","type-post","status-publish","format-standard","hentry","category-admin","category-corruption","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-42","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/250","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=250"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/250\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=250"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=250"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=250"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}