Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to find the root cause of tempdb log file increasing Expand / Collapse
Author
Message
Posted Friday, August 22, 2014 9:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:25 AM
Points: 23, Visits: 142
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.
Post #1606581
Posted Friday, August 22, 2014 11:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 2,253, Visits: 6,183
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
Post #1606586
Posted Saturday, August 23, 2014 12:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 127, Visits: 185
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
Post #1606593
Posted Saturday, August 23, 2014 2:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:25 AM
Points: 23, Visits: 142
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.
Post #1606598
Posted Saturday, August 23, 2014 3:13 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 57, Visits: 169
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
Post #1606601
Posted Saturday, August 23, 2014 3:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:07 AM
Points: 2, Visits: 109
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.
Post #1606602
Posted Saturday, August 23, 2014 4:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 2,253, Visits: 6,183
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?
Post #1606613
Posted Saturday, August 23, 2014 8:57 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:31 AM
Points: 57, Visits: 169
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.

Post #1606707
Posted Monday, August 25, 2014 7:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:25 AM
Points: 23, Visits: 142
er.mayankshukla (8/23/2014)
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.




I have found some scripts from the internet, but need to combine them using some methods.
that is what i am trying to resolve.
Post #1607307
Posted Monday, August 25, 2014 9:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 2,253, Visits: 6,183
Here is a useful script from Paul S. Randal, 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

Post #1607322
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse