tempdb - second ldf

Question

tempdb - second ldf

bobba

SSCommitted

Points: 1605
More actions
April 19, 2017 at 10:02 am

#322972

We have a canned product installed on a standalone server. Contains both the application and SQL Server 2008 R2 (2 CPUs). I was reviewing the configuration via SSMS post install and noted that the tempdb had 1 data file (mdf + ldf). Storage allocated was low, very low (like 8 mb for the mdf, 1 for the ldf). Autogrowth was enabled but I felt storage should have been added.
There appeared a need for an additional datafile. There had been complaints about performance (vendor reviewed), when I saw tempdb setup, called them back. They agreed to some changes. A few weeks later it was done. While I saw a second ndf, I was surprised to see a second ldf as well (there is a lot of free space on the drive).
I've added tempdb datafiles (ndf), but never an addtional log (ldf). Did I miss something? Add a second log file if you are maxing out on space with the current physical drive?
Comments are appreciated - thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

It doesn't affect performance like the data files. Writes to the log are just sequential writes. If you are tight on space and think you may run out of space for the drive the log file is you could add another file to another drive - the writes will go to the second log file if the first is filled. But it's just to address a space issue.
Normally you'd just want to make sure you have enough space on the drive used for the log.

Sue

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

bobba - Wednesday, April 19, 2017 10:02 AM
We have a canned product installed on a standalone server. Contains both the application and SQL Server 2008 R2 (2 CPUs). I was reviewing the configuration via SSMS post install and noted that the tempdb had 1 data file (mdf + ldf). Storage allocated was low, very low (like 8 mb for the mdf, 1 for the ldf). Autogrowth was enabled but I felt storage should have been added.
There appeared a need for an additional datafile. There had been complaints about performance (vendor reviewed), when I saw tempdb setup, called them back. They agreed to some changes. A few weeks later it was done. While I saw a second ndf, I was surprised to see a second ldf as well (there is a lot of free space on the drive).
I've added tempdb datafiles (ndf), but never an addtional log (ldf). Did I miss something? Add a second log file if you are maxing out on space with the current physical drive?
Comments are appreciated - thanks.

Was the additional log file on a different volume? Was the volume for the primary ldf cramped on space? Additional log files serve no role in performance but can in some circumstances mitigate space issues as "overflow" measures.
😎

rvsc48 SSCertifiable Points: 7701 More actions · Answer 3

"I've added tempdb datafiles (ndf), but never an addtional log (ldf). Did I miss something? Add a second log file if you are maxing out on space with the current physical drive?"
Exactly. There is no advantage to having multiple log files for any database, including tempdb, due to the fact that Sql Server only writes to one log file at a time unlike the data files. The only time to add a log file would be in an emergency where your drive which contains your log file is running out of space and you need to relocate the log file to another drive which has space on it to keep the database online. As far as pre-sizing the log file to keep it from growing which could be a performance issue, it is recommended the tempdb log file be sized x2 the size of one of the data files. Ex. Log File = Data file * 2

bobba SSCommitted Points: 1605 More actions · Answer 4

The second ldf is the same location as the first. And there's plenty of space on the drive. Don't see it running out for several years. And don't see the need for a second ldf.

Thank you all for responding.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

So you asked the vendor to add a data file but they added a data file and a log file? I see this sort of thing quite a lot - vendors who don't have the depth of knowledge of SQL Server required to make decisions like this.

John