May 24, 2011 at 9:39 pm
I've been having a problem with the TempDB on one of the servers I manage since yesterday. There are a few databases on this server (ranging from 50GB to over 500GB). There are 2 data drives at 2 TBs each as well as a TempDB data drive (200GB in size) and a TempDB log file drive (100 GB). These are separate physical drives.
Late yesterday one of the analysts came to me with an error that TempDB could not be accessed. On checking the drive, less than 7 MB of space was left. This morning (no one was connected to the server yet) I stopped SQL and deleted all of the TempDB files before starting SQL back up.
The drive dropped to 20 GB in use (each of the 4 data files is set to an initial size of 5 GB). Now, less than 6 hours after I restarted SQL the TempDB data drive has less than 7 MB of space available again.
I've done some digging and have found a list of all the tables being currently used:
SELECT * FROM tempdb..sysobjects (NOLOCK) WHERE xtype = 'U'
I then ran this query to get approximate sizes of current indexes:
SELECT * FROM tempdb..sysobjects (NOLOCK) WHERE xtype = 'U'
However I am not sure of what to do. How can I tell what is causing the TempDB to to expand so fast?
I've been thinking about having another drive added to deal with the fast growth in data files but not sure if that is required?
Thanks
May 24, 2011 at 11:01 pm
This query inform you about the usage of tempdb space
SELECT
SPID = s.session_id,
s.[host_name],
s.[program_name],
s.status,
s.memory_usage,
granted_memory = CONVERT(INT, r.granted_query_memory*8.00),
t.text,
sourcedb = DB_NAME(r.database_id),
workdb = DB_NAME(dt.database_id),
mg.*,
su.*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_db_session_space_usage su
ON s.session_id = su.session_id
AND su.database_id = DB_ID('tempdb')
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.most_recent_session_id
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
LEFT OUTER JOIN (
SELECT
session_id,
database_id
FROM sys.dm_tran_session_transactions t
INNER JOIN sys.dm_tran_database_transactions dt
ON t.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID('tempdb')
GROUP BY session_id, database_id
) dt
ON s.session_id = dt.session_id
CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle,
c.most_recent_sql_handle)) t
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
WHERE (r.database_id = DB_ID('tempdb')
OR dt.database_id = DB_ID('tempdb'))
AND s.status = 'running'
ORDER BY SPID;
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 24, 2011 at 11:17 pm
Try this article for some info on things you can do to troubleshoot the growth.
http://www.sqlservercentral.com/articles/Log+growth/69476/
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
May 25, 2011 at 12:32 am
SQLRNNR (5/24/2011)
Try this article for some info on things you can do to troubleshoot the growth.
I do not have a problem with the transaction log, it's the actual database files.
Thanks for the script Syed, but it does not return any value.
I now have the the same problem with another server, the TempDB drive has 7MB of space left.
May 25, 2011 at 4:42 am
You need to look at what processes you're putting through tempdb. If it's growing that much, it's because it's being used, so you need to identify what it is that is using it. Do you have lots of data loads that use temp tables or table variables? Stuff like that is what you have to identify.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 25, 2011 at 5:16 am
Start with :-
1) use this query to get NoOfConnections in tempdb
(SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE db_name(dbid) = 'tempdb' GROUP BY dbid, loginame)
2) Sp_who2 and see the spid on tempdb
3) on new query window check whats running behind those spids using
dbcc inputbuffer(spid)
----------
Ashish
May 25, 2011 at 9:03 am
mpartridge (5/25/2011)
SQLRNNR (5/24/2011)
Try this article for some info on things you can do to troubleshoot the growth.I do not have a problem with the transaction log, it's the actual database files.
Thanks for the script Syed, but it does not return any value.
I now have the the same problem with another server, the TempDB drive has 7MB of space left.
The same principles can be used to troubleshoot the growth of your tempdb files.
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
May 25, 2011 at 5:53 pm
Thanks for the replies guys. Turns out one of the analysts was inserting 27 million rows and doing a lot of sorting on non indexed columns. He finished his run and the data files returned to their normal sizes.
He's now aware of what he was doing wrong and shouldn't be making the same mistake again.
May 26, 2011 at 4:13 am
I had the similar issue on our PROD environment, by finding the culprit SPs and tunning it had helped us to manage the TEMP DB space.
Hence everything depends on the tunning parts.
Please ensure to pass on the same information to the Devlopment team.
"More Green More Oxygen !! Plant a tree today"
May 26, 2011 at 4:19 am
Turns out one of the analysts was inserting 27 million rows and doing a lot of sorting on non indexed columns. He finished his run and the data files returned to their normal sizes.
Not necessary to respond, but if you can then share the information how you proceed to analyse this.
----------
Ashish
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply