Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Re-size TempDB files

By Amar P,

Recently I had to deal with re-sizing multiple TempDB files on a production server. My task was to reduce the size of the mdf, ldf and 15 ndf files because they were created with the wrong size initially.

As many of the DBAs already know, altering this system database is quite different compared to user databases and even compared to other system databases. Although it is relatively simple to add files, modify the file path, rename the file or increase the file size, it is actually somewhat challenging when it comes to reducing the size of existing TempDB files.

After many trials and tests I came away with 2 viable methods to accomplish this task but each option has very important benefits and drawbacks to consider.

METHOD 1: Shrink & Modify file

First of all, Microsoft strongly discourages shrinking any TempDB files that are actively being used. That's because "you may receive multiple consistency errors" that could not only disrupt user activity ("Worktables that hold intermediate results created during query processing and sorting.") happening inside TempDB at the time but may also abort the shrink operation altogether. However, if user activity is minimal or none in your case you can follow these 2 steps to reduce the TempDB file sizes:

1. Run DBCC SHRINKFILE command on each file you want to reduce the size for.
USE TempDB
GO
DBCC SHRINKFILE (N'logical_file_name', 5) -- size in MB

2. Then, run ALTER DATABASE statement for each of these files with the size you'd like them to be. This will cause the new size to register in master.sys.master_files, which is the system catalog that SQL Server uses to recreate a fresh, blank TempDB any time the server/instance is restarted.
USE MASTER
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME=' logical_file_name, SIZE=6MB)

Note that until SQL Server is restarted (when TempDB is created fresh) the above changes will not show the new values in the Database Properties or the Shrink File GUI. However, you can verify the changes immediately by running:
SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, [SIZE]*8/1024 SIZE_MB
FROM MASTER.SYS.MASTER_FILES
WHERE DB_NAME(DATABASE_ID) = 'TEMPDB'

More suggestions on how to shrink TempDB files.

METHOD 2 - Modify files in Restricted user mode

This method eliminates the risk of any consistency errors while it assures successful operation to reduce file sizes. But on the flip side, you will have to disconnect all users before you can begin your work. However, if you plan it well, you can probably accomplish these changes in about 10 minutes by following the steps below:

  1. Establish a Dedicated Administrator Connection (DAC) from Management Studio to the instance you are working on. You can do this by simply prefixing the tag "Admin:" in front of the instance name. E.g., ADMIN:ProdServer\Instance1
  2. Run ALTER DATABASE with the REMOVE option to mark the ndf files obsolete.
    USE MASTER
    GO
    ALTER DATABASE TEMPDB REMOVE FILE logical_file_name
  3. RDP to the server and...
    1. Stop the instance from command prompt. For default instance:
      C:\>NET STOP MSSQLSERVER
      or
      NET STOP "SQL Server (MSSQLSERVER)"

      For named instance:
      C:\> NET STOP "SQL Server ( instancename )" or NET STOP MSSQL$instancename
    2. Start the instance in restricted mode (change the path to where you SQL bits are)
      C:\SQL\MSSQL.1\MSSQL\Binn>sqlservr.exe -c -f
  4. Alter TempDB with the new initial file size for the mdf and ldf files only from the dedicated connection in SSMS (until you restart SQL Server in normal mode, this is the only connection available)
    USE MASTER
    GO
    ALTER DATABASE TEMPDB MODIFY FILE (NAME='logical_file_name', SIZE=6MB)
    
  5. Go back to the command prompt window and Hit Ctrl + C to get out of the restricted mode (say "Yes" when the prompt asks if you want to stop the SQL Server). Then start the instance in normal mode. For default instance:
    C:\>NET START MSSQLSERVER
    
    or
    NET START "SQL Server (MSSQLSERVER)"
    
    For named instance:
    C:\> NET START "SQL Server ( instancename )"
    
    or
    
    NET START MSSQL$instancename
    
  6. Alter TempDB with Add File option with the new size specification for the ndf files.
    ALTER DATABASE TEMPDB ADD FILE (NAME=logical_file_name, FILENAME='C:\SQL\Data\logical_file_name.ndf',SIZE=6MB)
    
    At the end run this query again to ensure all your changes are properly showing in the Master database:
    SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, [SIZE]*8/1024 SIZE_MB
    FROM MASTER.SYS.MASTER_FILES
    WHERE DB_NAME(DATABASE_ID) = 'TEMPDB'
    

Conclusion

Clearly the first method is a lot simpler and faster but you'd be taking a big risk of possibly corrupting data in TempDB. Plus, the shrink operation may not even finish due to concurrent usage of those files. The second method, while being more elaborate, ensures a smooth operation. However it does require downtime.

Your constructive comments are always welcome!

Total article views: 6541 | Views in the last 30 days: 32
 
Related Articles
BLOG

SQL Server Needs Tempdb

I saw a post recently where someone noted they had moved tempdb like this: USE master; GO ALTER DAT...

FORUM

Shifting TEMPDB database to another folder in same server.

Shifting TEMPDB database to another folder in same server.

FORUM

Alter Database with variable

Alter Database with variable

FORUM

TempDB

Tempdb

ARTICLE

Moving TempDB

One of the common questions new SQL Server workers ask is how they can move or alter tempdb. Andy Wa...

Tags
dbcc shrinkfile    
tempdb    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones