March 16, 2010 at 5:04 pm
Hello Friends,
I'm having an issue with my tempdb on one of my servers, at least once a week it grows out of control and uses all the space on the HD. I've queried the db to see whats going on and 98% is system related object, mainly replication. No other server is experiencing the same issue, and I'm at a lost to a) Understand why this is happening, never happened before and b) How to stop this from happening. Any advice or possible solutions would be greatly appreciated
Cheers 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 17, 2010 at 2:29 am
What sort of replication do you have on the server?
What makes you think the space in tempdb is used by replication?
Information on using the dynamic management views to identify big users of tempdb can be found here:
Troubleshooting Insufficient Disk Space In tempdb
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 2:43 am
Its a mix of the three, but its mainly merge. I know its the replication agent because i ran this query
SELECT
sys.dm_exec_sessions.session_id AS [SESSION ID],
DB_NAME(database_id) AS [DATABASE Name],
HOST_NAME AS [System Name],
program_name AS [Program Name],
login_name AS ,
status,
cpu_time AS [CPU TIME (in milisec)],
total_scheduled_time AS [Total Scheduled TIME (in milisec)],
total_elapsed_time AS [Elapsed TIME (in milisec)],
(memory_usage * 8) AS [Memory USAGE (in KB)],
(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)],
(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)],
(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)],
(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)],
CASE is_user_process
WHEN 1 THEN 'user session'
WHEN 0 THEN 'system session'
END AS [SESSION Type], row_count AS [ROW COUNT]
FROM sys.dm_db_session_space_usage
INNER join
sys.dm_exec_sessions
ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
Which showed me that the biggest chuck on the tempdb was being used by Replication Distribution History and the jobs that service the replication
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 17, 2010 at 2:53 am
So, if you apply to sys.dm_exec_sql_text, does that give you any more clues?
You should be able to see if it is related to initialization or ongoing replication operations.
How is replication performing in general?
edit: DMF name!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 3:30 am
Can't say i'm familiar with sys.dm_exec_query_text, sorry... little help?? 🙂 Also the replication is fine runs smoothly without any issues.
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 17, 2010 at 3:49 am
Mr J (3/17/2010)
Can't say i'm familiar with sys.dm_exec_query_text, sorry... little help?? 🙂 Also the replication is fine runs smoothly without any issues.
Sorry, typo. Meant sys.dm_exec_sql_text:
SELECT S.session_id AS [SESSION ID],
DB_NAME(SU.database_id) AS [DATABASE Name],
HOST_NAME AS [System Name],
PROGRAM_NAME AS [Program Name],
login_name AS ,
S.status,
S.cpu_time AS [CPU TIME (in milisec)],
total_scheduled_time AS [Total Scheduled TIME (in milisec)],
S.total_elapsed_time AS [Elapsed TIME (in milisec)],
(memory_usage * 8) AS [Memory USAGE (in KB)],
(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)],
(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)],
(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)],
(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)],
CASE is_user_process
WHEN 1 THEN 'user session'
WHEN 0 THEN 'system session'
END AS [SESSION Type],
S.row_count AS [ROW COUNT],
R.request_id,
R.command,
R.cpu_time,
R.granted_query_memory,
R.logical_reads,
R.start_time,
R.status,
R.total_elapsed_time,
R.wait_type,
R.wait_type,
R.wait_resource,
ST.text,
QP.query_plan
FROM sys.dm_db_session_space_usage SU
JOIN sys.dm_exec_sessions S
ON SU.session_id = S.session_id
LEFT
JOIN sys.dm_exec_requests R
ON R.session_id = S.session_id
OUTER
APPLY sys.dm_exec_sql_text (R.sql_handle) ST
OUTER
APPLY sys.dm_exec_query_plan (R.plan_handle) QP;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 3:59 am
thanks Paul, i already purged the tempdb so its not goin to be much information within it until it blows out again next time. So i guess I'll have to get back to you when it does. Do you have any ideas why this could be happening ??
Cheers
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 17, 2010 at 4:05 am
Mr J (3/17/2010)
thanks Paul, i already purged the tempdb so its not goin to be much information within it until it blows out again next time. So i guess I'll have to get back to you when it does. Do you have any ideas why this could be happening?Cheers
At least you now have another diagnostic tool for when it does. I have added a few columns to the statement to return extra information and the query plan too, just in case it is useful.
As to the cause, it could be just so many things...I would not even like to guess.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 4:16 am
Too true, thanks for the help and advice, i 'll reply with anythin interesting that might come up. Thanks again
Mr J 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 17, 2010 at 8:03 am
I'm just curious. You say it grows out of control but didn't say how big TempDB was getting. How big is it getting?
--Jeff Moden
March 17, 2010 at 4:21 pm
Hi Jeff,
It's hard to say just how big it wants to get, because it always runs out of HD, but i can tell you it gobbles up 40 Gbs within an hour(i have a HD monitoring tool that emails me hourly).
Cheers
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 18, 2010 at 4:37 pm
Mr J (3/17/2010)
Hi Jeff,It's hard to say just how big it wants to get, because it always runs out of HD, but i can tell you it gobbles up 40 Gbs within an hour(i have a HD monitoring tool that emails me hourly).
Cheers
It's not likely (at all) that replication or even rebuilding indexes will cause such explosive growth. Somewhere, someone is running (probably a weekly report script) that has an "accidental cross join" in it (usually due to someone not understanding the data and writing incorrect or incomplete criteria.)
Paul's good script should help you find it.
--Jeff Moden
March 18, 2010 at 4:52 pm
I would also recommend checking out http://qa.sqlservercentral.com/articles/Log+growth/69476/ to help you track down the culprit.
You can setup a job to track your growths and capture the query that is at the root cause of the problem.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 18, 2010 at 7:09 pm
Thanks gents for the advice, I'm currently setting up the login inspection db to capture the growth of the tempdb so to see exactly when this might happen, plus i have informed the web dev team to make sure all SP's are droppin the #tables and will personally go through all the sql code on the jobs that are running. 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
March 18, 2010 at 8:01 pm
Mr J (3/18/2010)
Thanks gents for the advice, I'm currently setting up the login inspection db to capture the growth of the tempdb so to see exactly when this might happen, plus i have informed the web dev team to make sure all SP's are droppin the #tables and will personally go through all the sql code on the jobs that are running. 🙂
You're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply