How to determine the Log Space

Question

How to determine the Log Space

DBA_Learner

SSCarpal Tunnel

Points: 4228
More actions
January 4, 2012 at 6:36 pm

#265897

For ex: If i allocate some 10GB for MDF file, then how much should I allocate for LDF. Is there any specific consideration required or does SQLServer takes required LDF space automatically.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

2 to 3 X the size if your biggest table is a good starting point. Or maybe 5 to 10% of the data files' size.

That being said, you need to monitor this after sometime and then make the call. There's just no way to guess at this without some historical data under production load.

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 2

I've never seen a hard & fast ruling on this one. I generally go between 1/10 and 1/5 of the size of the database and then watch things from there. It completely depends on the size and number of your transactions along with the frequency of your log backups (assuming you have them).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

You should have some NFR's about the kind of transactional activity expected for the database . Usually its a good idea to understand the NFR and then Size the mdf and ldf accordingly.

Based on your backup strategy and other requirements the actual numbers could vary. But like mentioned above 5 -10 %the mdf is a good place to start.

Keep in mind any weekly bulk load or other kind of operations performed but not accounted for in the NFR

Jayanth Kurup[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please read through this - Managing Transaction Logs[/url]

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Jayanth_Kurup (1/5/2012)
You should have some NFR's about the kind of transactional activity expected for the database . Usually its a good idea to understand the NFR and then Size the mdf and ldf accordingly.

NFR?

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

DBA_Learner SSCarpal Tunnel Points: 4228 More actions · Answer 6

Thanks Everyone. So, from the responses what I understood is, better to have 5-10% of mdf for ldf files and need to keep monitoring, so depends upon number of transactions we can increase the size of ldf.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

george sibbald (1/5/2012)
Jayanth_Kurup (1/5/2012)
You should have some NFR's about the kind of transactional activity expected for the database . Usually its a good idea to understand the NFR and then Size the mdf and ldf accordingly.
NFR?

Non-Functional Requirements?

Not For Replication?

...

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 8

non functional requirements , its got to do with the kind of expected loads and performance criteria the application is expected to meet on a minimum. A typical example would should be able to handle 300 customers ( 50 concurrent) per hour with 500 transactions per min and a delay of less than 2 sec . etc

Jayanth Kurup[/url]

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

🙂

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