When to add an LDF Data File

Question

When to add an LDF Data File

drodriguez-762178

SSCommitted

Points: 1795
More actions
August 11, 2008 at 11:13 am

#154604

At what point should an LDF Data File be added to the database? Right now our MDF file is approximately 17GB. The database is basically a read only database during the day.
Thanks,
Dave

Viewing 8 posts - 1 through 7 (of 7 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

do you mean another transaction log file? the log file is a write ahead file , by which I mean it completely fills the first file before writing to the next,so you would only ever add another log file for space reasons, i.e the drive was completeley full but the log file needed to be bigger to service normal database activity.

---------------------------------------------------------------------

drodriguez-762178 SSCommitted Points: 1795 More actions · Answer 2

Not the transaction log, the data file. . Right now the data file is roughly 17 GB.

Wesley Brown SSChampion Points: 12045 More actions · Answer 3

LDF is a transaction log data file. The log is sequential and doesn't benefit from additional files other than space constraints.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

I think you mean a data NDF file then. As the Db is mainly read only you could get performance improvements by adding a new FILEGROUP and moving the most used indexes non-clustered indexes to it.

Just adding another file to the primary filegroup won't buy you much, and a size of 17GB is not big enough to warrant it on its own.

---------------------------------------------------------------------

drodriguez-762178 SSCommitted Points: 1795 More actions · Answer 5

My mistake. I meant NDF (secondary database file).

Thanks.

drodriguez-762178 SSCommitted Points: 1795 More actions · Answer 6

How do I go about finding what the most used indexes are?

george sibbald SSC Guru Points: 104210 More actions · Answer 7

this is in the 2005 forum so I presume you are on 2005, therefore use the DMVs

http://msdn.microsoft.com/en-gb/magazine/cc135978.aspx

If you are in fact on 2000, a lot more difficult, you are stuck with profiler.

Don't sperate out your indexes for the hell of it though, only if you have performance problems and have exhausted other avenues such as tuning queries and your indexing strategy

---------------------------------------------------------------------