Tempdb

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Tempdb

Tempdb

Rate Topic

Display Mode

Topic Options

Author

Message

emile.milne

Posted Monday, October 08, 2012 10:17 AM

Valued 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

emile.milne

Posted Monday, October 08, 2012 10:37 AM

Valued 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

ib.naji

Posted Monday, October 08, 2012 12:16 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 91, Visits: 97,158

emile.milne (10/8/2012)

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

Bobby Glover

Posted Monday, October 08, 2012 12:20 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:33 AM
Points: 162, Visits: 659

SQL 2008 (10.0.2351)

Not R2

Post #1369987

ib.naji

Posted Monday, October 08, 2012 12:26 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 91, Visits: 97,158

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

Sean Lange

Posted Monday, October 08, 2012 12:28 PM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547, Visits: 8,204

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

Post #1369994

Bobby Glover

Posted Monday, October 08, 2012 12:54 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:33 AM
Points: 162, Visits: 659

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

emile.milne

Posted Wednesday, October 17, 2012 8:17 AM

Valued 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

rajaster

Posted Wednesday, October 17, 2012 8:51 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:21 AM
Points: 16, Visits: 191

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

emile.milne

Posted Thursday, October 18, 2012 6:47 AM

Valued 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 »

Permissions