Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


why i can't shrink tempdb data file


why i can't shrink tempdb data file

Author
Message
Alex Gay
Alex Gay
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 450

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
Sunil John
Sunil John
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
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.
laacade23
laacade23
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 268
Excelent! with :

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE


then if I could execute Shrink successfully Smile
Tom Staab
Tom Staab
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
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. Smile
GK01
GK01
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
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 Sad U need to try something else.
GK01
GK01
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
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?
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7766 Visits: 8728
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search