October 30, 2013 at 9:59 am
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%5B/quote%5D
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
November 5, 2014 at 3:10 pm
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.
June 23, 2015 at 4:36 pm
Excelent! with :
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE
then if I could execute Shrink successfully 🙂
December 14, 2015 at 11:30 am
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. 🙂
April 3, 2016 at 2:15 am
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.
April 3, 2016 at 6:04 am
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?
April 4, 2016 at 5:32 am
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.
Viewing 7 posts - 61 through 66 (of 66 total)
You must be logged in to reply to this topic. Login to reply