Large Tempdb size

Question

Good Morning Friends!

I had a problem last night where our Tempdb grew to 138GB. Is that normal? We havent tracked dbsizes to analyze the pattern. With TempDB growing so big it occupied most of the disk space which were not able to free.. We shrunk files to release space.

1. I would like to know how I can dig in to see what exactly is going on with tempdb?

2. What can be the fixes for these kind of problems

This is production issue. Any urgent help is very much appreciated!!! 

Thanks,

Gopu

---

Gopal

Answer 1

Hi

Can you please check the location of the TEMP DB.

Sparte the Temp Db with user databases.

Check the commited transaction and un commited transaction.. in temp dont shrnik blankly temp db it is not recomended as DBA.

---

subu

Answer 2

Hi, in the following link you can check when tempdb is used: Capacity Planning for tempdb

Check also this link for troubleshooting: Troubleshooting Insufficient Disk Space in tempdb

David.

Answer 3

Hi,

There is no way to track for what processes were running in the past unless you run a profiler. Which may not be recommended for long period of time on production system. Sql sentry is a good tool to track that if you have it installed.

There is one proc written by Adam Machanic: sp_whoisactive..if run when transaction log is growing can tell you exactly what queries are running for how long. And you can drill down to the longest running query (possibly the culprit)

It might be that someone was creating a lot of temp tables for processing data from large dataset on that server.

-Sddesh

Answer 4

First of all Thanks to all you guys for the prompt replies! On my findings I see no open transactions to tempdb and on the tempdb properties allocated space and free space are the same. Services were restarted around 3 months back. Just wanted to post the information which might help us in tracking down the problem.

Gopu

---

Gopal

Answer 5

Thanks Subu for the reply.. This would be my next step as a long term solution. Can you explain why shirinking a blank db is bad?

Gopu

---

Gopal

Answer 6

Great info Dac03..Will be following these links to narrow down the issue..

Gopu

---

Gopal

Answer 7

Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information,

Use the following commands  and try to shrink the tempdb.

DBCC FREEPROCCACHE

GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO

please see http://msdn.microsoft.com/en-us/library/ms176029.aspx

Answer 8

Thank you for the suggestion Debasish!.. Well the problem right now is my tempdb is 99% empty. I did a service restart and it didnt help. Why is tempdb not releasing the unused space? It is all the tempdb data file which is holding up the space. 

How can we free up this space?

Thanks,

Gopu

---

Gopal

Answer 9

What is the initial size set to in the tempdb file properties? It rebuilds each time you restart.

---

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you.

Answer 10

When I checked It was 110GB.. So that is why it rebuilt a empty datafile..? So I just have to reduce this file size and restart?

Gopu

---

Gopal

Answer 11

Hi,

if you can read chinese, then there is a good post at the link below that describes the issue and troubleshooting steps for tempdb:
http://blogs.msdn.com/b/apgcdsd/archive/2011/02/11/sql-server-tempdb.aspx

---

Best Regards, nicofer

Answer 12

Hi Gopal,

Temp db t-log shrink  i had face very bad situation we lose all temp valueble informaiton also. finally again we re-start the server. with alot of approval, its bit panic to team.

for that reason only i put blankly...

Tx

---

subu

Answer 13

Hi Gopal,

Tempdb recreate when you restart your server.After restarting your server, if your tempdb size is 110gb then i think you have to check model database bcoz

model databse is template db.

---

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you