Tempdb

  • 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.

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

  • 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

  • SQL 2008 (10.0.2351)

    Not R2

  • 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

  • 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/

  • 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.

  • 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?

  • 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.

  • 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.

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

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