tempdb growth problem

Question

tempdb growth problem

TheRedneckDBA

SSChampion

Points: 14001
More actions
January 20, 2010 at 7:56 am

#156595

I'm having an issue with some odd tempdb file growth, and hoping someone can give me some ideas on how to deal with it.
We have 20+ SQL servers scattered around the country on a WAN that are configured identically. About once a week one of them will experience sudden growth in the tempdb data file, to the point where it will fill up the hard drive. (To prevent that, I put a limit of 25 GB on the data file, in which case it expands to 25GB and then things start blowing up with 'tempdb full' error messages).
We have good monitoring tools and I'm able to see to within 5 min. or so when the growth starts, but it seems to be starting at random times when nothing special is running, and occasionally even overnight when absolutely nothing is running that I can see.
I'm running the following queries to try to see what is happening:
The following query will return that the data file is about 25GB
SELECT
name AS logical_name,
physical_name,
size * 8 / 1024 AS file_size
FROM
tempdb.sys.database_files
The following query (found on a SQLServerCentral.com article) returns that the tempdb file has 99.991% free space in it:
SELECT
user_object_perc = CONVERT(DECIMAL(6,3), u*100.0/(u+i+v+f)),
internal_object_perc = CONVERT(DECIMAL(6,3), i*100.0/(u+i+v+f)),
version_store_perc = CONVERT(DECIMAL(6,3), v*100.0/(u+i+v+f)),
free_space_perc = CONVERT(DECIMAL(6,3), f*100.0/(u+i+v+f)),
[total] = (u+i+v+f)
FROM (
SELECT
u = SUM(user_object_reserved_page_count)*8,
i = SUM(internal_object_reserved_page_count)*8,
v = SUM(version_store_reserved_page_count)*8,
f = SUM(unallocated_extent_page_count)*8
FROM
sys.dm_db_file_space_usage
) x;
The following will return no active transactions in tempdb:
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC
Despite the fact that it appears nothing is using tempdb significantly, I can't for the life of me get the file to shrink. Any suggestions would be greatly appriciated!
Thanks,
Jason
The Redneck DBA

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Are there any jobs running DBCC or Index maintenance at those times?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 2

We do have a nightly job that defrags/rebuilds indexes, but the tempdb growth doesn't happen anywhere close to when that job runs.

That's what's so frustrating about this...it seems to always happen during times when the DB server is fairly idle.

The Redneck DBA

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Run profiler and capture the "growth" event. You probably won't be able to capture the code causing the problem because it never completes but, make no doubt about it, it's code causing the problem. Someone has written a poor set of joins that return an accidental cross-join.

You might be able to run a DBCC INPUTBUFFER on the offending SPID if you can figure out which SPID it is... the profiler "growth" even will give you that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

I agree with Jeff. Since the growth start is somewhat random - there is a query that is firing off, that needs tuned, at the root of the problem.

You can use the profiler option.

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

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 5

I agree, it's got to be something running. What would I profile for though?

The Redneck DBA

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

This may help...

If you know a time window during which this event is most likely to occur then a scheduled job could be created. The scheduled job would run frequently and capture all running queries. The shorter the window, the better.

Insert Into AdminDB.dbo.AuditTSQL (LoginName,SPID,DBName,ParentQuery,Wait,Program,HostName,StartTime,InsertedTime)

SELECT sp.loginame as [UserName],sp.spid as SPID

, DB_NAME(sp.dbid)

,qt.text

,wait_type

,program_name

,Hostname

,start_time

,getdate()

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp

ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt

WHERE session_Id > 50 -- Ignore system spids.

I used this query in a million user (and about 1.5 million logons daily) database to capture queries being executed by specific individuals based on a logon trigger. There was no noticeable performance degradation due to it. It will capture the sql, but you will quite likely end up with duplicate data. Since the query is running prior to the tempdb growth, you would want to be running this kind of a query on a schedule for before and after the event that causes the growth.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

And here is info on the Profiler that Jeff spoke of:

http://www.eraofdata.com/blog/tag/sql-profiler/

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Very cool code and a great link, Jason. Thanks!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Jeff Moden (1/21/2010)
Very cool code and a great link, Jason. Thanks!

Thanks for the compliment and 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

gaojia2004 SSC-Addicted Points: 409 More actions · Answer 10

Try to use report 'Disk usage by top table' to find which table use so big space, and then analysis this table.