Avg. Disk Queue Length & Transaction Log
Posted on 2009-02-24
We have a SQL server that handles about 2000 transactions per second from a series of web servers. Each web server delivers content and that content is read from the database server. Everything works fine most of them time but then about once ever couple of minutes the transaction log truncates (which is good otherwise it would grow forever). However whilst truncating it effectively pauses all incoming traffic from the web servers. Then the web servers queue the requests and don't start processing them until the log is finally truncated. The size of the database is about 50 GB and the size of the transaction log is about 200 MB. I think that it is better to have the transaction log small and truncate quickly rather than having it large and taking ages to truncate.
We have taken as much precaution as possible when setting up the SQL server. The log files are on a seperate RAID controller from the database. The RAID controller is 5+1 spread over 6 72 GB SATA disks and works as quickly as we think it can work. There is no other processes running on this server as it is dedicated SQL server.
Considering that we don't really care about ROLLBACK and other transactional things is there any Options, or switches we can flick to turn off Transaction logging all together? Alternatively has anybody else been turning SQL servers and found a faster way to avoid HDD bottlenecks whilst Transaction Logs are being truncated?