patternsqlMinor
Tempdb with Multiple mdf and Renaming
Viewed 0 times
mdftempdbwithrenamingmultipleand
Problem
We just inherited a legacy Tempdb database with Multiple MDF files. Tempdb1 is the primary.
Example:
(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?
Example:
Tempdb1.MDF
Tempdb2.MDF
Tempdb3.MDF(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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#212048, answer score: 2
Revisions (0)
No revisions yet.