asked
what could be done to avoid or stop from Temp Db growing bigger?
Hi, 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
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: - `#` local temporary tables - `##` global temporary tables - cursors - table-valued functions - @ table variables 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 : - SORT operations - HASH joins and HASH aggregates - intermediate results spooling Tempdb is used when version stores are required: - row versioning - snapshot isolation - triggers - MARS - online index rebuilds Some features of SQL Server also rely heavily on tempdb, maybe you could review your usage of them: - index maintenance (using SORT_IN_TEMPDB) - AlwaysON readable secondaries - DBCC CHECK - Service Broker - Event Notifications
14 People are following this question.
