why i can't shrink tempdb data file
-
DBCC FREESYSTEMCACHE ('ALL')DBCC FREEPROCCACHEUSE [tempdb]GODBCC 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
-
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.
-
Excelent! with :
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE
then if I could execute Shrink successfully
-
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.
-
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. -
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?
-
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 67 (of 67 total)
You must be logged in to reply to this topic. Login to reply