The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:
- Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
- Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
- Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
- Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdbto be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
Additional details can be found at: https://msdn.microsoft.com/en-us/library/ms190768%28v=sql.110%29.aspx
A lack of available space on the tempdb could result in the following errors generated in Insight logs:
2016-06-13 14:44:12 CDT (http-nio-8080-exec-3) ERROR [com.variphy.mssql.impl.MSSQLServerConnector] Could not allocate space for object ‘dbo.SORT temporary run storage: 141829702615040’ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
This issue can be resolved by increasing the autogrowth and maximum size for tempdb.
Step 1) Login to Management Studio
Step 2) Expand “Databases” > Right click “tempdb” > select “Properties”
Step 3) Ensure “Autogrowth” is enabled and adjust settings accordingly.
