August 1, 2013 at 9:13 am
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..
August 1, 2013 at 9:22 am
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.
August 1, 2013 at 9:32 am
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
August 1, 2013 at 9:51 am
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..
August 1, 2013 at 9:57 am
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:
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
August 1, 2013 at 10:59 am
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.
August 2, 2013 at 5:38 am
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.
August 2, 2013 at 6:57 am
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.
August 2, 2013 at 7:55 am
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.
August 2, 2013 at 9:12 am
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.
August 5, 2013 at 6:32 am
I have shrunk TempDB by selecting it under Object Explorer, right-clicking on it, and choosing the relevant sub-menu option (SQL 2012 Express) that appears. It worked a treat, as I was able to shrink it back to whatever I wanted to. (Apologies, I'm not on the machine with SQL installed so I cannot remember the exact sub-menu option). My file expanded as I executed some poorly-construed SQL, so I was clearing up my own mistakes.
August 6, 2013 at 5:11 am
I am using SQL 2005 not SQL server 2012. Moreover, I shrinked as much as I can. At last after clients approval we have restarted services and it clears the space from TempDB. But i am very much disapointed as I am not able to find out exact culprit of the issue. Might be the SP that I attached in the previous post of mine but not sure.;-).
If anyone have good knowledge of SP Or scripting pls review the SP I attached and let me know if it creating TempDB to use more Disk space.
August 6, 2013 at 6:04 am
I looked at the stored procedure, but it doesn't look like it hits anything that hard. It looks to me like an upgrade or version management procedure for ManageSoft, a package with which I'm not familiar. A quick Bing search shows a number of packages, ranging from enterprise software licensing management to Oracle licenses to career development. I'm not familiar with any of them.
The procedure itself calls another procedure named PackageVersionAddBound, which might be the culprit, but there's no real way to tell. I would monitor the tempdb and see what people are doing in it for a while. Then you'd have a chance of narrowing things down. Just as a note, I have to believe that there's not just a single offender; most times there are multiple things to address.
August 7, 2013 at 12:53 pm
You can see some basic information for the processes responsible for the growth of TempDB by querying the default trace. The following query will show the SPID, Login, Host Name from which the request originated, and some other information for the process(es) responsible for causing TempDB to grow:
SELECT TE.name AS [EventName],T.DatabaseName, t.DatabaseID, t.NTDomainName,
t.ApplicationName, t.LoginName, t.HostName,t.SPID, t.Duration, t.StartTime, t.EndTime,
t.textdata
FROM sys.fn_trace_gettable (CONVERT(VARCHAR(150), (SELECT TOP 1 f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
where te.name ='data file auto grow' and DatabaseID=2
ORDER BY t.StartTime;
From there, you can use that information to ferret out the offending query in a couple ways.
If the growth happens frequently and activity on the server is not too busy, it might be worth it to start a trace so that you can correlate the Start Time and SPID from the default trace Auto Grow events with a specific piece of T-SQL.
Otherwise, you would have to catch the activity as it is happening, or hunt around in sys.dm_exec_query_stats to find a query
that correlates with the times TempDB was growing. Of course, that will only work if the query is still in the cache by the time you investigate.
Cheers!
August 9, 2013 at 2:16 am
In SQL 2012, when wanting manually to shrink Tempdb, I go to the entry in Object Explorer (under Databases, System databases, TempdB), then right-click on TempdB and choose Tasks - Shrink - Database or File. It is a useful way round a re-boot of SQL when some code has ballooned this file, but obviously doesn't address the underlying cause.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply