SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TempDB Full...!!


TempDB Full...!!

Author
Message
Jitendra Padhiyar
Jitendra Padhiyar
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 671
Hello Masters,

My TempDB full..!! Its utilising almost 400% more disk space than its assigned..! How can I free disk space ? If I will take backup of system databses, will it free the disk space ? Please advice me..
Sarah Wagner
Sarah Wagner
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1628 Visits: 715
Restart sql and it will create a new tempdb the size it was originally set up as. However you need to find the root cause of what made it grow so large or it will just happen again.
dkschill
dkschill
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 430
This is a tough question....made even tougher because you posted in the Newbie forum (I am not sure what you may have checked, and what prior knowledge you have). Do you have an idea of how much space the tempdb was consuming an hour ago, yesterday, or even last week?

Hope to help,
Dane
Jitendra Padhiyar
Jitendra Padhiyar
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 671
I want to know whts going wrong with it, its looking somehow challenging. I know if I will restart services it will clear the issue. but I want to konw why its happened so that I can fight with this issue in future too. I ahve attached configuration of TempDB. Please look on it and advice me if need to do anything..
Attachments
Tempdberror1.jpg (231 views, 46.00 KB)
Tempdberror.jpg (152 views, 31.00 KB)
dkschill
dkschill
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 430
There are a number of things that can cause your tempdb to grow...but the most common are costly queries.

Here is another forum post with a similar question:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0e710dcb-9046-41d0-a6af-be8e118ec7fc/tempdb-is-growing-abnormallyhow-to-check-and-find-out-what-makes-it-grow

That forum post references what looks to be a very good Microsoft article on the topic:

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


Hope this helps!
-Dane
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62270 Visits: 19102
It's also entirely possible with your workload that you need the space. There is not set size for tempdb. You need what you need for your workload.

If it grows consistently to a size, and you don't have issues with long running transactions, then you need that space.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jitendra Padhiyar
Jitendra Padhiyar
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 671
Thanks a lot to all Masters for guiding me!

I went through URL's given by you all and used below query to find out how much disk space tempDB has and how much free:

SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

And I came to know currently TempDB has 150966 MB and out of it TempDB has about 150199MB free space!!! that means TempDB currently using only around 46MB!!!!!!!

Now my concern is "How to release this free space from TempDB?" I tried to shrink file but it showing just 8MB assigned to tempdb.mdf file and .50MB to tempdb.ldf. I am confused. Please help me to release this free TempDB disk space.
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16583 Visits: 10063
TempDB will use whatever space it needs for the workload. If you have procedures that create temp tables, populate them, manipulate them, return them and the drop them, the work is done in tempdb. Table variables and cursors are also processed there, as well as any worktables created during query execution.

It grows to have whatever it needs, but it doesn't shrink itself. So, if you have a large TempDB, this means that it grew to that point because it needed the space. If the temporary objects are dropped, the space is available but not yielded back to the operating system. Normally, this is done by shrinking the database, but TempDB is not a normal database. I've tried a couple approaches to shrink a bloated TempDB before and they didn't work. The solution is to restart the SQL Server service and TempDB will shrink back to its original size...only to start growing again.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Voide
Voide
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 280
Ed Wagner (8/2/2013)
TempDB will use whatever space it needs for the workload.


I haven't been around on these forums for too long, but boy have I seen a lot of "My TempDB!!!" posts lately.


Your TempDB doesn't just grow because it feels like eating more space. It grows because it NEEDS to. If I was you I'd start by looking through SProc's, nightly jobs, any query that runs often that I didn't write myself. Look through them, you'd be surprised how in-efficiently some people can code. All it takes is one hungry query to bring your database to its knees.
Jitendra Padhiyar
Jitendra Padhiyar
Mr or Mrs. 500
Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)Mr or Mrs. 500 (504 reputation)

Group: General Forum Members
Points: 504 Visits: 671
Thanks Ed and Voide,

@Voide: You are right, I found one SP which seems to be culprit, its runnig from long time , but not blocking anything. Might it is using much space in Tempdb. Attached is the SP. I am not good at creating\editing SP. So if anyone find anything wrong in this SP pls let me know so that I can suggest my customer to edit that SP so it will not use so much space in TempDB.

@Ed: First of all Thanks for your valuable suggestion, I know it that restarting services will refresh the tempdb but each time restarting services is not proper and permanat solution. Thats why I want to know the root cause to TempDb's space eating.
Attachments
SP_updateinstallation.txt (180 views, 14.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search