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 ««12345»»»

why i can't shrink tempdb data file Expand / Collapse
Author
Message
Posted Monday, April 27, 2009 2:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:07 AM
Points: 289, Visits: 969
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
Post #705328
Posted Monday, April 27, 2009 2:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:58 PM
Points: 22,999, Visits: 31,481
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




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)
Post #705341
Posted Monday, April 27, 2009 5:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:53 AM
Points: 1,145, Visits: 1,932
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.

Post #705435
Posted Tuesday, April 28, 2009 1:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:07 AM
Points: 289, Visits: 969
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
Post #705556
Posted Friday, May 15, 2009 11:40 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:26 PM
Points: 656, Visits: 467
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
Post #718143
Posted Friday, May 15, 2009 11:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:32 AM
Points: 33,055, Visits: 15,165
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
Post #718147
Posted Friday, May 15, 2009 11:52 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:26 PM
Points: 656, Visits: 467
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

Post #718153
Posted Sunday, May 17, 2009 9:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:58 PM
Points: 22,999, Visits: 31,481
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.



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)
Post #718794
Posted Monday, May 18, 2009 5:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 7,128, Visits: 6,291
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #718939
Posted Monday, May 18, 2009 6:15 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:26 PM
Points: 656, Visits: 467
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.
Post #718970
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse