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

Tempdb Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 10:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
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.





Post #1369931
Posted Monday, October 8, 2012 10:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
Points: 63, Visits: 224
Additionally , each file was set to 4gb, would it be worth extending this to 5gb and turning off autogrowth.
Post #1369948
Posted Monday, October 8, 2012 12:16 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:14 PM
Points: 91, Visits: 164,592
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
Post #1369985
Posted Monday, October 8, 2012 12:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
SQL 2008 (10.0.2351)

Not R2
Post #1369987
Posted Monday, October 8, 2012 12:26 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:14 PM
Points: 91, Visits: 164,592
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
Post #1369992
Posted Monday, October 8, 2012 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 13,481, Visits: 12,339
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)
Post #1369994
Posted Monday, October 8, 2012 12:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
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.
Post #1370003
Posted Wednesday, October 17, 2012 8:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
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?
Post #1373820
Posted Wednesday, October 17, 2012 8:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:12 AM
Points: 30, Visits: 299
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.

Post #1373861
Posted Thursday, October 18, 2012 6:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
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.
Post #1374301
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse