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


why i can't shrink tempdb data file


why i can't shrink tempdb data file

Author
Message
Mad-Dog
Mad-Dog
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 1183
Adam Bean (4/27/2009)
Steve Jones - Editor (4/27/2009)
Are you sure there's free space in there? Perhaps there are temp objects preventing it from shrinking?


Good call, run this to verify:


USE [tempdb]
SELECT
   [name]
   ,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2))                                 AS [Size]
   ,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2))            AS [Used]
   ,CONVERT(NUMERIC(10,2),ROUND(([size]-FILEPROPERTY([name],'SpaceUsed'))/128.,2))      AS [Unused]
FROM [sys].[database_files]





results

name Size Used Unused

tempdev 29221.25 4.19 29217.06

templog 750.00 96.83 653.17
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37935
Run this and let us know the values for size.


use [tempdb]
go
select * from sys.database_files
select * from sys.master_files where database_id = 2
go



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Adam Bean
Adam Bean
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1215 Visits: 2160
Yeah, lots of free space there ... but keep in mind, a data file will grow to it's size for a reason. Shrinking usually only results in the file growing back. Regardless, if you need to shrink, you need to shrink. I wouldn't shrink it all the way down ... usually good to leave some room in the tempdb. Actually you should match the amount of data files to the amount of CPU's for better performance.

Anyways, run this:

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 4096)
GO



Then re query your size. If nothing has changed, check the error log (sp_readerrorlog). See if there is anything about a wait or stall.

---
SQLSlayer
Making SQL do what we want it to do.
Mad-Dog
Mad-Dog
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 1183
when i run the DBCC SHRINKFILE i get this error message

DBCC SHRINKFILE: Page 1:3740312 could not be moved because it is a work table page.
but the free space on the tempdb is 29GB.
how can i know what i keeping my tempdb busy,so i can't shrink it?
sp_spaceused =

database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
tempdb 29971.25 MB 29219.73 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
1560 KB 648 KB 712 KB 200 KB
ganci.mark
ganci.mark
Right there with Babe
Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)

Group: General Forum Members
Points: 726 Visits: 537
I am having the same trouble. My tempdb files will not shrink despite the fact that only a small portion is actually in use. There are no open transactions.
tempdev   10006.94    15.38   9991.56
templog   113.00    9.71   103.29
tempdev2 7157.94    7.38   7150.56

Shrinkdatabase, shrinkfile do not work.

This query :
select * from tempdb.sys.all_objects
where is_ms_shipped = 0;
Did produce 74 objects.



It seems the only solution will be to bounce the instance.
Does anyone have any other suggestions without restarting SQL Server?

Thanks
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36040 Visits: 18736
What is tempdb set at for a size? I wonder if you can set it lower and restart SQL.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
ganci.mark
ganci.mark
Right there with Babe
Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)

Group: General Forum Members
Points: 726 Visits: 537
use [tempdb]
go
select * from sys.database_files
select * from sys.master_files where database_id = 2
go

Produces two different "sizes" in pages

select * from sys.database_files
gives me "size" on disk. (free +used)

select * from sys.master_files where database_id = 2
gives me "size" used
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37935
Unless you are experiencing disk space issues on the disk where tempdb resides, I'd leave it alone. It grew to whatever size it is for a reason. If you shrink it, it will just have to grow again if more space is needed later. This is an expensive operation and can impact your systems response times.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7766 Visits: 8728
I agree with Lynn. Unless you're experiencing serious space issues, leave it alone. I've had this problem several times myself and usually it's a valid process. Only on rare occasions was it a runaway process that wouldn't release the connections.

If you are having disk or runaway process issues, try killing all the users on the instance (or on the database which is actually using the space). This may take a while as there's rollback which needs to be done. Then try the shrink again.

Another option is to add another data file on another disk, make it the default, and let TempDB finish what it's trying to do.

And, if all else fails, restarting the services really is the only way to go.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
ganci.mark
ganci.mark
Right there with Babe
Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)

Group: General Forum Members
Points: 726 Visits: 537
In my case it was a one time query that caused the tempdb files to grow so I did want to reclaim the 19GB that they were holding. I restarted SQL server and they cleared as expected.
There should be a way to do it with restarting.
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