The Transaction Log for the Database Is Full

The transaction log for the database is full

Is this a one time script, or regularly occurring job?

In the past, for special projects that temporarily require lots of space for the log file, I created a second log file and made it huge. Once the project is complete we then removed the extra log file.

How do you clear the SQL Server transaction log?

Making a log file smaller should really be reserved for scenarios where it encountered unexpected growth which you do not expect to happen again. If the log file will grow to the same size again, not very much is accomplished by shrinking it temporarily. Now, depending on the recovery goals of your database, these are the actions you should take.

First, take a full backup

Never make any changes to your database without ensuring you can restore it should something go wrong.

If you care about point-in-time recovery

(And by point-in-time recovery, I mean you care about being able to restore to anything other than a full or differential backup.)

Presumably your database is in FULL recovery mode. If not, then make sure it is:

ALTER DATABASE testdb SET RECOVERY FULL;

Even if you are taking regular full backups, the log file will grow and grow until you perform a log backup - this is for your protection, not to needlessly eat away at your disk space. You should be performing these log backups quite frequently, according to your recovery objectives. For example, if you have a business rule that states you can afford to lose no more than 15 minutes of data in the event of a disaster, you should have a job that backs up the log every 15 minutes. Here is a script that will generate timestamped file names based on the current time (but you can also do this with maintenance plans etc., just don't choose any of the shrink options in maintenance plans, they're awful).

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\testdb_' 
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

