Database log and transaction log for database 'tempdb' is full log_reuse_wait_desc

Message:

The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Solution :

tempdb Space Usage

The following types of objects can occupy tempdb space:

Check the current connection and transaction  on tempdb

DBCC SQLPERF('logspace')

SELECT name,recovery_model_desc,log_reuse_wait,

log_reuse_wait_desc,page_verify_option_desc

FROM [master].[sys].[databases]

where log_reuse_wait_desc <> 'NOTHING'

--------------------------------------------------------------

Sys.dm_db_file_space_usage

This DMV returns space allocation information for the files associated with tempdb. The allocation information is grouped by object category (user, internal, and version store) for the instance. The following code is an example.

SELECT SUM (user_object_reserved_page_count)*8 as usr_obj_kb, SUM (internal_object_reserved_page_count)*8 as internal_obj_kb, SUM (version_store_reserved_page_count)*8  as version_store_kb, SUM (unallocated_extent_page_count)*8 as freespace_kb, SUM (mixed_extent_page_count)*8 as mixedextent_kb FROM sys.dm_db_file_space_usage

-----------------------------------------------------------------------------------------

Sys.dm_db_session_file_usage

SELECT top 5 *  FROM sys.dm_db_session_space_usage   ORDER BY (user_objects_alloc_page_count +  internal_objects_alloc_page_count) DESC

Sys.dm_db_task_space_usage

SELECT top 5 *  FROM sys.dm_db_task_space_usage ORDER BY (user_objects_alloc_page_count +  internal_objects_alloc_page_count) DESC

If the version store is not shrinking, it is likely that a long-running transaction is preventing version store cleanup. The following query returns the five transactions that have been running the longest and that depend on the versions in the version store.

SELECT top 5 transaction_id, transaction_sequence_num,  elapsed_time_seconds  FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC

https://technet.microsoft.com/library/Cc966545