What is holding the transaction log from shrinking?

If the database transaction log is ever growing, or you are trying to shrink it and it just sits there stubbornly refusing, run the below script and check the log_reuse_wait column. If it is saying anything other than LOG_BACKUP or NOTHING than you have to continue investigating.

select name, log_reuse_wait_desc from sys.databases

If it is LOG_BACKUP than just run one or more LOG backups, normally that will resolve it. You may have a problem that the active part of the log is at the end of the log file. You can check that with

dbcc loginfo()

Scroll down to the bottom, looking at the Status column, whenever there is a 2 in there it means that part of the log file is currently used, you can never shrink past a status 2 block counting from the bottom.

Check if you have any open transactions with

dbcc opentran()

or you can use the below script that does a more thourough check for long running transactions.

SELECT 	db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
		SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
		(CASE WHEN r.statement_end_offset = -1
				THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
		ELSE 
				r.statement_end_offset
		END -r.statement_start_offset)/2) AS stmt_executing,
	s.login_name, r.percent_complete, r.start_time
	,CASE s.transaction_isolation_level
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'ReadUncomitted'
		WHEN 2 THEN 'ReadComitted'
		WHEN 3 THEN 'Repeatable'
		WHEN 4 THEN 'Serializable'
		WHEN 5 THEN 'Snapshot'
	END as 'Isolation level'
	,	s.HOST_NAME, 	s.PROGRAM_NAME,	s.host_process_id, 
	r.status, 	r.wait_time,	wait_type, 	r.wait_resource,
	r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle
	, (select query_plan from sys.dm_exec_query_plan(r.plan_handle)) as xml_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
			sys.dm_exec_sessions s
WHERE r.session_id > 50 
and r.session_id=s.session_id
and r.session_id <> @@SPID
ORDER BY r.cpu_time desc

If log_reuse_wait is anything but LOG_BACKUP or NOTHING than have a look here at Microsoft site about factors that can delay log truncation.

If the log disk is full first investigate WHY it is filling up before just extending the file/disk, if it for example is because your log backups are failing than it will never stop growing and you are just wasting disk instead of trying to solve the real problem.

Leave a Reply

Your email address will not be published. Required fields are marked *