SQL SERVER – T-SQL Script to Find Details About TempDB

Two days ago I wrote article about SQL SERVER – TempDB Restrictions – Temp Database Restrictions. Since then I have received few emails asking details about TempDB. I use following T-SQL Script to know details about my TempDB. I have found this script from MSDN site. I have mentioned original source link in reference at the end of this article.
name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END AutogrowthStatus,
growth AS 'GrowthValue',
'GrowthIncrement' =
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0
AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'

Above script will return following result set.

FileName FileSizeinMB AutogrowthStatus GrowthValue GrowthIncrement
tempdev 8 Autogrowth is on. 10 Growth value is a percentage.
templog 0.5 Autogrowth is on. 10 Growth value is a percentage.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL

SQL SERVER – TempDB Restrictions – Temp Database Restrictions

While conducting Interview for my outsourcing project, I asked one question to interviewer that what are the restrictions on TempDB? Candidate was not able to answer the question. I thought it would be good for all my readers to know answer to this question so if you face this question in interview or if you meet me in interview you will be able to answer this question.

  • Adding filegroups.
  • Backing up or restoring the database.
  • Changing collation. The default collation is the server collation.
  • Changing the database owner. tempdb is owned by dbo.
  • Creating a database snapshot.
  • Dropping the database.
  • Dropping the guest user from the database.
  • Participating in database mirroring.
  • Removing the primary filegroup, primary data file, or log file.
  • Renaming the database or primary filegroup.
  • Setting the database to OFFLINE.
  • Setting the database or primary filegroup to READ_ONLY.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

#TempTable and @TempVariable are different things with different scope. Their purpose is different but highly overlapping. TempTables are originated for the storage and & storage & manipulation of temporal data. TempVariables are originated (SQL Server 2000 and onwards only) for returning date-sets from table-valued functions.

Common properties of #TempTable and @TempVariable
They are instantiated in tempdb.
They are backed by physical disk.
Changes to them are logged in the transaction log1. However, since tempdb always uses the simple recovery model, those transaction log records only last until the next tempdb checkpoint, at which time the tempdb log is truncated.

Discussion of #TempTable and @TempVariable relations with each other.
A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch it is declared in. Within its scope, a table variable can be used like a regular table. Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined. Table variables require fewer locking and logging resources. If a temporary tables are used in stored procedure, it will create separate copy of the temporary table for each user in system who makes use of that stored procedure. SQL Server identifies the different temporary tables by internally adding a numerical suffix to the name.

Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used. If the temporary tables referred to in a stored procedure are created outside the procedure, that will cause recompilation. Recompilation occurs when DECLARE CURSOR statements whose SELECT statements reference a temporary table, or DROP TABLE statements comes before other statements referencing a temporary table. In each of these cases, changing to a table variable rather than a temporary table will avoid the repeated recompilation.

Temp Tables supports non-clustered indexes and creates statistics on the query executed. This helps some query which needs stats and indexes to run faster. Temp Tables supports input or output parameters. Also they can be copied to another temp tables. Temp tables supports SELECT INTO or INSERT EXEC but Temp variables does not. Temp Table definition can be changed after it is created. Temp table can be explicitly dropped as well.

My Recommendation: Which one to use?
Simple : Choose a TempVariables as Default. If any of the following circumstances arises use TempTables.
1) Transactions needs to rollback
2) If query optimizer needs stats to run complex query
3) If result sets of one tables needed for another stored procedure like SELECT INTO or INSERT EXEC
4) Complex logic of dynamic SQL, which are not supported by TableVariables like creating Index or Constraints
5) Results set is very large (greater than 100K rows)

The bottom line is that both temporary tables are table variables are invaluable tools in your SQL Server toolbox, and you really should become familiar with both.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 Improvements in TempDB

Following are some important improvements in tempdb in SQL Server 2005 over SQL Server 2000
Input/Output traffic to TempDB is reduced as logging is improved. In SQL Server 2005 TempDB does not log “after value” everytime. E.g. For INSERT it does not log after value on log as that will be any way logged in the TempTable. Similar for DELETE as It does not have to log After value as it is not there. This is big improvement in performance in SQL Server 2005 for TempDB.

Some other improvement in File System of operating system. (I am not listing them as it does not apply to SQL concepts).

Improvement in UP latch contention. Each file in TempDB is filled up with proportion with the free spaces it reduces the latch contention in TempDB while many users are accessing TempDB.

Worktable caching is improved along with memory management and pre-allocation resources. SQL Sever 2005 caches all the temporary objects (table variables, local tables). If table is smaller than 8 MB it is cached in the TempDB system Catalog, so when it is created again it is there and reduces the work for TempDB. If it is bigger than 8 MB TempDB uses background differed drop. When large object(temporary table) is dropped in TempDB, background process drops it, so main process does not have to wait for this to execute and complete. This improves performance for the application time.

TempDB is used by following feature of SQL Server 2005:

  • Query
  • Triggers
  • Snapshot isolation and read committed snapshot (RCSI)
  • MARS
  • Online index creation
  • Temporary tables, table variables, and table-valued functions
  • LOB parameters
  • Cursors
  • Service Broker and event notification
  • XML and LOB variable
  • Query notifications
  • Database mail
  • Index creation
  • User-defined functions

Best Practices and Recommendations for TempDB in SQL Server.
Analyze the existing workload and adjust the space for projected concurrent activities in SQL TempDB.
SQL Server does not cache the temp table if it is created as part of dynamic SQL.
Perform index maintenance and update statistics on TempDB as well, even thought objects in TempDB are not permanent.
Set Auto Grow to ON for TempDB.
Instant file initialization improves the performance of auto grow operations in TempDB.
Create TempDB database on other disk drive than other database. This will improve performance for database as different disk controller will improve the performance for disk input/output.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , MS TechNet Working with tempdb in SQL Server 2005

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.

If you come across following errors in log file, please follow the direction below.
Event ID: 17052
Description: The

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow.

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to other drive.
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.
EXEC sp_helpfile

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB
along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files.
USE master
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
(NAME = templog, FILENAME = 'e:datatemplog.ldf')

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

Reference : Pinal Dave (http://blog.SQLAuthority.com) ,Microsoft Support, Database Journal.