August 22, 2014 at 9:33 pm
Hi all,
I found the tempdb log file size of one instance increased to 200 GB.
how can i get the root cause of the issue?
any answer is appreciated.
thanks.
August 22, 2014 at 11:22 pm
Wison (8/22/2014)
Hi all,I found the tempdb log file size of one instance increased to 200 GB.
how can i get the root cause of the issue?
any answer is appreciated.
thanks.
Quick suggestion, look into sys.dm_tran_* dynamic management views. There are quite few good articles around such as this one Investigating Transactions Using Dynamic Management Objects[/url]
😎
August 23, 2014 at 12:59 am
This was removed by the editor as SPAM
August 23, 2014 at 2:42 am
jacksonandrew321 (8/23/2014)
Wison (8/22/2014)
Hi all,I found the tempdb log file size of one instance increased to 200 GB.
how can i get the root cause of the issue?
any answer is appreciated.
thanks.
You can reduce the tempdb size by using T-SQL, DBCC shrinkDataBase and DBCC shrinkFile. For more information about these visit : http://support.microsoft.com/kb/307487
actually now the status of tempdb vlf is 2. and i cannot shrink it until the oldest active transaction releases.
so, i only want to get some ideas about how to find the root cause of tempdb log file increased to so large.
August 23, 2014 at 3:13 am
I would not suggest you to use DBCC ShrinkDatabase, but should see the query which is creating this.
In one of my projects I observed such a behaviour when I was using a bulk delete which caused log file to increase tremendously.
Try to use fn_dblog and try to find which transaction created maximum log
August 23, 2014 at 3:14 am
In your case, the reason mite be some long running queries still waiting to commit or mite be blocking. So, as u mentioned in the earlier post that still active transactions are present in ur VLF rite, try to check what operations the Open Transactions are firing. Hope this helps. if u still Unable to find the issue, i can give u more suggestions as i faced similar issues in the past.
August 23, 2014 at 4:35 am
er.mayankshukla (8/23/2014)
I would not suggest you to use DBCC ShrinkDatabase, but should see the query which is creating this.In one of my projects I observed such a behaviour when I was using a bulk delete which caused log file to increase tremendously.
Try to use fn_dblog and try to find which transaction created maximum log
Quick question, have you looked into the sys.dm_tran* views to identify the transaction activities?
😎
August 23, 2014 at 8:57 am
Yes, but dmv's like dm_tran_active_transactions Or dm_tran_database_transactions
do not provided me the log length which I require to see which transaction is making my log size to increase.
August 25, 2014 at 7:05 pm
er.mayankshukla (8/23/2014)
Yes, but dmv's like dm_tran_active_transactions Or dm_tran_database_transactionsdo not provided me the log length which I require to see which transaction is making my log size to increase.
I have found some scripts from the internet, but need to combine them using some methods.
that is what i am trying to resolve.
August 25, 2014 at 9:50 pm
Here is a useful script from Paul S. Randal[/url], lists open transactions with plans and text
😎
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM
sys.dm_tran_database_transactions [s_tdt]
JOIN
sys.dm_tran_session_transactions [s_tst]
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
sys.[dm_exec_sessions] [s_es]
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec]
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er]
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
[Begin Time] ASC;
GO
August 25, 2014 at 10:34 pm
fn_dblog , I found useful becoz it gave me the log size created with a transaction.
You can fire a command and check the new transaction id created in the log by this command.
select [transaction id] ,* from fn_dblog(null,null)
Once you get this, get an aggregated value of log size :
select sum([log record length]),[transaction id] from fn_dblog(null,null)
where [transaction id] = '0000:01886015' -- replace it
group by [transaction id]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply