We just inherited a legacy Tempdb database with Multiple MDF files. Tempdb1 is the primary.



(1) Was tempdb2.mdf and tempdb3.MDF being ignored the whole time?

(2) Or Is is true naming extensions don't matter? I can call 2 and 3, .mydat, or .datafile, etc. And they are still inferred as secondary?

(3) However, for best practice should I label 2/3 with .ndf?


Converting previous comment into answer - Sql Server does not care what extension you put on your secondary files, however, to make administration easier and make sense, why not follow the .ndf for all secondary files?


SQL Server uses a round robin with proportional fill algorithm for TempDB and data files in general. Thus if you have 1 TempDB file and add 2 more, only the other 2 will be filled until they reach the size of the first one and all will then grow proportionally; or when the next restart occurs and they all get emptied out.

Thus you will be using those files. You can see them being used in the physical index stats DMV. Naming doesn't matter, just do what works for you to manage them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy