February 21, 2012 at 9:38 pm
hi all,
i have tried all the options(except server reboot) mentioned in this thread but still my tempdb size didn't reduce. we are maintaing tempdb in separate drive and it has occupied full drive... out of 20GB of total drive size it has left only 400MB now.... well the used space is not more than 1GB but the file size never came back to 1GB of size i have tried shrinkfile/database, active/long running transaction lookup, dbcc updateusage,freecache,freebuffer oter options as well, have tried to shrink file through GUI too but STILL NO LUCK... the tempdb file is still 20GB size....
Are there any other things i can check other than server reboot?? :exclamationmark:
February 22, 2012 at 12:26 am
Don't shrink?
As I said, trying to shrink TempDB with the system in use is documented to be able to cause corruption, so it's really not a good idea.
Why is a 20GB tempDB file a problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2012 at 2:27 am
As per normal situation 20GB is not such huge file to worry but here in this case the drive itself has total space of 20GB and this one is eating entire space..... and they dont want to increase the size of the drive.....
February 22, 2012 at 6:47 am
If TempDB is reaching 20GB then it needs to be 20GB for the workload on the server. Your options are to increase drive space, add a second tempDB file on another drive (dedicated, not shared with other stuff), or start looking through the queries running and see what you can do to reduce the usage of TempDB. No magic solution
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2012 at 7:53 am
As Gail has stated.. if TEMPDB has grown to 20gig then even if you shrink it there is a 99.99999% chance that it will grow to that size again. There is some process that requires 20 gig worth of TEMP space. Either temp tables, sorting or if you rebuild indexes and specify SORT in TEMPDB. If the drive is almost full you are running the risk of causing some process to fail in the future if TEMPDB needs to grow bigger. Your options are to create another file for TEMPDB on another lightly used drive or move all of TEMPDB files to another larger lightly used drive.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply