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 12:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:47 AM
Points: 290, Visits: 971
Hi,

my tempdb database is 30GB(datafile).
i try to shrink it with all those options but no luck.

use tempdb
go
DBCC SHRINKFILE (tempdev,5000)
go
DBCC SHRINKFILE (tempdev,truncateonly)
go
DBCC SHRINKDATABASE (tempdb,5000)
--answer
DBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
sp_spaceused
--answer
tempdb 29971.25 MB 29219.77 MB
DBCC OPENTRAN
--answer
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

i run this as well with no luck

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
dbcc shrinkfile (tempdev,5000)
go

how i can shrink the file without restart the sql server services?

THX
Post #705182
Posted Monday, April 27, 2009 12:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
Check http://support.microsoft.com/kb/307487 ... this is why I love Microsoft's KB

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #705194
Posted Monday, April 27, 2009 1:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:47 AM
Points: 290, Visits: 971
this article not help me because i already did what is writing there and it's not working.
what else i can do to reduce the data file?(with no sql restart)

THX
Post #705273
Posted Monday, April 27, 2009 1:44 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 12:57 PM
Points: 33,206, Visits: 15,361
Is your tempdb one data file? Just tempdb.mdf?

What was the result of the shrinkfile on this file?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #705277
Posted Monday, April 27, 2009 1:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:47 AM
Points: 290, Visits: 971
this article not help me because i already did what is writing there and it's not working.
what else i can do to reduce the data file?(with no sql restart)

THX
Post #705282
Posted Monday, April 27, 2009 1:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:47 AM
Points: 290, Visits: 971
Steve Jones - Editor (4/27/2009)
Is your tempdb one data file? Just tempdb.mdf?

What was the result of the shrinkfile on this file?


yse only one file.
the results are the nothing happen,it's not shrink the file,the file stay as is.
Post #705286
Posted Monday, April 27, 2009 1:58 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
Mad dog, how big is the file? How much activity against tempdb? Sometimes a file won't shrink due to open transactions and will do so once it is idle. You may see some indication of this in the error log.

---
SQLSlayer
Making SQL do what we want it to do.

Post #705302
Posted Monday, April 27, 2009 2:02 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 12:57 PM
Points: 33,206, Visits: 15,361
Are you sure there's free space in there? Perhaps there are temp objects preventing it from shrinking?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #705308
Posted Monday, April 27, 2009 2:09 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:34 PM
Points: 23,343, Visits: 32,079
Try running this query and see what is out there in tempdb:

select * from tempdb.sys.all_objects
where is_ms_shipped = 0;




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 #705314
Posted Monday, April 27, 2009 2:16 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
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]



---
SQLSlayer
Making SQL do what we want it to do.

Post #705319
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse