Database Reference
In-Depth Information
There are plenty of reasons SQL Server is unable to truncate the transaction log. You can examine the
log_reuse_wait_desc
column in the
sys.databases
view to discover the reason the transaction log cannot be reused.
You can see the query, which checks
log_reuse_wait_desc
for the users' databases in Listing 29-2. The output of the
query is shown in Figure
29-15
.
Listing 29-2.
Check log_reuse_wait_desc for users' databases
select database_id, name, recovery_model_desc, log_reuse_wait_desc
from sys.databases
where database_id >=5
Figure 29-15.
Log_reuse_wait_desc output
For databases in the FULL or BULK-LOGGED recovery models, one of the most common reasons the transaction
log is not truncated is the lack of log backups. It is a common misconception that a FULL database backup truncates
the transaction log. It is not true, and you must perform log backup in order to do so. The
Log_reuse_wait_desc
value
of
LOG_BACKUP
indicates such a condition.
The
Log_reuse_wait_desc
value of
ACTIVE_TRANSACTION
indicates that there are long and/or uncommitted
transactions in the system. SQL Server is unable to truncate the transaction log past the LSN of the oldest
uncommitted transaction, regardless of the database recovery model in use.
The query in Listing 29-3 returns the list of the five oldest uncommitted transactions in the current database.
It returns the time when the transaction was started, information about the session, and log usage statistics.
Listing 29-3.
Query that returns a list of the five oldest active transactions in the system
select top 5
ses_tran.session_id as [Session Id]
,es.login_name as [Login]
,es.host_name as [Host]
,es.program_name as [Program]
,es.login_time as [Login Time]
,db_tran.database_transaction_begin_time as [Tran Begin Time]
,db_tran.database_transaction_log_record_count as [Log Records]
,db_tran.[database_transaction_log_bytes_used] as [Log Used]
,db_tran.[database_transaction_log_bytes_reserved] as [Log Rsrvd]
,sqlText.text as [SQL]
,qp.query_plan as [Plan]
from
sys.dm_tran_database_transactions db_tran join
sys.dm_tran_session_transactions ses_tran on
db_tran.transaction_id = ses_tran.transaction_id
join sys.dm_exec_sessions es on
es.[session_id] = ses_tran.[session_id]
left outer join sys.dm_exec_requests er on
er.session_id = ses_tran.session_id
