SQL Clone
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
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 1197
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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40446 Visits: 38567
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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2512 Visits: 2192
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
Mr or Mrs. 500
Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)Mr or Mrs. 500 (591 reputation)

Group: General Forum Members
Points: 591 Visits: 1197
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
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 546
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 Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64553 Visits: 19118
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
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 546
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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40446 Visits: 38567
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14950 Visits: 9006
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
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 546
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