Usage Scenario: Testing and Adjusting the New Threshold

Use dump transaction to make sure your transaction log is less than 55 percent full.

  1. 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 dumps the transaction log. Since this is not a last-chance threshold, transactions are not suspended or aborted; the log continues to grow during the dump.

  2. 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.
  3. If considerable space was left in the log when the dump completed, you may not need to dump the transaction log so soon:
    Transaction Log with Additional Threshold at 45 Percent
    Graphic of a bar graph that shows the last chance threshold not having to fire because there is considerable space left in the log after the dump completes.

    Try waiting until only 25 percent of log space remains:

    Moving Threshold Leaves Less Free Space After Dump
    Graphic of a bar graph that shows a reasonable amount of space defining the last chance threshold. It fires at a reasonable time because there isn’t too much or too little space left in the transaction log.
    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
  4. 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:
    Additional Log Threshold Does Not Begin Dump Early Enough
    Graphic of a bar graph that shows the last chance threshold firing too soon becuase the amount of free space is too little and the last chance threshold fires again before the dump completes.

    25 percent free space is not enough. Try initiating the dump transaction when the log has 37.5 percent free space:

    Moving Threshold Leaves Enough Free Space to Complete Dump
    Graphic of a bar graph that shows a reasonable amount of space defining the last chance threshold. It fires at a reasonable time because there isn’t too much or too little space left in the transaction log.
    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
Parent topic: Creating a Free-Space Threshold for the Log Segment