August 3, 2015 at 6:39 am
I have a problem with TEMPDb. i have configured the tempdb with 30Gb space with initial size we have total 30+Gb disk space. now we have total 500MB free space is available.
I have changed the intial size to 2 gb with 10% growth. and checked 29Gb free space is available in TEMPDB but when i shrink the database that space is not realsing
Please help how can i get 29Gb free space.
August 3, 2015 at 6:43 am
after setting the tempdb file sizes you'll need to restart the sql server service
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 3, 2015 at 6:52 am
Is there any other way to fix this issue because critical applications are running with HA on this server so we wont get the downtime.
August 3, 2015 at 4:26 pm
New persopn (8/3/2015)
I have a problem with TEMPDb. i have configured the tempdb with 30Gb space with initial size we have total 30+Gb disk space. now we have total 500MB free space is available.I have changed the intial size to 2 gb with 10% growth. and checked 29Gb free space is available in TEMPDB but when i shrink the database that space is not realsing
Please help how can i get 29Gb free space.
Do not use percentage for growth. Use a fixed size. Also, what's wrong with having a 30GB tempdb if that's what it occasionally needs to be? If tempdb is on the same disk(s) as the rest of the MDF/LDF files, it would be helpful for you to move it to a separate drive.
--Jeff Moden
August 4, 2015 at 3:05 am
Hi,
If a restart is not possible try cleaning some buffer elements that may be using the tempdb and preventing the shrink operation. Please, let us no if it worked.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
August 4, 2015 at 3:59 am
JManuelN (8/4/2015)
Hi,If a restart is not possible try cleaning some buffer elements that may be using the tempdb and preventing the shrink operation. Please, let us no if it worked.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DO NOT DO THIS.
August 4, 2015 at 4:15 am
To elaborate on above - this will remove all data, query plans etc from RAM, so your SQL server will have to reload data from disk into RAM, calculate new query plans for every query/proc/function. In short, performance is going to tank for a while.
I'm also pretty sure it won't help at all.
The DBCC SHRINKFILE command should allow you to shrink tempdb.
I wouldn't recommend going as low as 1GB though - you'll almost certainly need more during normal operation, and definitely will for some maintenance operations.
Do what you can for now, get some scheduled maintenance planned to add more disks & move tempdb away from the other database files.
August 4, 2015 at 4:17 am
Gazareth (8/4/2015)
JManuelN (8/4/2015)
Hi,If a restart is not possible try cleaning some buffer elements that may be using the tempdb and preventing the shrink operation. Please, let us no if it worked.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DO NOT DO THIS.
Sorry for the misdirection if I was wrong.
This other thread talk about the same subject and mention this method. It is true that freeing buffer pool will affect your production system almost as it was rebooted. I suggest using the previous thread for any further question.
http://qa.sqlservercentral.com/Forums/Topic705182-146-1.aspx
August 4, 2015 at 10:51 am
Thank you all for your suggestions I checked with all possibilities but doesn't help so I am going for additional space
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply