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

The error message "The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'" typically occurs when the transaction log for the tempdb database becomes full. This can happen for several reasons, which I'll dive into down below, along with ways to check if indeed they are the issue.

A note first for those on shared servers. If you are sharing your database server space with other applications, such as on GoDaddy Plesk web apps, then you might need to reach out support, as some of these solutions require administrative access to the actual server.

Possible causes

Long running transactions

Long running transactions are transactions that remain open for a prolonged period of time, typically much longer than your standard transaction. These could end up continuously writing to the transaction log and could inevitably lock resources from being cleared.

Insufficient disk space

Quite possibly the most common cause if you're running your websites on a shared database server is the lack of disk space. Often times, you might see hundreds upon hundreds of database tables all sharing the same server, and while your application might not be as resource heavy, there might be other's on that server that consume a large amount.

Different database engines and hosts have their own set limits when it comes to database size. GoDaddy for example sets a 300MB per database limit on the Windows hosting plans, which for most standard websites shouldn't be a huge problem.

Other providers might market having unlimited storage, though performance might be the trade-off that you get.

Preventive measures

If you start encountering that error every so often then there are a few things that you can start to look at in order to fix it.

Query optimization

If you have long-running queries with complex joins and large values, then potentially you could be putting quite the load on 'tempdb'.

Try limiting the result set to a certain number and add pagination if needed, or reduce the number of columns that you're querying to only the relevant ones.

-- avoid
SELECT * FROM table_name


-- adjust to
SELECT TOP 10 title, categoryid, id from table_name

For me personally, updating my queries and reducing the result set size typically fixes this error relatively quickly.

Note that if you're on a shared server, this might be the only alternative solution that you have available, as tempdb is shared by all of the databases that are running on the server.

Check for running transactions

In order to check if you have any long-running transactions running on your server, you can run the following query.

SELECT session_id, start_time, status, command, blocking_session_id
FROM sys.dm_exec_requests
WHERE database_id = DB_ID('tempdb');

If you are running on a shared server, you will see the transactions that are running under your user account.

'tempdb' management

Managing and configuring your 'tempdb' database correctly can help to prevent issues such as these in the future.

If you're on a shared server, then this is out of your control as only database admins can configure and maintain 'tempdb'.

However, if you do have access (such as with a VPS), then you can run the following query in order to see the remaining space on your 'tempdb' table.

SELECT 
    SUM(unallocated_extent_page_count) * 1.0 / 128 AS [Unallocated Space in MB],
    SUM(version_store_reserved_page_count) * 1.0 / 128 AS [Version Store Space in MB],
    SUM(internal_object_reserved_page_count) * 1.0 / 128 AS [Internal Object Space in MB],
    SUM(user_object_reserved_page_count) * 1.0 / 128 AS [User Object Space in MB],
    SUM(mixed_extent_page_count) * 1.0 / 128 AS [Mixed Extent Space in MB]
FROM sys.dm_db_file_space_usage;

Ensuring that your 'tempdb' table is configured correctly is also crucial for proper performance. For example, setting the proper autogrowth values to ensure that the database can scale appropriately can help to mitigate issues later on.