Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

why i can't shrink tempdb data file Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 9:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:05 AM
Points: 248, Visits: 343

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 1000)


I found this information here: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/2a00c314-f35e-4900-babb-f42dcde1944b


Thanks Erik,

This worked for me. I have a new clustered 2008R2 server and the log file was full when I got it. None of the scheduled backups would work as it had no space left on the TempDB drive.

Regards,

Alex
Post #1509852
Posted Wednesday, November 5, 2014 3:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 26, 2015 1:19 PM
Points: 33, Visits: 364
Run

DBCC SHRINKFILE (N'tempdev' , 50) -- this will bring the data-file size to 50% of current size (you will not see the file size is decreasing) below command will do it.

Now run below command to set 50% of the current size

(my initial size was 1000MB, i shrink the file to 50% and now issuing below command to set it)

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 500MB )
GO


After this command you will see the file size has reduced.
Post #1631200
Posted Tuesday, June 23, 2015 4:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 27, 2016 10:47 AM
Points: 2, Visits: 267
Excelent! with :

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE


then if I could execute Shrink successfully :)
Post #1697141
Posted Monday, December 14, 2015 11:30 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 5:34 PM
Points: 65, Visits: 264
Even though this thread started 6.5 years ago, it continues to help people. Earlier today, I started searching for answers because I couldn't shrink data files in tempdb in a dev environment. The hard drive was almost full, so I had to do something, but I didn't want to have to restart the instance. I found Mike Good's reply to this thread here regarding snapshot and read committed snapshot:
http://www.sqlservercentral.com/Forums/Topic705182-146-4.aspx#bm821775

I didn't think any database in that instance was using one of those, but sure enough one of them was using snapshot isolation. After checking with those developers to ensure it wouldn't be an issue, I temporarily turned off that option, shrunk the data files a little bit, and then turned the option back on.

Thanks, Mike. And thank you, Red Gate, for maintaining this site. :)
Post #1745158
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse