When it comes to the monitoring of SQL Server system databases, the tempdb database is one of the most important for consideration, since it holds most of the internally created objects.. Beside some unique characteristics of the tempdb database itself (like version store, e.g. Seeing a test server grow the tempdb log file to 8 GB used out of 12 GB allocated over the course of a couple hours while load testing. The tempdb data files have almost no file space usage. if you switch your database context to tempdb, sp_spaceused works for me: select top 100 * into #temp from edlogdetail. The following query returns the total number of free pages and total free space in megabytes (MB) available in all data files in tempdb. Use [Tempdb] GO Here are some T-SQL scripts that you can use to monitor who/what is consuming space from tempdb and plan accordingly. Listing 2: Total latch wait time over the previous minute. I have used SP_SPACEUSED. SP_SPACEUSED returns the DB size in MB but have question like whether returns . Database developers and the database engine use tempdb as a store for transient data. The following actions should be taken: On the SQL Server – Open Query analyser and run: –This will show the size of the tempdb. One of the functions of TempDB is to act something like a page or swap file would at the operating system level. The tempdb is the workbench for Microsoft SQL Server and will be increased every time when the workload does not fit into the current size of TEMPDB. The temporary data does not need persistence, and tempdb provides a functional "scratchpad" for the entire SQL Server instance. Listing 7 in Jonhathan Kehayias’ article, Optimizing tempdb configuration with SQL Server 2012 Extended Events will do the trick. If a SQL Server operation is too large to be completed in memory or if the initial memory grant for a query is too small, the operation can be moved to disk in TempDB. exec sp_spaceused #temp /* name rows reserved data index_size unused The problem with this kind of query is that it is a "snapshot" of current activity. Tempdb stores data used in various active processing routines. In future, If Tempdb fills up then it is not necessary to restart SQL Server. Running DBCC OPENTRAN in tempdb, there are no open transactions for more than a few seconds. Before we get into identifying queries that use tempdb, it is very important to understand what all activities in SQL Server (both internal & user activities), which use the tempdb database. Determing the Amount of Free Space in tempdb. How to find actual space used (exact size) in TempDB (Sql Server 2000). SQL Server TempDB Overview What is the purpose of SQL Server TempDB? Tempdb is the name of a system database in Microsoft SQL Server. I'm running SQL Server 2005 and read snapshot isolation is off. If you want to test this out on your local instance, you’ll need to generate a bit of tempdb action. When filegrowth occurs, SQL Server will grow all files to the same size at the same time to ensure all files remain evenly used. Shanky has given you a pretty fine hint to check the usage of tempdb. use tempdb. sql server created intermediate resultsets as worktables in tempdb - usually for sorting purposes (usually is a sign of absent indexes/out-of-date statistics) sql server decided to pre-evaluate the resultset of table valued function and in this case it stores the data in tempdb; recreating indexes with option SORT_IN_TEMPDB = ON Auto growth for TempDB is not as simple as other user databases due to multiple data files of TempDB, especially when you have trace flags 1117 and 1118 enabled or you are on SQL Server 2016 and later. In SQL Server 2016 and later tempdb will be created with a number of data files based on the number of logical processors detected on your machine, by default. — Get input buffer for a SPID DBCC INPUTBUFFER(21) – Substitute the SPID number from above.