The size of the transaction log is determined by the amount of update activity in the associated database and the frequency of transaction log dumps.
This is true whether you perform transaction log dumps manually or use threshold procedures to automate the task. As a general rule, allocate to the log 10 to 25 percent of the space that you allocate to the database.
Inserts, deletes, and updates increase the size of the log. dump transaction decreases its size by writing committed transactions to disk and removing them from the log. Since update statements require logging the “before” and “after” images of a row, applications that update many rows at once should plan on the transaction log being at least twice as large as the number of rows to be updated at the same time, or twice as large as your largest table. Or you can batch the updates in smaller groups, performing transaction dumps between the batches.
In databases that have a lot of insert and update activity, logs can grow very quickly. To determine the required log size, periodically check the size of the log. This also helps you choose thresholds for the log and scheduling the timing of transaction log dumps.
dbcc checktable(syslogs)
Checking syslogs The total number of data pages in this table is 199. *** NOTICE: Space used on the log segment is 0.39 Mbytes, 19.43%. *** NOTICE: Space free on the log segment is 1.61 Mbytes, 80.57%. Table has 1661 data rows.
select count(*) from syslogs
Repeat either command periodically to see how quickly the log grows.