April 27, 2009 at 12:08 pm
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
tempdb29971.25 MB29219.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
April 27, 2009 at 12:17 pm
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.April 27, 2009 at 1:40 pm
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
April 27, 2009 at 1:44 pm
Is your tempdb one data file? Just tempdb.mdf?
What was the result of the shrinkfile on this file?
April 27, 2009 at 1:46 pm
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
April 27, 2009 at 1:48 pm
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.
April 27, 2009 at 1:58 pm
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.
April 27, 2009 at 2:02 pm
Are you sure there's free space in there? Perhaps there are temp objects preventing it from shrinking?
April 27, 2009 at 2:09 pm
Try running this query and see what is out there in tempdb:
select * from tempdb.sys.all_objects
where is_ms_shipped = 0;
April 27, 2009 at 2:16 pm
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(/128.,2))AS [Size]
,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2))AS [Used]
,CONVERT(NUMERIC(10,2),ROUND((-FILEPROPERTY([name],'SpaceUsed'))/128.,2))AS [Unused]
FROM [sys].[database_files]
April 27, 2009 at 2:31 pm
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(/128.,2))AS [Size]
,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2))AS [Used]
,CONVERT(NUMERIC(10,2),ROUND((-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
April 27, 2009 at 2:44 pm
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
April 27, 2009 at 5:38 pm
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.
April 28, 2009 at 1:36 am
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
May 15, 2009 at 11:40 am
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.
tempdev10006.94 15.389991.56
templog113.00 9.71103.29
tempdev2 7157.94 7.387150.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
Viewing 15 posts - 1 through 15 (of 66 total)
You must be logged in to reply to this topic. Login to reply