what are the steps to be followed when dealing with Temp Db issues, for instance.. My temp db is growing bigger because of a lot of open transactions and long running queries, apart from creating secondary files in another drive, what other things can be done. Can i shrink the Temp Db files if so,is it good idea.
And what can i possibly do when not able to shrink the Temp Db files because of open transactions?
Thanks in Advance
asked Nov 07, 2014 at 02:11 PM in Default
TempDB is the temporary 'working area' for SQL Server. If you can't manage the amount of space that is needed, then maybe look at what is using the space. Look at your code and try and reduce the amount of tempdb space needed for objects such as:
You might find that making better (or reduced) use of those kind of objects will also improve performance.
Look at execution plans and see where you are heavily using :
Tempdb is used when version stores are required:
Some features of SQL Server also rely heavily on tempdb, maybe you could review your usage of them: