log truncate interruption – open transaction

http://yoonsy.tistory.com/26

Case:

DB Type: MS SQL 2012

DB Recovery Model: Full

One day, Growing DB log size is pressing the Disk space, it almost full.

See the status of db files, the log file size has been increased very big though the transaction log backup task has been taking regularly.

Once to handle this situation, just trying the DBCC SHRINKFILE  statement but?! it doesn’t work, log size is still being growing.

 

According to the contents of blog mentioned above..

  • A long Transaction is open and still running, this disturbs the file shrink action.
  • To check the open transactions, run the queries below:

select name,state,state_desc,log_reuse_wait,log_reuse_wait_desc
from sys.databases with (NOLOCK)
where database_id=DB_ID(‘BBLEARN’);

dbcc opentran;

SELECT tl.resource_type, tl.request_type, tl.request_status, tl.request_owner_id, sp.waittime, t.text AS blocked_query, t2.text AS blocking_query
FROM
sys.dm_tran_locks tl
INNER JOIN sys.sysprocesses sp ON tl.request_session_id = sp.spid
INNER JOIN sys.sysprocesses sp2 ON sp.blocked = sp2.spid
CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle) AS t
CROSS APPLY sys.dm_exec_sql_text (sp2.sql_handle) AS t2
WHERE request_status != ‘GRANT’;

–kill 559 — kill the process forcely.

— exec sp_lock; — check the locks.

–dbcc inputbuffer(301); — Look the query which caused the lock

Modification of application logic which cause the long transaction is needed.

2,486 total views, 1 views today

Leave a Reply