Properly Sizing the SQL Server TempDB Database

By:   |   Comments (12)   |   Related: More > System Databases


Problem

Properly sizing the TempDB database has recently surfaced with some new customers and has also been a thread posted in the MSSQLTips forums.  Here is the general scenario: On all of my SQL Server instances (development, test and production) the TempDB database is configured for auto growth for the database and transaction log.  During a recent performance tuning engagement I learned a lot about how TempDB is being used in my environment.  It was brought to my attention how much the TempDB database is being used by our applications.  I had no idea our core business applications are using the TempDB database so heavily.  In addition, I did not know how large the TempDB database and transaction log have grown.  As such, I know I cannot change the applications in short order so I need to get a handle on how to resolve these issues and size the TempDB database correct.  How should I do so? 

Solution

On many SQL Server instances the TempDB database is set to default configurations which I believe are an 8 MB database and a 1 MB transaction log with unrestricted growth for both the database and the log file.  As compared to other environments where TempDB probably started with default configurations and is now tens of gigabytes in size unknown to the IT team.  TempDB is a unique database for a number of reasons which include:

  • The database only stores transient data such as
    • Temporary tables (#temptable or ##temptale)
    • Table variables
    • Cursors
    • Work tables
  • Row versioning which previously occurred in the user defined database transaction log or to support new features such as Multiple Active Result Sets (MARS)
  • Create or rebuild indexes sorted in TempDB

With TempDB potentially playing such a large role it is necessary to really understand when the TempDB database grows and then start to dig into the processing during that time to see how it can be tuned in order to improve the process.  As such, below outlines a process (table, stored procedure and SQL Server Job) to capture the TempDB utilization in order to have the information necessary to start the investigation.  So let's jump into code that can capture the needed data:

Table - Historical TempDB Database and Log File Records

CREATE TABLE [dbo].[TempDBFileSize] (
[TFSID] [int] IDENTITY (1, 1) NOT NULL ,
[FileID] [int] NULL ,
[File_Logical_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type_Desc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Default_File_SizeMB] [int] NULL ,
[ActualKB] [int] NULL ,
[ActualMB] [int] NULL ,
[File_MaxSize] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[File_Growth] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Growth_Type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Physical_File_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCaptured] [datetime] NULL 
) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[TempDBFileSize] WITH NOCHECK ADD 
CONSTRAINT [PK_TempDBFileSize] PRIMARY KEY CLUSTERED 
(
[TFSID]
) ON [PRIMARY] 
GO

Stored Procedure - Capture the TempDB Database and Log File Space Used

CREATE PROCEDURE dbo.spTempdbFileSize
AS
/*
------------------------------------------------------
-- Object Name: dbo.spTempdbFileSize
-- Project: SQL Server TempDB Sizing
-- Business Process: SQL Server Capacity Planning
-- Purpose: Capture the TempDB growth
-- Detailed Description: Capture the TempDB size, name, file, etc for the TempDB files
-- Database: TempDB
-- Dependent Objects: dbo.TempDBFileSize
-- Called By: Admin - TempDB Sizing
-- Upstream Systems: None
-- Downstream Systems: None
-- 
------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
------------------------------------------------------
-- 001 | N\A | 12.02.2007 | Edgewood | Original code
-- 002 |     | 03.30.2009 | Penberth | Modified the code to get the actual size
--                                     of the tempdb alongside the the default sizes.
--                                     Added the ActualKB and ActualMB columns and 
--                                     renamed the [File_SizeMB] to [Default_File_SizeMB]
--
*/
SET NOCOUNT ON
INSERT INTO dbo.TempDBFileSize (FileID, File_Logical_Name, State_Desc, Type_Desc,
[Default_File_SizeMB], ActualKB, ActualMB, File_MaxSize, File_Growth, Growth_Type,
Physical_File_Name, DateCaptured)
SELECT File_ID,
MasterTbl.[Name],
MasterTbl.State_Desc,
MasterTbl.Type_Desc,
(MasterTbl.[Size] * 8)/1024 AS 'File_SizeMB',
(TempTbl.[size]*8) AS ActualKB, 
(TempTbl.[size]*8)/1024 as ActualMB,
File_MaxSize = CASE 
WHEN MasterTbl.[Max_Size] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Max_Size] = -1 THEN 'UnlimitedGrowth'
WHEN MasterTbl.[Max_Size] = 268435456 THEN 'TLogMax'
ELSE CAST((MasterTbl.[Max_Size] * 8)/1024 AS varchar(10)) END,
File_Growth = CASE 
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 0 
THEN CAST((MasterTbl.[Growth]* 8)/1024 AS varchar(10))
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 1 
THEN CAST(MasterTbl.[Growth] AS varchar(10))
ELSE 'Unknown' END,
Growth_Type = CASE
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[is_percent_growth] = 0 THEN 'MegaBytes'
WHEN MasterTbl.[is_percent_growth] = 1 THEN 'Percentage'
ELSE 'Unknown' END,
MasterTbl.[Physical_Name],
GETDATE() AS 'DateCaptured'
FROM Master.sys.master_files MasterTbl (NOLOCK) 
LEFT OUTER JOIN TEMPDB.SYS.SYSFILES TempTbl (NOLOCK) 
 ON MasterTbl.[Physical_Name] = TempTbl.[filename]
