July 27, 2016 at 6:44 am
I have an issue with one of my clusters when TEMPDB Log won't shrink and keeps growing until reset. Happens randomly about once a month and have to reset eventually although the growth is gradual(2GB a day).
OS: Windows 2012 R2(Patched up to date)
SQL : 11.0.6020
Cluster Setup: Multi-Subnet with 2 Syncronous Physical in Primary and 1 ASYNC Virtual in secondary DataCenter
*TempDB/TempLog is on local SSD while other DATA on Shared SAN.
*I have run checkpoint
*I have run dbcc freeproccache
*Log_resuse_wait is ACTIVE_TRANSACTION
The only active transaction is sp_server_diagnostics which I used multiple queries to make sure(sp_whoisactive and below query and a few more just to be sure). I killed that but this is for the isAlive heartbeat check and comes right back and still can't clear log
Any help would be much appreciated!
;WITH task_space_usage AS (
-- SUM alloc/delloc pages
SELECT session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE session_id <> @@SPID
GROUP BY session_id, request_id
)
SELECT TSU.session_id,
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
EST.text,
-- Extract statement from sql text
ISNULL(
NULLIF(
SUBSTRING(
EST.text,
ERQ.statement_start_offset / 2,
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
THEN 0
ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
), ''
), EST.text
) AS [statement text],
EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;
July 27, 2016 at 11:23 am
What is the output you get when you run:
DBCC OPENTRAN
Sue
July 27, 2016 at 11:39 am
Actually dbcc opentran could still not give you the results you need since it's for the current database only.
Did you query sysprocesses where open_tran >0
I would also try this script instead to see the query text of any open transactions:
http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/
Sue
July 27, 2016 at 11:48 am
Thanks very much the Paul Randal led me to a stray process that didn't show up as an active transaction. Issue resolved!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply