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: Monday, August 15, 2016 8:38 AM
Points: 263, Visits: 397

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: Thursday, February 25, 2016 8:07 AM
Points: 2, Visits: 268
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: Friday, May 20, 2016 1:47 PM
Points: 65, Visits: 280
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
Posted Sunday, April 3, 2016 2:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 14, 2016 12:38 AM
Points: 3, Visits: 4
Log file was empty. Shrink released little bit space but didnt help. Restarted the service but size didnt drop. Shrink also not worked after restart, just was not releasing any space. No error in shrink. No open transactions of row versioning snapshot isolation level or such. No big temp object inside Db. So what i did thanks to someone who suggested here:

ALTER DATABASE [tempdb] MODIFY FILE (
NAME = N'tempdev',
SIZE = 2048 ); --2MB

Wow....!. it worked. but not without sql service restart after that. If you need to fix it without service restart, then............ sorry U need to try something else.

Post #1774482
Posted Sunday, April 3, 2016 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 14, 2016 12:38 AM
Points: 3, Visits: 4
But now I have same issue again with a new twist. Temp db log file hos grown to 169 GB on the disk but If i try to shrink it via SSMS UI, it says file size is 2 GB only and out of that X gb is utilized Y gb space is free.

Why so? Why SQL is behaivng like this? Can someone please help asap?
Post #1774503
Posted Monday, April 4, 2016 5:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 7,271, Visits: 8,437
ganesh.01 (4/3/2016)
But now I have same issue again with a new twist. Temp db log file hos grown to 169 GB on the disk but If i try to shrink it via SSMS UI, it says file size is 2 GB only and out of that X gb is utilized Y gb space is free.

Why so? Why SQL is behaivng like this? Can someone please help asap?


You will get more assistance if you start a new thread with all the details (not just this snippet) of what you did and what messages you're receiving when you do it.


Brandie Tarvin, MCITP Database Administrator

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 #1774627
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse