January 20, 2010 at 7:56 am
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
January 20, 2010 at 8:10 am
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
January 20, 2010 at 8:19 am
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
January 20, 2010 at 10:21 pm
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
January 20, 2010 at 10:41 pm
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
January 21, 2010 at 9:33 am
I agree, it's got to be something running. What would I profile for though?
The Redneck DBA
January 21, 2010 at 12:11 pm
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
January 21, 2010 at 12:23 pm
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
January 21, 2010 at 10:02 pm
Very cool code and a great link, Jason. Thanks!
--Jeff Moden
January 21, 2010 at 10:06 pm
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
January 24, 2010 at 7:34 pm
Try to use report 'Disk usage by top table' to find which table use so big space, and then analysis this table.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply