How Big should my Log Files drive be ?

[mitzysman]

I'm setting up a new server.

I putting the data on a raid 5 - 144 Gig capacity.
I putting the logs on a raid 1 -

Would you say that 18 gig's is enough space for this or would I need 36 gigs ?

On our current server the log files don't take much space but I just wondered what the recommended ratio Data / Logs is

thank you

 

[Corran007]

depends on the number of datbases and how active they are as well as how often you do backups but 18 is likely more than enough. course with drives relitvely inexpensive, if you really have doubts about having enough room, then go for 36. its only a few hunred more i believe.

 

[mitzysman]

We do nightly backups of the data and logs to both disk and tape.

So would that mean at backup the logs get truncated ?

 

[TitleistDBA]

By default SQL server will not truncate the log when you create a log backup with the wizard. You have to remove the NO_TRUNCATE option.

You want your log backup to look like this.

BACKUP LOG [Titleist_CMS] TO [Titleist_CMSLogDump] WITH INIT , NOUNLOAD , NAME = N'Titleist_CMSLog (INIT)', NOSKIP , STATS = 10, DESCRIPTION = N'Titleist_CMSLog (INIT)', NOFORMAT

Also, You don't want your backup going to the same drive as your data or logs. You would better served by creating a third array for backups. Not only will your backups run faster because you are not reading and writting to the same drive but you add another level of fault tolerance. It is much quicker to restore a database if you don't have to go to tape. But if your backup and data are on the same drive and you lost that drive you would have to go to tape.

http://www.acushnetcompany.com

 

[mitzysman]

How important is the speed of the Log Files Drive.

If I go raid 1 I can have (for about the same price)

2 18 gig drives at 15,000 RPM
or
2 36 gig drives at 10,000 RPM

I'm obviously going 15K on the data drives but does it make a ton of difference in performance on the log file drives ??

I want this server to last 4-5 years and still perform well.

 

[TitleistDBA]

well you will be mostly writting to the Log drive. The only time SQL server reads from the log is on a rollback. I would go with the two 36gig drives. You want to be able to have some room if your database grows at a faster rate than first thought.

http://www.acushnetcompany.com

 

[john230873]

If your database is mostly used for transactions then 18 Gigs should be find but if you are doing large transaction eg(insert into Select * from <table >) then 36 gig drives should be used. There is not real ratio because depending on what the database is being used for and what the database mode is set up as will change the amount of space needed. What I have done in the past is have 2 log files one on the log file drive and a second on the data file drive. The file on the log file drive is maxed size is just under the total size of the drive, if by any reason the tranaction log needs more it will write it to the data file drive. This will effect performance if this file starts being used But this is better than the database stopping.