Sql-server – TempDB Transaction log not releasing space

checkpointsql serversql-server-2016tempdbtransaction-log

Every time I complete a transaction in tempdb, the temp log grows, however the log doesn't seem to release the space once the transaction has completed.The log usage percent is 43.4% but seems to increase in around 4% increments.

When I look for open transactions (sys.dm_exec_sessions), there are none. Yet the log_reuse_wait_desc in sys.databases shows 'ACTIVE_TRANSACTION'. When I query the sys.dm_tran_active_transactions DMV, it shows only work table.

I'm unsure of how I can release this space from the tempdb transaction log.

I've just run a manual checkpoint and that seems to free up the space so I'm not sure as to why the space isn't being freed up on it's own

Best Answer

The log_reuse_wait_desc is only updated when SQL Server attempts to clear the log. In other words, it's not necessarily a "live" view of whether the log can be cleared or not. Paul Randal explains that in his blog post "Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?".

In the case of tempdb, which uses the SIMPLE recovery model, SQL Server attempts to clear the log whenever a CHECKPOINT is issued. This is why doing a manual CHECKPOINT frees up the space, as you mentioned.

SQL Server is a lot less aggressive about doing automatic CHECKPOINTs on tempdb, both because it uses the SIMPLE recovery model, and because it is by definition "temporary" information. So there's no need to constantly persist it to disk. This is a performance optimization specific to tempdb.

Per another blog post by Paul Randal, the details are as follows:

automatic CHECKPOINT operations on tempdb are only done when the log usage reaches 70%
dirty pages are not written to disk on automatic CHECKPOINT (its primary purpose it to clear the log)

The "work table" rows in dm_tran_active_transactions are normal behavior, and shouldn't prevent the log from being cleared. You can read more about that in this Q&A:

SQL Server sys.dm_tran_active_transactions long running transaction worktable

Related Solutions

Sql-server – Changing a column’s data type fills the transaction log

Try to set your log file to autogrow or temporarily add some extra log files which can handle such an operation

BUT

it may be simplier and faster to do something like this:

use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20)
GO
UPDATE npidata SET
  npiNew = SUBSTRING(npi, 1, 20)
GO
ALTER TABLE npidata DROP COLUMN npi
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)

In this case log file usage can be controlled by UPDATE statement, you may perform this by small chunks like:

use SalesDWH
go
ALTER TABLE npidata ADD npiNew varchar(20), ready bit
GO
WHILE 1 = 1
BEGIN
  UPDATE TOP (100) npidata SET
    npiNew = SUBSTRING(npi, 1, 20),
    ready=1
  WHERE ready is null

  IF @@ROWCOUNT = 0 BREAK
END
GO
ALTER TABLE npidata DROP COLUMN npi
GO
ALTER TABLE npidata DROP COLUMN ready
GO
EXEC sp_rename 'npidata.npiNew', 'npidata.npi', 'COLUMN'
GO
DBCC CLEANTABLE(0, 'npidata', 100)

For better performance, in case when the table is big - you may apply temporary index on column ready

OH, YES!!!

Like @RemusRusanu said - TURN OFF THE AUTOSHRINK

Sql-server – SQL Server transaction log file refuses to grow

To fix the issue in the short term manually grow the log file since that's best practice anyway.

If the log file will end up being multiple GB grow it in 1 - 4 GB increments to get there.

If you don't know how big it's going to get I would recommend using at least 1 GB increments. This is a blocking operation so it should be done during a low use time if at all possible.

To discover why your problem is happening check for errors in the SQL error log, not just error message from the failed statement. There may be an error stating why it couldn't grow.

You can also look at activity monitor to see what the sessions that are having problems are waiting on. Right click on the instance in SQL Server Management Studio (SSMS) and click on "Activity Monitor." Once you have it open look at the blocking chain (Blocked By and Head Blocker) and the Wait Type to see what is causing the issue.

To determine why the DB is getting created with a MAXSIZE of 70 run Profiler to catch the CREATE DATABASE statement. That will tell you what query is getting run so you know if SQL is getting the correct query or not.

Best Answer

Related Solutions

Sql-server – Changing a column’s data type fills the transaction log

Sql-server – SQL Server transaction log file refuses to grow

Related Question