June 26, 2012 at 10:57 am
I know this topic has been covered, But i have a 284 GB tempdev file and it will not shrink. What is the easiest way to shrink it? Will bouncing SQL server resolve this? If so then wanted bouncing to be last resort. Went through SSMS to no avail and script out to no avail...Am I missing something??
June 26, 2012 at 11:08 am
Yes, bouncing the service will resolve it. If scripting the shrink did not resolve it, it may depend on the script. But you are possibly down to the last resort though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 26, 2012 at 11:14 am
Kasnut (6/26/2012)
I know this topic has been covered, But i have a 284 GB tempdev file and it will not shrink. What is the easiest way to shrink it? Will bouncing SQL server resolve this? If so then wanted bouncing to be last resort. Went through SSMS to no avail and script out to no avail...Am I missing something??
The only safe way to shrink temdb is to restart SQL Server. The problem is, since your tempdb database grew to 284GB in size, it probably will again. If this is a problem (running out of disk space on the disk drive where tempdb resides for example), then you need to start looking at the processing on your system to try and determine what is causing tempdb to grow so much. Remember, tempdb is used by all of your databases on the instance.
June 26, 2012 at 10:12 pm
Lynn Pettis (6/26/2012)
The only safe way to shrink temdb is to restart SQL Server.
i too agree to it.
Regards
Durai Nagarajan
June 28, 2012 at 6:55 am
Reboot is a pathetic solution answer for a routine situation that is nearly 100% resolvable without a reboot. I'd rather kill random spids until the shrinkfile command magically works than Reboot. Just suggesting a reboot is the "SAFE" way should make you give up your dba card. The safe way to getting fired maybe... what kind of applications are you supporting obviously not 24/7 Online type software packages if you consider reboot a "safe" anything...
Find out what is using tempdb, has open transaction, doing massive writes, spooling to tempdb whatever and kill it. Shrink the dang file and cap it if it causes you issues growing uncontrolled, put in place monitoring to determine what cause the bloating if you don't know...
June 28, 2012 at 10:01 am
thadeushuck (6/28/2012)
Reboot is a pathetic solution answer for a routine situation that is nearly 100% resolvable without a reboot. I'd rather kill random spids until the shrinkfile command magically works than Reboot. Just suggesting a reboot is the "SAFE" way should make you give up your dba card. The safe way to getting fired maybe... what kind of applications are you supporting obviously not 24/7 Online type software packages if you consider reboot a "safe" anything...Find out what is using tempdb, has open transaction, doing massive writes, spooling to tempdb whatever and kill it. Shrink the dang file and cap it if it causes you issues growing uncontrolled, put in place monitoring to determine what cause the bloating if you don't know...
Obviously you would not reboot until approval to do so. Who would wantonly reboot a server on a whim? Further more who would kill random spids. If killing a random spid is safe, it sounds like your data is not very important and a reboot would be no worse.
Flipside - why even shrink the tempdb database? Sure you may need to shrink it if out of disk space. But, if your drive is full then your 24/7 application is already halted and you are in an outage.
I agree on the monitoring of tempdb to discover usage.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 28, 2012 at 12:08 pm
thadeushuck (6/28/2012)
Reboot is a pathetic solution answer for a routine situation that is nearly 100% resolvable without a reboot. I'd rather kill random spids until the shrinkfile command magically works than Reboot. Just suggesting a reboot is the "SAFE" way should make you give up your dba card. The safe way to getting fired maybe... what kind of applications are you supporting obviously not 24/7 Online type software packages if you consider reboot a "safe" anything...Find out what is using tempdb, has open transaction, doing massive writes, spooling to tempdb whatever and kill it. Shrink the dang file and cap it if it causes you issues growing uncontrolled, put in place monitoring to determine what cause the bloating if you don't know...
And shrinking tempdb can cause corruption that could take down your instance as well.
I am not recommending a restart of SQL Server to solve a problem, I only stated that restarting SQL Server is the only safe way to shrink tempdb.
In fact, I specifically stated that tempdb could easily grow back to the same size and that what was needed was to identify what was causing tempdb to grow and determine if those processes could be modified/changed to reduce the impact on tempdb.
June 28, 2012 at 2:21 pm
thadeushuck (6/28/2012)
I'd rather kill random spids ...
Be careful, now... You could end up with SPIDs stuck in a rollback using 1 full CPU each that never resolve until you do a reboot.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2012 at 2:22 pm
Lynn Pettis (6/26/2012)
Kasnut (6/26/2012)
I know this topic has been covered, But i have a 284 GB tempdev file and it will not shrink. What is the easiest way to shrink it? Will bouncing SQL server resolve this? If so then wanted bouncing to be last resort. Went through SSMS to no avail and script out to no avail...Am I missing something??The only safe way to shrink temdb is to restart SQL Server. The problem is, since your tempdb database grew to 284GB in size, it probably will again. If this is a problem (running out of disk space on the disk drive where tempdb resides for example), then you need to start looking at the processing on your system to try and determine what is causing tempdb to grow so much. Remember, tempdb is used by all of your databases on the instance.
+1
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply