Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tempdb


Tempdb

Author
Message
emile.milne
emile.milne
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 224
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.
emile.milne
emile.milne
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 224
Additionally , each file was set to 4gb, would it be worth extending this to 5gb and turning off autogrowth.
ib.naji
ib.naji
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 164988
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.

I believe in Codd
... and Thinknook is my Chamber of Understanding
Bobby Glover
Bobby Glover
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 751
SQL 2008 (10.0.2351)

Not R2
ib.naji
ib.naji
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 164988
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.

I believe in Codd
... and Thinknook is my Chamber of Understanding
Sean Lange
Sean Lange
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: 16539 Visits: 16992
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Bobby Glover
Bobby Glover
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 751
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
emile.milne
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 224
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
rajaster
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 351
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
emile.milne
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 224
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.
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