TempDB Configuration – SQL Server 2016 Setup

Hello Geeks,

My good friend Jay Vemula has posted a blog about an error you may face if you are selecting all features while installing SQL Server 2016. As a DBA, you should always know what to be checked and what it needs as part of the installation prerequisites. It is also equally important to plan your post installation tasks.

If you are a database professional who have ever installed an SQL Server, you would know the pain of doing all the post installation, configuration tasks. Implementing the best practices and recommendations. Well, in SQL Server 2016 one of them is checked off the list. TempDB configuration.

During setup of SQL Server 2016, you can set the number of TempDB files based on your number of processors. The default value is the number of processors if the machine has less than 8 processors OR 8 if the machine has more than 8 processors. If the machine has more than 8 processors, you can increase it to the number of processors you have during installation.

One thing to be noted is what you can do during setup and what not. The number of files you chose during setup is restricted to number of cores you have during installation. In my example, I only had one core. When I chose 2 tempdb files during setup it throws an error.


Post installation if I have to add additional file for any reason, say, I am a sloppy DBA and missed space alerts and my TempDB grew to fill the drive. The only option in few cases is to add another file on other drive. This is purely a break-fix scenario.

TempDB Configuration SQL Server 2016 2

Microsoft has planned for such scenarios and still allows to add it as a break-fix attempt. This makes sense as allowing more number of tempdb files than number of cores should not be a practice and therefore restricted during setup.

Given that it is a simple inclusion, it is very important for naïve installers. It also removes the additional task for DBAs post installation. Look forward to post more on SQL Server 2016 as I explore.

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook


About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.