March 1, 2013 at 12:49 pm
Does anybody know how sql uses multiple tempdb .ldf files when there is a restricted file growth limit on the data files? So, let's say I have tempdb1.mdf, tempdb2.ldf and tempdb3.ldf data files. When let's say tempdb2.ldf get's full does it move to the next data file (tempdb3.ldf) withough throwing a tempdb full error message? Or, does sql only throw the tempdb full error message when ALL the data files are full?
Because a client has this set up with multiple tempdb data files with restricted file growth (not sure why cause i know this is not right practice). Appreciate the help.
March 1, 2013 at 1:16 pm
Multiple tempDB data files with limited growth isn't a bad thing, as long as the total size is enough for the app and as long as all files are the same size.
As with all data files, SQL uses a proportional fiill algorithm, writing to the files in proportion of the empty space they have.
Now you said data files yet mentioned ldf files. Which is it? ldf are transaction log, not data.
Log files (of which there should only be one per database) are used strictly sequentially, one and then the other.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 1, 2013 at 1:33 pm
SQL Server uses datafile in parallel and log file in sequential order..
Just as a best practice, for naming convention always try to use .ndf for second, third, etc data files and .ldf for log files....
March 1, 2013 at 2:00 pm
sorry for the confusion. i meant multiple .ndf files NOT .ldf huge difference. =)
March 1, 2013 at 3:31 pm
BuntyBoy (3/1/2013)
SQL Server uses datafile in parallel and log file in sequential order..Just as a best practice, for naming convention always try to use .ndf for second, third, etc data files and .ldf for log files....
I prefer to use ".mdf" for all data files.
What is the gain to using ".mdf" on some files and ".ndf" on others?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 1, 2013 at 4:51 pm
As a best practice and even you will find in almost all the SQL Server books, use the following naming convention:-
.mdf -> for primary data file
.ndf -> for subsequent secondary, etc dat files
.ldf -> for log files
This way if I say .ndf file other DBA will know you are not talking about primary datafile ....
Bottom line is it all depends on the best practise you are following.. at least we are following the one described above....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply