Transaction log setup | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transaction log setup

I have a database that is using simple recovery model Evey night a large amount of batch inserts are performed against it through a custom program. the databases has a 2 log files of 1024MB each on seperate physical disks during the nightly load process the transaction per second counter has been around 80-100
and the time to complete is about an 1.5 hours when I reduced the size of the primary log files to 128mb and removed the secondary log file the transactions per second went up to 260-300. anmd time to complete was .45 hours I having trouble understanding why this is the case
and interpreting the following counters in the context of the simple recovery mode: Log Flush Wait Time
Log Flush waits/sec
Log Flushes/sec
Transactions/sec How should the transaction log be setup for best performance in Simple recovery mode DB’s? I realize that bulkloading the data would be the ideal solution.
If the inserts are huge and executed in batches then better to choose Bulk-Logged Recovery model rather than Simple recovery model. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

the inserts are occuring one at a time but not inside of a transactional batch
Transaction logs are written sequentially. So, it doesn’t help distributing these files across disks. Unless, you don’t have enough space on the disk for the transaction file to expand, you don’t have to create an additional log file.
Also check the PROCESS & Physical disk counters to assess the situation. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>