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:
from sys.databases with (NOLOCK)
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
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.
1,673 total views, 2 views today