How to resize TempDB in SQL Server

SQL Server Tempdb is used to store temporary objects. By default the initial size of the tempdb is too small for a production database engaged in any significant enterprise activities, therefore it should be sized accordingly on setup.

Failure to do so will have a negative performance impact when the database is first put into operation as it will need to grow to a more fitting size. Worse still, every time SQL Server is restarted the  tempdb will be recreated with its initial size. So SQL Server has to initiate autogrowth steps to grow the database file again and performance will be impacted negatively while it grows.

To compare the initial tempdb size to the current size run the script below.

USE master;
GO

SELECT mf.database_id
	,mf.NAME
	,mf.size * 8 / 1024 AS Initial_Size
	,df.size * 8 / 1024 AS Current_Size
FROM sys.master_files mf
/*adding info about current file size*/
INNER JOIN tempdb.sys.database_files df ON mf.NAME = df.NAME
/*filtering for tempdb only*/
WHERE mf.database_id = 2;
GO
You can set the tempdb initial size to the displayed current size if you think it will need to grow to this size again or take it as a simple guide and set the initial size less than its current size and allow it to grow as it needs.
To change the tempdb size run the script below replacing all the values in the placeholders with your specific values, use the example script further down as a guide. The files tempdev and templog are typically what the tempdb files are called in a default installation. (You can run the first script again to confirm success)

USE master;
GO

ALTER DATABASE TempDB MODIFY FILE (
	NAME = [logical file name of the tempdb data file]
	,SIZE = [value] MB
	);
GO

USE master;
GO

ALTER DATABASE TempDB MODIFY FILE (
	NAME = [tempdev]
	,SIZE = 4 MB
	);
GO


ALTER DATABASE TempDB MODIFY FILE (
	NAME = [templog]
	,SIZE = 3 MB
	);
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s