Note that \\backup_share\ should be on a different machine that represents a different underlying storage device. Backing these up to the same machine (or to a different machine that uses the same underlying disks, or a different VM that's on the same physical host) does not really help you, since if the machine blows up, you've lost your database and its backups. Depending on your network infrastructure it may make more sense to backup locally and then transfer them to a different location behind the scenes; in either case, you want to get them off the primary database machine as quickly as possible.

Now, once you have regular log backups running, it should be reasonable to shrink the log file to something more reasonable than whatever it's blown up to now. This does not mean running SHRINKFILE over and over again until the log file is 1 MB - even if you are backing up the log frequently, it still needs to accommodate the sum of any concurrent transactions that can occur. Log file autogrow events are expensive, since SQL Server has to zero out the files (unlike data files when instant file initialization is enabled), and user transactions have to wait while this happens. You want to do this grow-shrink-grow-shrink routine as little as possible, and you certainly don't want to make your users pay for it.

Note that you may need to back up the log twice before a shrink is possible (thanks Robert).

So, you need to come up with a practical size for your log file. Nobody here can tell you what that is without knowing a lot more about your system, but if you've been frequently shrinking the log file and it has been growing again, a good watermark is probably 10-50% higher than the largest it's been. Let's say that comes to 200 MB, and you want any subsequent autogrowth events to be 50 MB, then you can adjust the log file size this way:

USE [master];
GO
ALTER DATABASE Test1 
  MODIFY FILE
  (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

Note that if the log file is currently > 200 MB, you may need to run this first:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

If you don't care about point-in-time recovery

If this is a test database, and you don't care about point-in-time recovery, then you should make sure that your database is in SIMPLE recovery mode.

ALTER DATABASE testdb SET RECOVERY SIMPLE;

Putting the database in SIMPLE recovery mode will make sure that SQL Server re-uses portions of the log file (essentially phasing out inactive transactions) instead of growing to keep a record of all transactions (like FULL recovery does until you back up the log). CHECKPOINT events will help control the log and make sure that it doesn't need to grow unless you generate a lot of t-log activity between CHECKPOINTs.

Next, you should make absolute sure that this log growth was truly due to an abnormal event (say, an annual spring cleaning or rebuilding your biggest indexes), and not due to normal, everyday usage. If you shrink the log file to a ridiculously small size, and SQL Server just has to grow it again to accommodate your normal activity, what did you gain? Were you able to make use of that disk space you freed up only temporarily? If you need an immediate fix, then you can run the following:

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO

Otherwise, set an appropriate size and growth rate. As per the example in the point-in-time recovery case, you can use the same code and logic to determine what file size is appropriate and set reasonable autogrowth parameters.

Some things you don't want to do

• Back up the log with TRUNCATE_ONLY option and then SHRINKFILE. For one, this TRUNCATE_ONLY option has been deprecated and is no longer available in current versions of SQL Server. Second, if you are in FULL recovery model, this will destroy your log chain and require a new, full backup.

• Detach the database, delete the log file, and re-attach. I can't emphasize how dangerous this can be. Your database may not come back up, it may come up as suspect, you may have to revert to a backup (if you have one), etc. etc.

• Use the "shrink database" option. DBCC SHRINKDATABASE and the maintenance plan option to do the same are bad ideas, especially if you really only need to resolve a log problem issue. Target the file you want to adjust and adjust it independently, using DBCC SHRINKFILE or ALTER DATABASE ... MODIFY FILE (examples above).

• Shrink the log file to 1 MB. This looks tempting because, hey, SQL Server will let me do it in certain scenarios, and look at all the space it frees! Unless your database is read only (and it is, you should mark it as such using ALTER DATABASE), this will absolutely just lead to many unnecessary growth events, as the log has to accommodate current transactions regardless of the recovery model. What is the point of freeing up that space temporarily, just so SQL Server can take it back slowly and painfully?

• Create a second log file. This will provide temporarily relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid. You should deal with the problematic log file directly instead of just adding another potential problem. Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you (unlike a second data file), since only one of the files can ever be used at a time. Paul Randal also explains why multiple log files can bite you later.

Be proactive

Instead of shrinking your log file to some small amount and letting it constantly autogrow at a small rate on its own, set it to some reasonably large size (one that will accommodate the sum of your largest set of concurrent transactions) and set a reasonable autogrow setting as a fallback, so that it doesn't have to grow multiple times to satisfy single transactions and so that it will be relatively rare for it to ever have to grow during normal business operations.

The worst possible settings here are 1 MB growth or 10% growth. Funny enough, these are the defaults for SQL Server (which I've complained about and asked for changes to no avail) - 1 MB for data files, and 10% for log files. The former is much too small in this day and age, and the latter leads to longer and longer events every time (say, your log file is 500 MB, first growth is 50 MB, next growth is 55 MB, next growth is 60.5 MB, etc. etc. - and on slow I/O, believe me, you will really notice this curve).

Further reading

Please don't stop here; while much of the advice you see out there about shrinking log files is inherently bad and even potentially disastrous, there are some people who care more about data integrity than freeing up disk space.

A blog post I wrote in 2009, when I saw a few "here's how to shrink the log file" posts spring up.

A blog post Brent Ozar wrote four years ago, pointing to multiple resources, in response to a SQL Server Magazine article that should not have been published.

A blog post by Paul Randal explaining why t-log maintenance is important and why you shouldn't shrink your data files, either.

Mike Walsh has a great answer covering some of these aspects too, including reasons why you might not be able to shrink your log file immediately.

The transaction log for database is full due to 'LOG_BACKUP' in a shared host

Call your hosting company and either have them set up regular log backups or set the recovery model to simple. I'm sure you know what informs the choice, but I'll be explicit anyway. Set the recovery model to full if you need the ability to restore to an arbitrary point in time. Either way the database is misconfigured as is.

Transaction log is full (due to NOTHING)... but this database is in simple recovery mode

Got it, help received from stackexchange.

https://dba.stackexchange.com/questions/241172/transaction-log-is-full-due-to-nothing-but-this-database-is-in-simple-recov?noredirect=1#comment475763_241172

Autogrowth was set to 0. Unfortunately there's no way to see this in SSMS because it hides such settings about recovery-mode-simple DBs.

Query to see the real value of Autogrowth, thanks to @HandyD:

SELECT 
    db.name AS [Database],
    mf.name AS [File],
    CASE mf.[type_desc]
        WHEN 'ROWS' THEN 'Data File'
        WHEN 'LOG' THEN 'Log File'
    END AS [FileType],
    CAST(mf.[size] AS BIGINT)*8/1024 AS [SizeMB],
    CASE
        WHEN mf.[max_size] = -1 THEN 'Unlimited'
        WHEN mf.[max_size] = 268435456 THEN 'Unlimited'
        ELSE CAST(mf.[max_size]*8/1024 AS NVARCHAR(25)) + ' MB'
    END AS [MaxSize],
    CASE [is_percent_growth]
        WHEN 0 THEN CONVERT(VARCHAR(6), CAST(mf.growth*8/1024 AS BIGINT)) + ' MB'
        WHEN 1 THEN CONVERT(VARCHAR(6), CAST(mf.growth AS BIGINT)) + '%'
    END AS [GrowthIncrement]
FROM sys.databases db
LEFT JOIN sys.master_files mf ON mf.database_id = db.database_id
where mf.name like 'aspnetdb%'

The other problem is that, in this state you can't change autogrowth. But you can alter size. So by increasing size and then introducing autogrowth, you can fix the problem.

ALTER DATABASE aspnetdb MODIFY FILE (
    NAME = aspnetdb_log
    , SIZE = 1GB
) --this fixes the problem
GO
ALTER DATABASE aspnetdb MODIFY FILE (
    NAME = aspnetdb_log
    , SIZE = 1025MB
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 10MB
) -- now we have autogrowth
GO
USE aspnetdb
DBCC SHRINKFILE(aspnetdb_log,1) --now we can shrink the DB back to a sane minimum since autogrowth is in place
GO

The transaction log for database 'test' is full due to 'LOG_BACKUP'. and free space issue with C Drive

As per the comments, I have shrunk the transaction log file, it freed up around 13GB. Since the recovery model of my database is Full, first I have taken a backup of transaction logs and then change the recovery model of my database to Simple. After that, I have shrunk my log files and again set back to Full.

 ALTER DATABASE AdventureWorks2012
    SET RECOVERY SIMPLE
    GO
    DBCC SHRINKFILE (AdventureWorks2012_log, 1)
    GO
    ALTER DATABASE AdventureWorks2012
    SET RECOVERY FULL

https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/

Thanks, @sepupic, for the very accurate approach you have mentioned in the post. After cleaned up space, I have deleted the records in small batches as per the comments of @Pedro Martins.

SQL server- Transaction log for database is full

You will need to check the Recovery mode of your database. Put it in Full Recovery mode. After that, make sure there is a transaction log backup in place for your database. You will need to dig through it and make a Maintenance plan, depending upon how critical your data is. That will be the long term solution.

For time being you can shrink your log files using following DBCC command -

BACKUP LOG  DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE (  DBNameLog, 1)

Or you can do it through Object Explorer. Refer to this link for details. But you will have to set your Database to Simple Recovery model to use the Shrink command

MSSQL Recurring Transaction Log full. Need to know who causes it

An hourly transaction log backup means in case of a disaster you could lose up to an hour's worth of data.

It is usually advised to keep you transaction log backups as frequent as possible.

Every 15 mins is usually a good starting point. But if it is a business critical database consider a transaction log backup every minute.

Also why would you limit the size for your transaction log file? If you have more space available on the disk, allow your file to grow if it needs to grow.

It is possible that the transaction log file is getting full because there is some maintenance task running (Index/Statistics maintenance etc) and because the log file is not backed up for an entire hour, the logs doesn't get truncated for and hour and the file reaches 5GB in size. Hence the error message.

Things I would do, to sort this out.

1. Remove the file size limit, or at least increase the limit to allow it to grow bigger than 5 GB.

2. Take transaction Log backups more frequently, maybe every 5 minutes.

3. Set the log file growth increment to at least 1 GB from 128MB (to reduce the number of VLFs)

4. Monitor closely what is running on the server when the log file gets full, it is very likely to be a maintenance task (or maybe a bad hung connection).

5. Instead of setting max limit on the log file size, setup some alerts to inform you when the log file is growing too much, this will allow you to investigate the issue without any interference or even potential downtime for the end users.

The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'

You can search all the temp objects with a simple SELECT * FROM tempdb..sysobjects WHERE name LIKE '%AllClasses%'
To fix it just run once:

BEGIN TRANSACTION
    DROP TABLE #AllClasses
COMMIT TRANSACTION

If you still cant delete it just check for zombie sessions with SELECT * FROM sys.dm_exec_sessions and kill it with KILL session_id.

---

Related Topics