Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TempDB Full...!! Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 9:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:53 AM
Points: 209, Visits: 573
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..
Post #1479988
Posted Thursday, August 1, 2013 9:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:38 AM
Points: 1,392, Visits: 646
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.
Post #1479991
Posted Thursday, August 1, 2013 9:32 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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
Post #1480000
Posted Thursday, August 1, 2013 9:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:53 AM
Points: 209, Visits: 573
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..

  Post Attachments 
Tempdberror1.jpg (67 views, 46.50 KB)
Tempdberror.jpg (52 views, 31.46 KB)
Post #1480008
Posted Thursday, August 1, 2013 9:57 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
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
Post #1480010
Posted Thursday, August 1, 2013 10:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Saturday, December 20, 2014 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #1480038
Posted Friday, August 2, 2013 5:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:53 AM
Points: 209, Visits: 573
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.
Post #1480338
Posted Friday, August 2, 2013 6:57 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:36 AM
Points: 4,625, Visits: 4,085
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
Post #1480365
Posted Friday, August 2, 2013 7:55 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:18 PM
Points: 30, 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.
Post #1480409
Posted Friday, August 2, 2013 9:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:53 AM
Points: 209, Visits: 573
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.


  Post Attachments 
SP_updateinstallation.txt (52 views, 14.25 KB)
Post #1480458
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse