MS SQL Full Disk, transaction log does not shrink

Posted on 2009-04-28
Last Modified: 2012-06-27

I've a very strange problem.

The situation on monday morning:
Database-server with database-file of +/- 48 GB. Transaction log is +/- 38 GB. Recovery mode is full, nightly backup.
One of the tables includes 7 million records.

I wanted to delete 5 million records to clean up some disk space and make the database faster again.

After two hours of running the delete-query, the server returned the error "transactionlog is full" because disk is full. De transactionlog was grown to more than 45 GB.
The database turned itself into "in recovery" modus. After some minutes, the database was online again, but disk is still full.
I've done a shrink of the database but nothing happens! (transactionlog still too large).
After that, I've done a backup because I've read the transaction log will become smaller after a backup.
No result, still big transaction log and database.

I've shrinked another database on the server, which gave me 5GB workspace on the disk again. Now I've deleted some 100.000 records per 5.000 records, with a shrink between every +/- 100.000 records. Result: database is some 100MB's smaller, but transaction log keeps big (or even larger).
After some 100.000 records, the transaction log explodes again and disk is full again.


- Why doesn't shrink the transaction log after a "shrink" or "backup"?
- How do I delete my 5 million of records without any problem? (to make disk space again free)
- Is it a good idea to convert the recovery model from "full" to "simple"? Will the transactionlog of 43GB be deleted, or be a lot smaller?
- There are a lot of indexes on the table. Is het required to rebuild indexes before transaction log will become smaller?
- ...

Any advice will be appriciated!

Thanks a lot!

Question by:corcon
    LVL 12

    Assisted Solution

    Whenever I've had a transaction log that will not shrink with either a shrink or backup I find that this combination nearly always shrinks the log to its smallest size. Just beware that if you require the log entries you have a back up before doing this:

    use <dbname> 
    backup log <dbname> with no_log 
    dbcc shrinkfile (...) 
    backup log <dbname> with no_log 

    Open in new window

    LVL 12

    Assisted Solution

    You should realise that when the transaction log is full, you can't perform any more transactions because the DB will try to log what you're doing but it's got no more space.
    You can handle this without deleting data first.
    Change the database recovery mode from FULL to simple, the do a "backup log [dbname] with truncate_only" (without quotes).
    Then run a "DBCC SHRINKFILE(filename, 1);" where filename is the name of your log file. (Use sp_helpfile" to get the file names.
    This will free the space of the log and you will be able to work again.
    LVL 4

    Assisted Solution

    We had this problem some while a go. After changing the log to simple and doing a shrink the size became normal again. You can change it back to full after that if needed.

    Assisted Solution

    You need to truncate the transaction log
    Run the following the code

    BACKUP LOG xxx_DatabaseName_xxx WITH TRUNCATE_ONLY
    LVL 31

    Assisted Solution


    Accepted Solution

    Thanks to all.

    I've changed my database to simple and shrinked the log file, which is now 1MB. Cool!

    Now I can delete my records.

    I will assign the points to you all.

    LVL 31

    Expert Comment

    yes, you can delete but do keep watch on log file and delete your records in small chunk
    LVL 1

    Expert Comment

    I know this is a closed question but I had same issue and could not find a good solution. This one works Great and it works everytime. The log file will be shrunk to 1024k and by using the sp_helpfile it gives you a nice result in the result pane with. You could also put this into a stored procedure and setup an maintenance plan to run it at a given interval.

    name      fileid      filename      filegroup      size      maxsize      growth      usage

    use databasename
    exec sp_helpfile
    Backup LOG databasename with truncate_only
    dbcc shrinkfile (logfilename, 1)

    LVL 12

    Expert Comment

    Good to know you're happy.
    This is just to caution you: if you do a Backup LOG db_name with truncate_only;, make sure that you do a full backup of your database otherwise you will not be able to do a point-in-time recovery or use subsequent log files to go forward when recovering after a truncating your log files.

    A differential backup right after truncating your logs also suffices .

    Good luck.
    You may reduce the rate of growth of your log files by changing the recovery mode from Full to Bulk-logged just before doing either Integrity checks (checking data & index linkage) or before re-index & re-org exercises.  Remember to change the recovery model back to full right after these maintenance exercises.

    By changing the recovery model to bulk-logged, the server will ignore logging bulky operations in the logs, hence reducing the growth of log files greatly during reindex/reorg/integrity check exercises.

    All the best & regards,

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now