what could be done to avoid or stop from Temp Db growing bigger?


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

more ▼

asked Nov 07, 2014 at 02:11 PM in Default

avatar image

281 9 20 30

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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


  • Service Broker

  • Event Notifications

more ▼

answered Nov 07, 2014 at 02:37 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 07, 2014 at 02:11 PM

Seen: 204 times

Last Updated: Nov 07, 2014 at 03:18 PM

Copyright 2018 Redgate Software. Privacy Policy