WHERE Database_ID = 2
SET NOCOUNT OFF
GO

SQL Server Job - 6 Hour Interval to Collect the TempDB Database and Log File Space Used

BEGIN TRANSACTION 
DECLARE @JobID BINARY(16) 
DECLARE @ReturnCode INT 
SELECT @ReturnCode = 0 
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'Admin - TempDB Sizing') > 0 
PRINT N'The job "Admin - TempDB Sizing" already exists so will not be replaced.'
ELSE
BEGIN 

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Admin - TempDB Sizing', @owner_login_name = N'sa', @description = N'06.16.2007 - ES - Capture the TempDB growth to determine the needed size for the TempDB database', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'EXEC dbo.spTempdbFileSize', @command = N'EXEC dbo.spTempdbFileSize', @database_name = N'TestTest', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Daily Every 6 Hours', @enabled = 1, @freq_type = 4, @active_start_date = 20070524, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 6, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION 
GOTO EndSave 
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

Sample Query

SELECT * 
FROM [dbo].[TempDBFileSize];
GO
Next Steps
  • When you are faced with determining the sizing for your TempDB database, consider these scripts as a means to capture the needed storage requirements.  This information should serve as a guide for determining the storage for the TempDB database.  However, you need to consider your local knowledge of your applications, business processes and future growth to determine the final configurations. 
  • Depending on the application and maintenance related tasks, TempDB can be used heavily so sizing the database properly could become mission critical based on how the database is used.
  • It is also worth your time to review the code running during the time periods when TempDB is growing rapidly.  If this code can be tuned, perhaps the storage and IO requirements could be minimized.
  • For information on how to size just the database transaction logs, please reference - Monitoring transaction log space.
  • If you suspect IO problems related to TempDB, consider this tip as a point of reference to configure your disk drives - Hard Drive Configurations for SQL Server.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Saturday, July 14, 2012 - 2:54:24 PM - Jeremy Kadlec Back To Top (18522)

Rajasekhar,

I would review this tip:

http://www.mssqltips.com/sqlservertip/2097/how-to-shrink-the-transaction-log-file-in-sql-server-2008/

HTH.

Thank you,
Jeremy Kadlec


Friday, July 13, 2012 - 2:19:06 AM - Rajasekhar Back To Top (18487)

Hi james,

Some times we are unable to shrink the Database logs.Can you plz share information about what we are going to do in that situations??I might hope that it will be most useful for all DBA's

Thanks & Regards,

Rajasekhar Reddy


Monday, November 29, 2010 - 11:48:42 AM - Jeremy Kadlec Back To Top (10400)

JimPen,

Thank you for the feedback.

The code has been updated in the tip.

Thank you,
Jeremy Kadlec


Wednesday, November 24, 2010 - 10:31:50 AM - Stephen Back To Top (10396)

HI not sure if you still see these, 

 

Ive ran the scripts but the results are always File_SizeMB 8 (default starting size) and Log file 0 )im guessing default starting size. 

Where as at present they are actualyl 4-5gb and 2gb each. 

Am i missing something?

 

 


Saturday, March 6, 2010 - 7:06:10 AM - admin Back To Top (5020)

Change this part of the code that creates the job step:

@database_name = N'TestTest'

to the name of the database you are using to store the results.


Saturday, March 6, 2010 - 12:23:18 AM - newdba Back To Top (5017)

Currently my TempDB is set to Autogrow and per best practices it seems I should have multiple TEMPDB's based upon CPUs/Core. Is this correct? Also I was unable to complete Step 3 of the code given in this tip where it creates the job. I was able to create the table and procedure without issue. I receive the following error when creating the job per the code given:

Msg 14262, Level 16, State 1, Procedure sp_verify_jobstep, Line 272
The specified @database_name ('TestTest') does not exist.

 Any help would be appreciated as I am not a dba but just trying to determine optimal tempdb size.


Monday, March 30, 2009 - 6:30:25 AM - jimpen Back To Top (3095)

I noticed that it was only picking up the default size of the  tempdb, not the actual. So I modified your table and code as below to get the the real sizes.

