Use dump transaction to make sure your transaction log is less than 55 percent full. Then use the following procedure to test the new threshold:
Fill the transaction log by simulating routine user action. Use automated scripts that perform typical transactions at the projected rate.
When the 45 percent free-space threshold is crossed, your threshold procedure will dump the transaction log. Since this is not a last-chance threshold, transactions will not be suspended or aborted; the log will continue to grow during the dump.
While the dump is in progress, use sp_helpsegment to monitor space usage on the log segment. Record the maximum size of the transaction log just before the dump completes.
If considerable space was left in the log when the dump completed, you may not need to dump the transaction log so soon, as shown in Figure 31-8:
Figure 31-8: Transaction log with additional threshold at 45 percent
Try waiting until only 25 percent of log space remains, as shown in Figure 31-9:
Figure 31-9: Moving threshold leaves less free space after dump
Use sp_modifythreshold to adjust the free_space value to 25 percent of the log size. For example:
sp_modifythreshold mydb, logsegment, 512,
thresh_proc
Dump the transaction log and test the new free_space value. If the last-chance threshold is crossed before the dump completes, you are not beginning the dump transaction soon enough, as shown in Figure 31-10:
Figure 31-10: Additional log threshold does not begin dump early enough
25 percent free space is not enough. Try initiating the dump transaction when the log has 37.5 percent free space, as shown in Figure 31-11:
Figure 31-11: Moving threshold leaves enough free space to complete dump
Use sp_modifythreshold to change the free_space value to 37.5 percent of log capacity. For example:
sp_modifythreshold mydb, logsegment, 768,
thresh_proc
