SQL Server – How to Best Remove Extra TempDB Data Files and Log Files

« SQL Server – How to Search/Query the SQL Server Error Log

SQL Server – Does the SQL Agent Service Account Must be a SysAdmin? »

SQL Server – How to Best Remove Extra TempDB Data Files and Log Files

May 6, 2013 by Suresh Raavi

One of the Microsoft’s recommendation for optimizing the tempDB performance is to make each tempdb data file the same size.

Today, on one of our servers, I noticed there are 13 data files with different sizes as shown in the below screenshot:

My target here is to configure tempdb with 8 equi sized data files and one log file. So, in this case I have to delete those 5 extra data files and re-size the remaining 8 files equally.

To achieve this I followed the below simple three step process (The same procedure can be applied to any user databases as well)

-- Step1: First empty the data file
USE tempdb
GO
DBCC SHRINKFILE (tempdev12, EMPTYFILE); -- to empty "tempdev12" data file
GO

Data file won’t gets deleted unless it is empty. So before going to Step2 to delete the data file, we should first empty the data file which can be done from the Step1 above.

Note: If encountered this error Msg 2555, Level 16, State 1, Line 1 while performing the above operation, please refer to this post –> Msg 2555, Level 16, State 1, Line 1 – Error DBCC SHRINKFILE with EMPTYFILE option


--Step2: Remove that extra data file from the database
ALTER DATABASE tempdb
REMOVE FILE tempdev12; --to delete "tempdev12" data file
GO

I repeated the above two steps to delete the other files as well. Below is the screenshot after deleting the extra files

--Step3: Re-size the data files to target file size 
-- Use ALTER DATABASE if the target file size is greater than the current file size
USE [master]
GO
ALTER DATABASE [tempdb] 
MODIFY FILE ( NAME = N'tempdev', SIZE = 3072000KB ) --grow to 3000 MB
GO
--Use DBCC SHRINKFILE if the target file size is less than the current file size
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 3000) --shrink to 3000 MB
GO

In my case, the target file size is 3000 MB which is greater than the current file size, so I ran the ALTER DATABASE command for all the 8 files. And below is the screenshot after re-sizing the files

Note: The same procedure can be used for removing extra log files as well. Make sure the log file you are trying to empty do not have any active portion of the log. Run DBCC LOGINFO on your specified database, taking note of the FileId and the Status indicator (2 showing that it is an active VLF)

Posted in Files and FileGroups, TempDB | Tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | 13 Comments

13 Responses

on October 4, 2013 at 1:26 pm | Reply Rupesh

We do not need to restart the MS SQL Services after adding a new file to tempdb. So, do we need to restart it when we are removing it. The answer is yes, as any kind of tempdb space addition of removeal of teh files comes under tempdb reconfig that requires a service restart. This was just to add to this beautiful piece of technical information shared by you.
Thank you
on October 4, 2013 at 6:30 pm | Reply Venkata Suresh Raavi

That’s a good point Rupesh, appreciate your comment!
on November 8, 2013 at 2:03 pm | Reply narasimha

Really helpful Today
on February 11, 2014 at 6:59 pm | Reply Suresh Raavi

Glad to hear that Narasimha!
on June 13, 2014 at 10:42 pm | Reply BPOTW 2014-06-13 | SQL Notes From The Underground

[…] Correctly Keeping MSDB Clean Myth a Day – TempDB Should Always Have 1 Date File Per Core How To Remove Extra TempDB Data Files Using SQL 2014 with TFS 2013 SQL Sentry YouTube – Queries with IN(…) / OR SQL Sentry […]
on June 24, 2014 at 3:51 am | Reply SQL Server – Script to Configure TempDB Files per Number of Logical Processors | SqlServerZest.com

[…] Here is a great post that explains how to best remove extra Tempdb files. […]
on June 25, 2014 at 4:21 pm | Reply Gatorkate

Can you do this in a production environment? If so, how do we make sure that no corruption is introduced by the shrinkdb command?
on August 12, 2014 at 1:31 am | Reply SQL Server – Aborted Online Index Operation & Observed Elevated Values for PWAIT_MD_RELATION_CACHE and MD_LAZYCACHE_RWLOCK Wait Types | SqlServerZest.com

[…] by adding extra tempdb space but still wasn’t any help. (Please refer this article on How to Best Add/Remove Extra TempDB Data Files and Log Files). As the last resort I had to failover the services to another node, basically restarted SQL […]
on January 3, 2015 at 11:41 pm | Reply Mac

thanku
on May 11, 2016 at 5:57 pm | Reply How To Delete Log Files In Sql Server 2012 | Khmer Bank

[…] SQL Server – How to Best Remove Extra TempDB Data Files … – SQL Server – How to Best Remove Extra TempDB Data Files and Log Files. May 6, 2013 by Suresh Raavi […]
on June 2, 2016 at 11:54 pm | Reply How To Clean Tempdb In Sql Server 2005 | Football Online

[…] SQL Server – How to Best Remove Extra TempDB Data Files … – SQL Server – How to Best Remove Extra TempDB Data Files and Log Files. May 6, 2013 by Suresh Raavi […]
on September 18, 2017 at 10:06 am | Reply duwalnpd

I try this but not able to delete the tempdb file.
use tempdb
GO

DBCC DROPCLEANBUFFERS
GO

DBCC FREEPROCCACHE
GO

DBCC FREESESSIONCACHE
GO

DBCC FREESYSTEMCACHE ( ‘ALL’)
GO

DBCC SHRINKDATABASE (tempdb,8)
GO
— Step1: First empty the data file
USE tempdb
GO
DBCC SHRINKFILE (temp8, EMPTYFILE); — to empty “tempdev12” data file
GO
–Step2: Remove that extra data file from the database
ALTER DATABASE tempdb
REMOVE FILE temp8; –to delete “tempdev12” data file
GO
- on March 22, 2018 at 2:02 am | Reply Giles Middleton
  
  Thank you duwalnpd – My tempdb’s couldn’t be removed due to having content, but your script helped remove the contents of them. Interestingly, in a load test of our system, moving from 1 tempdb to 4 tempdb made the system perform worse, so don’t always assume it’s a good thing to do without testing the result!