CODEWITHSUNDEEP
sql-server-2008sql-server-2012sql-server-2008-r2sql-server-2014sql-server-2016
Vikrant More
Vikrant More

Reputation: 5442

How much tempdb files we can create in SQL Servers starting from 2008 version?

Hi i am working on different version of SQL Servers starting from SQL Server 2008 to 2016. How many tempdb files we can create for each versions?

Upvotes: 0

Views: 201

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28920

some History:

creating 1 TempDb datafile(same auto growth settings) for each processor and only one log file has been the best practice from long back..

There are also instances where TempDB datafiles have been restricted to maximum number of 8 or number of cores you have whichever is lesser

We followed first and switched to second one later.

Now coming to your question :
From versions 2005 to 2014,you can create as many datafiles you need and you are not restricted to follow best practices..

But SQLserver 2016,follows this best practice and limits number of datafiles.so maximum data files you can add in SQL2016 is

   8 or number of cores you have (which ever is lesser)

You can also start from 1 data file and depending on allocation contention ,you can increase your number

Upvotes: 1

Related Questions