CREATE PROCEDURE dbo.CP_TempdbFileSize
AS

/*
----------------------------------------------------------------------------
-- Object Name: dbo.CP_TempdbFileSize
-- Project: SQL Server TempDB Sizing
-- Business Process: SQL Server Capacity Planning
-- Purpose: Capture the TempDB growth
-- Detailed Description: Capture the TempDB size, name, file, etc for the TempDB files
-- Database: TempDB
-- Dependent Objects: dbo.TempDBFileSize
-- Called By: Admin - TempDB Sizing
-- Upstream Systems: None
-- Downstream Systems: None
--
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 12.02.2007 | Edgewood | Original code
-- 002 | | 03.30.2009 | Penberth | Modified the code to get the actual size
-- of the tempdb alongside the the default sizes.
-- Added the ActualKB and ActualMB columns and
-- renamed the [File_SizeMB] to [Default_File_SizeMB]
--
*/

SET NOCOUNT ON

INSERT INTO dbo.TempDBFileSize (FileID, File_Logical_Name, State_Desc, Type_Desc,
[Default_File_SizeMB], ActualKB, ActualMB, File_MaxSize, File_Growth, Growth_Type,
Physical_File_Name, DateCaptured)

SELECT File_ID,
MasterTbl.[Name],
MasterTbl.State_Desc,
MasterTbl.Type_Desc,
(MasterTbl.[Size] * 8)/1024 AS 'File_SizeMB',
(TempTbl.[size]*8) as ActualKB,
(TempTbl.[size]*8)/1024 as ActualMB,
File_MaxSize = CASE
WHEN MasterTbl.[Max_Size] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Max_Size] = -1 THEN 'UnlimitedGrowth'
WHEN MasterTbl.[Max_Size] = 268435456 THEN 'TLogMax'
ELSE CAST((MasterTbl.[Max_Size] * 8)/1024 AS varchar(10))
END,
File_Growth = CASE
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 0 THEN CAST((MasterTbl.[Growth]* 8)/1024 AS varchar(10))
WHEN MasterTbl.[Growth] > 0 AND MasterTbl.[is_percent_growth] = 1 THEN CAST(MasterTbl.[Growth] AS varchar(10))
ELSE 'Unknown'
END,
Growth_Type = CASE
WHEN MasterTbl.[Growth] = 0 THEN 'NoGrowth'
WHEN MasterTbl.[is_percent_growth] = 0 THEN 'MegaBytes'
WHEN MasterTbl.[is_percent_growth] = 1 THEN 'Percentage'
ELSE 'Unknown'
END,
MasterTbl.[Physical_Name],
GETDATE() AS 'DateCaptured'
FROM Master.sys.master_files MasterTbl (NOLOCK)
LEFT OUTER JOIN TEMPDB.SYS.SYSFILES TempTbl (NOLOCK)
ON MasterTbl.[Physical_Name] = TempTbl.[filename]
WHERE Database_ID = 2

Monday, December 10, 2007 - 7:05:31 PM - admin Back To Top (151)

APrato,

Thanks so much!  That is a great point!

Thank you,
The MSSQLTips.com Team


Monday, December 10, 2007 - 2:58:12 PM - aprato Back To Top (149)

 

One thing I'd like to add....In the past, I have avoided placing tempdb on a RAID-0.  Though tempdb doesn't require the fault tolerance of other RAIDs,  if tempdb is on a RAID-0 and the disk fails,  SQL Server will go down.  Tempdb is required for SQL Server.


Friday, December 7, 2007 - 12:15:02 PM - glynne_smith Back To Top (148)

That makes sense.....thanks


Friday, December 7, 2007 - 11:47:58 AM - admin Back To Top (147)

Glynne_smith,

That is a great question!  For many of the hard drive configurations in the Hard Drive Configurations for SQL Server tip the TempDB database and log file would reside on the System Database disk drive.

One item that was not covered in that tip is actually splitting the TempDB database (and transaction log) to a separate RAID 1 set when the TempDB database is used frequently.  Depending on the application, TempDB may or may not be used heavily.  For the circumstances when it is used heavily, then splitting the TempDB database (and transaction log) may be a performance improvement.

Let us know if this answers your question.

Thank you,
The MSSQLTips.com Team


Thursday, December 6, 2007 - 11:29:58 AM - glynne_smith Back To Top (144)

One question. The documentation, even the link recommended on 'Hard Drive Configurations for SQL Server' always say;

Drive X - System Database data files

Drive Y - User Database data files

Drive Z - Transaction log files.

 Where do the log files for TEMPDb go? X or Z?















get free sql tips
agree to terms