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 :
The following types of objects can occupy tempdb space:
Internal objects
Version stores
User objects
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