Tempdb

If your database contains very large tables, querying or managing those tables may fill tempdb. When tempdb is full, an error is raised and the transaction is rolled back. If this will happen or not depends on how large your tempdb can get. Even if the size of tempdb isn’t limited by turning off autogrowth or by setting a max size, the disk space available is the upper limit for how big tempdb can get. Check out this blog post to see how to calculate the maximum possible size of tempdb.

Large tables can be a latent problem, since there may be no problems at all until someone, for instance, tries to sort the table in a query. SQL Server uses tempdb (as the name implies) as a temporary workspace for data, and if SQL Server can’t handle all the data in memory tempdb is used.

To avoid this problem you need to know if there are very large tables that won’t fit in tempdb when at its maximum size. Here is a script to look for tables larger than tempdb:

There is always a limit on how large tempdb can get. Even if there are no size limitations on the data files in tempdb, the limit is still the free space on disk drives. Here I’ll show how to calculate the maximum size of tempdb (data only, not log).