Tempdb

Question

Post reply

Tempdb

emile.milne

Default port

Points: 1407
More actions
October 8, 2012 at 10:17 am

#282806

Hi I have a tempdb that is 45gb in size. When I go to shrink db it says there is 99% free.
When I go to shrink file,( I have multiple file groups) each file reports less than 4% free. How can this be if the db is 99% empty. 3 of the files have autogrown by 10% today.
Any ideas why the mismatch in figures.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

emile.milne Default port Points: 1407 More actions · Answer 1

Additionally , each file was set to 4gb, would it be worth extending this to 5gb and turning off autogrowth.

Thinknook SSCrazy Points: 2531 More actions · Answer 2

emile.milne (10/8/2012)
Hi I have a tempdb that is 45gb in size. When I go to shrink db it says there is 99% free.
When I go to shrink file,( I have multiple file groups) each file reports less than 4% free. How can this be if the db is 99% empty. 3 of the files have autogrown by 10% today.
Any ideas why the mismatch in figures.

Depending on your version of SQL Server, this could be an issue of inaccurate values being reported.

Details can be found at: http://blogs.msdn.com/b/ialonso/archive/2012/10/08/inaccurate-values-for-currently-allocated-space-and-available-free-space-in-the-shrink-file-dialog-for-tempdb-only.aspx

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].

I believe in Codd
... and Thinknook is my Chamber of Understanding

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 3

Bobby Glover

SSCarpal Tunnel

Points: 4143

October 8, 2012 at 12:20 pm

#1546871

SQL 2008 (10.0.2351)

Not R2

Thinknook SSCrazy Points: 2531 More actions · Answer 4

Bobby Glover (10/8/2012)
SQL 2008 (10.0.2351)
Not R2

Quoting from the article:

"Up until the version of SMO that comes with SQL Server 2008 R2, the Size property of an instance of the DataFile class, was being populated with the value returned in the size column of the corresponding row from the master.sys.master_files system table."

Issue is apparently resolved in 2012.

Its easy to check, just query the underlying tables mentioned in the article and see if you get a match.

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].

I believe in Codd
... and Thinknook is my Chamber of Understanding

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Don't shrink tempdb. You are asking for problems doing that. You can end up causing yourself all sorts of problems.

Here is one link that refers to the issues.

http://support.microsoft.com/kb/307487

http://sirsql.net/blog/2011/5/18/dont-shrink-tempdb.html

Search around for explanations on shrinking tempdb. It is very bad idea.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Bobby Glover SSCarpal Tunnel Points: 4143 More actions · Answer 6

I wasn't going to shrink the TEMPDB, I would never shrink a db in production especially a sys db.

I have queried the tables it seems that the 99% free reported by the database is bogus. The files seem to be full with data around 3gb. For now I have set the files to Autogrow may need to restart the service to resolve for now.

Can;t leave auto grow to none as it may result in a loss of service.

Thanks for your help I thought I was going crazy. If you have anything more please post.

emile.milne Default port Points: 1407 More actions · Answer 7

How do I find out what is causing the Internal_objects size to grow in tempdb.

I have dbcc running each night and update stats runs each night. I would expect tempdb to calim this space back when it has completed.

Is there a way to monitor the tempdb internal objects or view/list them?

rajaster Ten Centuries Points: 1228 More actions · Answer 8

I have been experiencing similar problems:

found the following useful:

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

I'm still going through this. but its a long ardeous task. My tempdbs grew from 25gb to 33gb. But just bare in mind that it could be rebuilding of indexes over night thats the cause and in my case I suspect it is as the databases contains approx 200 GB of data.

emile.milne Default port Points: 1407 More actions · Answer 9

Thanks, I'm getting closer to finding the issue.

When I execute

select * from sys.dm_db_task_space_usage

where internal_objects_alloc_page_count <> 0

Session 10 and 14 have a very high number of internal_objects.

Activity monitor show system events any more. I hate AM in SQL 2008.

Does anyone feel it hasn't improved or is it just me.

Mine seems to be service broker.

Now how to fix the thing or is that how it generally works.