Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

Properly Sizing the SQL Server TempDB Database


By:   |   Read Comments (12)   |   Related Tips: 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.

Updated - 11/29/2010



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

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

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 06, 2010 - 7:06:10 AM - admin Back To Top

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 06, 2010 - 12:23:18 AM - newdba Back To Top

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

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

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

 

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 07, 2007 - 12:15:02 PM - glynne_smith Back To Top

That makes sense.....thanks


Friday, December 07, 2007 - 11:47:58 AM - admin Back To Top

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 06, 2007 - 11:29:58 AM - glynne_smith Back To Top

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?


Learn more about SQL Server tools