Survey: how are your transaction logs configured?

Over the last few months there's been some noise (mostly of my making) on Twitter about the number of VLFs in transsction logs. Given the large numbers of people who read the blog and follow me on Twitter, I thought it would be very interesting to collect some statistics from a few hundred of you about how your transction logs are configured – in terms of the size, number of log files, and number of VLFs.

To that end, I've created a little script that you can download and run which will generate output of the form:

DB ID  Recovery Model  Log Size (MB)  Log Used (%)   Log File Count  VLF Count 
—— ————— ————– ————– ————— ———-
1      SIMPLE          6.12           34.42          1               22
2      SIMPLE          0.49           75.60          1               2 
3      FULL            14.68          97.90          1               51
4      SIMPLE          2.49           45.92          1               10
11     SIMPLE          1.99           33.55          1               4
12     FULL            359.99         77.84          1               96
13     FULL            0.48           45.67          1               2

I'd like as many of you as possible to send me the results for some of your systems – there are no database names in the output and the results will be completely anonymous. I'll collect all the results together and blog some scatter graphs showing how people have things set up.

You can get the code from SQLSkillsLogInfo.zip (2.05 kb) (it works on 2000, 2005, 2008) and send me as many results as you like (paul@sqlskills.com) either in plain text or spreadsheet format – or paste them totally anonymously in a comment. The script only takes a few seconds to run, won't affect performance in any way, and won't affect your transaction logs. Now – if your transaction log has many thousands of VLFs, it might take a few minutes to run…

You might think your system is boring – but as far as this is concerned, all results are useful results.

I'll publish the results in a couple of weeks.

Thanks!

24 thoughts on “Survey: how are your transaction logs configured?

  1. 1 SIMPLE 1.24 50.63 1 5
    2 SIMPLE 16.18 49.52 1 55
    3 FULL 0.74 56.32 1 3
    4 SIMPLE 5.55 69.48 1 20
    5 SIMPLE 277.37 2.75 1 90
    6 SIMPLE 262.43 2.46 1 34
    7 SIMPLE 37.12 8.54 1 54
    8 SIMPLE 37.12 7.44 1 54
    9 SIMPLE 62.38 42.89 1 2
    10 SIMPLE 37.12 6.89 1 54
    11 SIMPLE 1058.37 2.55 1 138
    12 SIMPLE 1058.37 2.01 1 138
    13 SIMPLE 37.12 7.61 1 54
    14 SIMPLE 37.12 6.18 1 54
    15 SIMPLE 37.12 5.57 1 54
    16 SIMPLE 37.12 5.06 1 54
    17 SIMPLE 37.12 27.59 1 54
    18 SIMPLE 90.8 6.57 1 70
    19 SIMPLE 0.99 48.57 1 4
    20 SIMPLE 0.99 47.49 1 4
    21 SIMPLE 62.38 24.28 1 2

  2. DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    —— ————— ———————- ———————- ————– ———–
    1 SIMPLE 17.8 42.37 1 59
    2 SIMPLE 5119.99 30.02 1 16
    3 FULL 0.49 50 1 2
    4 SIMPLE 672.24 2.4 1 211
    5 BULK_LOGGED 72418.05 1.31 1 342
    6 FULL 35562.93 0.18 1 428
    7 FULL 31.99 74.66 1 12
    8 FULL 9170.62 0.14 1 192

  3. DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 2.24 42.16 1 9
    2 SIMPLE 146.18 51.25 1 147
    3 SIMPLE 107.43 8.17 1 34
    4 SIMPLE 132.87 9.37 1 143
    5 FULL 3001.43 0.74 1 237
    6 FULL 102.24 8.97 1 132
    7 FULL 1574.99 1.78 1 248
    8 FULL 689.12 99.77 1 232
    9 FULL 137.43 44.2 1 6
    10 SIMPLE 566.74 22.85 1 1923
    11 SIMPLE 8.55 30.54 1 10
    12 SIMPLE 25.29 28.63 1 4
    13 SIMPLE 100.99 3.68 1 44
    14 SIMPLE 107.99 2.34 1 8
    15 SIMPLE 417.37 3.15 1 191
    16 SIMPLE 26.18 18.5 1 71
    17 FULL 2266.55 34.86 1 314
    18 BULK_LOGGED 249.88 0.17 1 2

  4. Hi Paul,

    I just checked the scritp and now make sense.

    The LOG USED column is a PERCENTAGE and not MB as described in this article.

    Right?

  5. Several servers:

    DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 1.24 50.63 1 5
    2 SIMPLE 1.49 46.6 1 6
    3 FULL 0.99 50.39 1 4
    4 SIMPLE 19.62 27.12 1 63
    5 FULL 0.8 66.87 1 3
    6 FULL 0.8 34.95 1 3
    7 FULL 7.55 28.41 1 26
    8 FULL 3.8 27.67 1 14
    9 FULL 4.18 27.59 1 15
    10 FULL 31.68 18.68 1 83
    1 SIMPLE 1.24 52.2 1 5
    2 SIMPLE 17.8 45.26 1 59
    3 FULL 0.74 41.58 1 3
    4 SIMPLE 44.43 26.78 1 83
    5 FULL 386.12 2.65 1 187
    6 FULL 513.99 1.43 1 199
    7 FULL 36.68 8.84 1 29
    8 SIMPLE 5694.18 0.8 1 155
    9 FULL 1835.99 0.67 1 231
    1 SIMPLE 1.24 42.77 1 5
    2 SIMPLE 9.93 61.37 1 35
    3 FULL 176.93 99.88 1 155
    4 SIMPLE 60.24 37.19 1 241
    5 FULL 531.62 2.53 1 132
    6 SIMPLE 51.18 15.04 1 103
    7 SIMPLE 3.74 19.61 1 10
    8 SIMPLE 10.93 23.83 1 35
    9 SIMPLE 9.93 25.32 1 35
    10 SIMPLE 259.12 8.01 1 171
    11 FULL 313.55 3.95 1 175
    12 SIMPLE 68.99 4.34 1 8
    13 SIMPLE 330.05 13.4 1 112
    14 FULL 24.99 17.79 1 4
    15 FULL 24.99 16.74 1 4
    1 SIMPLE 1.24 44.03 1 5
    2 SIMPLE 0.99 51.72 1 4
    3 FULL 0.99 35.04 1 4
    4 SIMPLE 26.55 33.82 1 76
    5 FULL 3.37 29.67 1 13
    6 FULL 24.99 6.91 1 8
    1 SIMPLE 1.24 49.37 1 5
    2 SIMPLE 4999.99 11.3 1 16
    3 FULL 0.99 44.49 1 4
    4 SIMPLE 896.8 1.72 1 195
    5 FULL 4999.99 3.22 1 18
    6 FULL 417.37 3.66 1 191
    7 FULL 7468.24 0.37 1 413
    8 FULL 3904.05 1.19 1 327
    9 FULL 2951.12 0.78 1 338
    10 FULL 5.68 25.08 1 20
    11 FULL 25154.12 0.53 1 587
    12 FULL 6.12 27.09 1 22
    13 FULL 3.74 25.56 1 10
    14 FULL 16011.12 0.42 1 508
    16 FULL 39.12 7.77 1 34
    17 FULL 1207.68 3.45 1 68
    1 SIMPLE 1.24 32.7 1 5
    2 SIMPLE 3.74 26.83 1 14
    3 FULL 0.74 46.32 1 3
    4 SIMPLE 8.93 34.08 1 32
    5 FULL 17.8 12.48 1 59
    6 FULL 611.12 2.41 1 207
    7 FULL 8.18 13.8 1 29
    1 SIMPLE 24.99 15.72 1 9
    2 SIMPLE 120.74 38.63 1 139
    3 FULL 0.99 38.58 1 4
    4 SIMPLE 36.68 9.54 1 35
    5 FULL 127.12 5 1 86
    6 FULL 828.87 2.2 1 230
    7 FULL 2047.99 2.03 1 24
    8 FULL 1499.99 1.03 1 21
    9 FULL 15359.99 4.45 1 18
    10 FULL 499.99 4.39 1 143
    11 FULL 11.18 24.05 1 39
    12 FULL 1333.99 1.76 1 268
    13 FULL 1164.99 1.11 1 162
    14 FULL 1018.74 1.67 1 169
    15 FULL 8.3 24.48 1 29
    16 SIMPLE 701.05 9.18 1 60
    17 SIMPLE 16.43 59.15 1 55
    1 SIMPLE 1.24 60.38 1 5
    2 SIMPLE 672.24 64.19 1 211
    3 FULL 0.99 56.3 1 4
    4 SIMPLE 42.24 29.13 1 95
    5 FULL 24.12 19.93 1 71
    6 FULL 4.62 29.3 1 16
    7 FULL 0.8 33.56 1 3
    8 FULL 6741.87 0.68 1 403
    9 FULL 30.24 7.47 1 17
    10 FULL 92.3 10.39 1 127
    11 FULL 111.74 9.3 1 135
    12 FULL 17486.99 0.39 1 523
    13 FULL 1333.62 1.57 1 267
    14 FULL 290.05 4.07 1 175
    15 FULL 11.18 25.38 1 39
    16 FULL 249.99 8.49 1 17
    1 SIMPLE 2.24 52.44 1 9
    2 SIMPLE 511.99 33.24 1 8
    3 FULL 17.8 23.12 1 59
    4 SIMPLE 160.8 30.39 1 151
    5 FULL 263.68 6.64 1 171
    6 FULL 62.99 12.96 1 111
    7 FULL 29.24 19.44 1 79
    8 FULL 1613.74 2.13 1 283
    9 FULL 198.05 6.05 1 159
    10 FULL 163.68 6.65 1 151
    11 FULL 21162.49 0.4 1 556
    12 FULL 17.99 18.89 1 4
    18 FULL 2.55 33.85 1 10
    19 FULL 417.37 4.49 1 191
    20 FULL 2.55 34.29 1 10
    22 FULL 4785.24 0.86 1 377
    23 FULL 32.18 17.44 1 83
    24 FULL 621.99 2.23 1 207
    25 FULL 14.99 25.76 1 8
    26 FULL 712.24 5.41 1 573
    27 FULL 417.37 3.07 1 191
    28 FULL 9.99 22.21 1 13
    30 FULL 420.05 2.73 1 160
    31 FULL 21.93 21.08 1 67
    32 FULL 0.8 65.66 1 3
    33 FULL 76.24 11.37 1 119
    34 FULL 17.99 16.1 1 4
    36 FULL 1122.68 3.42 1 84
    37 FULL 8.99 26.69 1 32
    38 FULL 26.43 13.43 1 20
    39 FULL 17.99 14.5 1 4
    42 FULL 11920.3 0.84 1 404
    46 FULL 511.99 11.36 1 8
    47 FULL 255.99 4.61 1 8
    48 FULL 31.99 19.09 1 4
    49 FULL 127.99 12.22 1 8
    50 FULL 31.99 10.97 1 4
    51 FULL 49.99 6.32 1 4
    1 SIMPLE 1.24 64.47 1 5
    2 SIMPLE 16.18 47.78 1 55
    3 FULL 0.99 38.98 1 4
    4 SIMPLE 30.24 24.01 1 32
    5 FULL 0.8 37.44 1 3
    6 FULL 0.8 57.77 1 3
    7 FULL 0.8 34.89 1 3
    8 FULL 0.8 38.65 1 3
    9 FULL 26.18 24.23 1 74
    10 FULL 3.43 28.66 1 13
    11 FULL 3459.49 0.94 1 347
    12 FULL 6.99 27 1 25
    13 FULL 3.8 32.26 1 14
    14 FULL 1333.62 1.96 1 267
    15 FULL 14.99 25.85 1 17
    16 FULL 23.8 20.53 1 70
    17 FULL 49.99 12.78 1 72
    18 FULL 19.93 22.7 1 63
    19 FULL 0.54 41.49 1 2
    20 FULL 63.99 12.52 1 112
    21 FULL 66.55 4.9 1 42
    1 SIMPLE 1.24 46.54 1 5
    2 SIMPLE 127.99 21.14 1 8
    3 FULL 16.18 24.09 1 55
    4 SIMPLE 176.93 9.21 1 155
    5 FULL 319.05 5.72 1 179
    6 FULL 217.87 5.83 1 163
    7 FULL 4.62 24.49 1 16
    8 FULL 135.24 7.97 1 143
    9 FULL 122.93 8.3 1 139
    11 FULL 111.74 8.28 1 135
    12 FULL 42.93 15.45 1 95
    13 FULL 889.87 2.24 1 233
    14 FULL 3550.05 0.84 1 350
    16 FULL 16.99 5.72 1 4
    17 FULL 153.87 4.9 1 86
    19 FULL 513.99 3.22 1 199

  6. DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 10.17 15.25 1 36
    2 SIMPLE 9691.99 52.88 1 8676
    3 FULL 0.49 59.52 1 2
    4 SIMPLE 19.62 45.3 1 63
    5 SIMPLE 1044384 0.01 1 2066
    7 SIMPLE 1023.99 0.82 1 12
    8 SIMPLE 1023.99 7.45 1 12
    9 SIMPLE 99.99 15.54 1 101
    10 SIMPLE 10.17 15.99 1 36

  7. DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 9.93 32.26 1 35
    2 SIMPLE 3411.68 32.11 1 184
    3 FULL 9.93 27.81 1 35
    4 SIMPLE 6.12 60.28 1 22
    5 FULL 0.99 50.44 1 4
    6 FULL 13.99 3.46 1 4
    7 FULL 0.99 36.76 1 4
    8 FULL 0.99 36.71 1 4
    9 FULL 0.99 41.73 1 4
    10 FULL 6.99 5.03 1 4
    11 FULL 6.99 15.82 1 4
    12 FULL 1.99 24.88 1 4
    13 SIMPLE 6701.99 0.71 1 540
    14 FULL 0.54 82.16 1 2
    15 FULL 3.99 11.36 1 4
    16 FULL 3.99 35.97 1 4
    17 FULL 0.99 38.63 1 4
    18 FULL 0.99 48.47 1 4
    19 FULL 0.48 45.26 1 2
    20 FULL 0.99 40.55 1 4
    21 FULL 8.99 28.38 1 4
    22 FULL 1.99 19.24 1 4
    23 FULL 1.99 20.81 1 4
    24 FULL 0.99 41.73 1 4
    25 FULL 2.99 15.99 1 4
    26 FULL 6.99 5.34 1 4
    27 SIMPLE 2473.3 1.35 2 494
    28 FULL 2.99 31.25 1 4
    29 FULL 0.99 40.35 1 4
    30 FULL 8.99 16.2 1 4
    31 FULL 14.99 21.49 1 4
    32 FULL 0.48 68.25 1 2
    33 FULL 0.99 53.69 1 4
    35 FULL 9.99 8.46 1 4

  8. DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 26.18 10.1 1 75
    2 SIMPLE 3430.98 18.97 3 80
    3 SIMPLE 8.99 14.59 1 32
    4 SIMPLE 347.18 6.6 1 392
    5 FULL 9402.68 3.14 2 731
    6 FULL 184.8 2.94 1 136
    7 FULL 6.99 8.29 1 4
    8 FULL 1503.74 0.86 1 192
    9 SIMPLE 394.99 2.53 1 167
    10 SIMPLE 13.24 10.74 1 39
    11 SIMPLE 32.87 21.51 1 7
    12 FULL 946.74 0.9 1 226
    13 FULL 26.13 26.94 1 4
    14 FULL 9.99 20.05 1 4
    15 FULL 4.99 17.23 1 4
    16 FULL 24.99 13.23 1 4
    17 FULL 1549.8 2 1 24
    18 SIMPLE 34.8 11.73 1 73
    19 SIMPLE 17.74 10.38 1 45
    20 FULL 258.87 2.66 1 157
    21 SIMPLE 34.8 9.07 1 4
    22 FULL 42441.23 0.33 3 1011

  9. 1 SIMPLE 0.99 53.94 1 4
    2 SIMPLE 3.37 67.63 1 10
    3 FULL 4.55 96.05 1 16
    4 SIMPLE 23.8 13.96 1 71
    5 FULL 6.12 26.64 1 22
    6 SIMPLE 1.24 68.75 1 5
    7 FULL 1441.3 1.65 1 275
    8 FULL 1441.3 1.17 1 275
    9 FULL 6.74 59.54 1 24
    10 SIMPLE 3.99 20.43 1 4
    11 FULL 13.3 90.69 1 43

  10. I had to modify the script to skip offline DB’s. The results are somewhat depressing and don’t support the "we don’t need a DBA" theme.

    29 is one of the better entries if you’re looking for a laugh at my expense :D

    DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    —— ————— ———————- ———————- ————– ———–
    1 SIMPLE 0.99 50 1 4
    2 SIMPLE 1704.98 46.04 3 40
    3 FULL 16.18 99.08 1 55
    4 SIMPLE 739.49 1.09 1 219
    5 FULL 6.74 14.47 1 24
    6 SIMPLE 0.74 59.21 1 3
    7 SIMPLE 10004.8 0.82 1 406
    8 SIMPLE 1.49 35.5 1 6
    9 FULL 111.74 94.33 1 135
    10 SIMPLE 33494.68 0.15 1 283
    11 SIMPLE 19487.24 0.89 1 187
    12 SIMPLE 7333.37 0.27 1 380
    13 FULL 21.62 12.19 1 67
    14 FULL 285.05 2.21 1 175
    15 FULL 21.62 10.92 1 67
    16 FULL 146.18 3.32 1 147
    17 FULL 290.05 96.35 1 175
    18 FULL 23.8 10.5 1 71
    19 SIMPLE 17715.68 0.75 1 171
    20 SIMPLE 10.99 7.44 1 4
    21 SIMPLE 19649.3 0.13 1 419
    22 FULL 493.68 1.31 1 198
    23 FULL 76.24 5.81 1 119
    25 SIMPLE 0.99 30.76 1 4
    26 FULL 99.99 0.66 1 8
    27 FULL 199.99 3.38 1 16
    28 SIMPLE 10.99 21.63 1 4
    29 SIMPLE 9140.99 1.31 1 3660
    30 SIMPLE 0.99 38.04 1 4
    31 SIMPLE 36828.3 0.06 1 651
    34 FULL 8.99 14.95 1 32
    35 FULL 1.74 34.84 1 7
    38 FULL 1441.3 95.37 1 275
    39 FULL 905.99 96.82 1 76
    40 SIMPLE 204.3 2.46 1 119

  11. DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 0.49 85.71 1 2
    2 SIMPLE 0.74 68.55 1 3
    3 FULL 0.49 56.35 1 2
    4 SIMPLE 0.74 77.89 1 3
    5 SIMPLE 0.74 37.24 1 3
    6 SIMPLE 0.74 36.05 1 3
    7 FULL 14.68 27.71 1 51
    1 SIMPLE 2.24 37.09 1 9
    2 SIMPLE 31.68 48.89 1 83
    3 FULL 3.05 88.44 1 12
    4 SIMPLE 2.24 54.05 1 9
    5 BULK_LOGGED 2.24 33.28 1 9
    6 FULL 34.87 21.65 1 87
    7 FULL 2.49 96.02 1 10
    8 SIMPLE 0.74 57.7 1 3
    9 SIMPLE 6.68 26.84 1 16
    10 SIMPLE 6.68 19.8 1 16
    11 FULL 24.8 25.3 1 65
    12 FULL 5.05 85.96 1 18
    13 SIMPLE 160.8 12.03 1 151
    14 SIMPLE 5.55 61.60 1 20
    15 SIMPLE 42.24 26.34 1 95
    17 FULL 3.05 80.43 1 12
    18 FULL 2.24 95.8 1 9
    19 FULL 6623.74 93.4 1 403
    20 FULL 7286.12 95.08 1 411
    21 SIMPLE 109.74 8.37 1 135
    22 FULL 6.74 98.05 1 24

  12. DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 1.74 55.38 1 7
    2 SIMPLE 3071.98 51.78 2 32
    3 FULL 90.68 96.36 1 127
    4 SIMPLE 21.62 33.66 1 67
    5 SIMPLE 1.99 27.06 1 4
    6 SIMPLE 1.99 34.9 1 4
    7 FULL 417.12 3.47 1 177
    8 FULL 8661.55 0.42 1 426
    9 FULL 1057.93 3.28 1 250
    10 FULL 97.43 11.33 1 130
    11 SIMPLE 150859 0.11 1 892
    12 SIMPLE 44562.3 0.17 1 683
    13 FULL 21178.12 0.19 1 558
    14 FULL 26.8 19.86 1 76
    15 FULL 4318.05 2.84 1 367
    16 FULL 513.37 6.59 1 200
    17 FULL 787.24 1.93 1 226
    18 FULL 3.37 35.22 1 13
    19 FULL 1477.3 1.38 1 277
    20 FULL 28.8 19.87 1 79
    21 FULL 120.74 11.29 1 139
    22 FULL 5.05 25.37 1 18
    23 FULL 961.99 1.67 1 241
    24 FULL 818.8 1.72 1 200
    25 FULL 4.12 26.46 1 15
    26 SIMPLE 8113.8 0.43 1 40
    27 FULL 11387.62 6.73 1 172
    28 FULL 1.24 43.83 1 5
    29 FULL 8.18 26.53 1 29
    30 FULL 0.74 65 1 3
    31 FULL 0.74 66.78 1 3
    32 FULL 26.68 20.33 1 45
    33 FULL 11.24 27.17 1 9
    34 FULL 272.74 4 1 128
    35 FULL 49.99 1.61 1 4
    36 FULL 4.12 29.48 1 15
    37 FULL 0.99 50.64 1 4
    38 FULL 90.68 14.04 1 127
    39 FULL 21.99 48.71 1 4
    40 FULL 3.74 26.64 1 14
    41 SIMPLE 194.62 8.44 1 159
    42 FULL 28.99 12.54 1 4
    43 FULL 154.37 6.18 1 136
    44 FULL 51.99 7.64 1 4
    45 SIMPLE 4.62 52.24 1 16
    46 SIMPLE 5889.37 0.51 1 296
    1 SIMPLE 1.74 28.25 1 7
    2 SIMPLE 0.49 90.48 1 2
    3 FULL 0.74 36.32 1 3
    4 SIMPLE 7.43 16.93 1 26
    5 FULL 6.12 18.57 1 22
    6 SIMPLE 0.74 60.26 1 3
    7 SIMPLE 10667.68 0.51 1 443
    8 FULL 42.24 28.7 1 95
    9 FULL 12907.99 0.82 1 475
    10 FULL 1057.93 23.97 1 250
    11 SIMPLE 150859 0.24 1 892
    12 SIMPLE 44562.3 0.27 1 683
    13 FULL 285.05 2.49 1 175
    14 FULL 8661.55 0.18 1 426
    1 SIMPLE 3.37 28.03 1 13
    2 SIMPLE 99.99 47.41 1 8
    3 FULL 7.43 96.19 1 26
    4 SIMPLE 0.74 64.93 1 3
    5 SIMPLE 0.74 43.29 1 3
    6 BULK_LOGGED 0.99 35.33 1 4
    7 FULL 272.87 18.46 1 21
    8 SIMPLE 1.24 36.75 1 5
    9 FULL 0.99 40.26 1 4
    11 SIMPLE 0.74 65 1 3
    12 BULK_LOGGED 4.99 28.96 1 4
    13 BULK_LOGGED 11.99 23.72 1 4
    14 BULK_LOGGED 11.99 4.66 1 4
    15 BULK_LOGGED 14.62 10.45 1 12
    16 BULK_LOGGED 11.99 3.1 1 4
    17 BULK_LOGGED 4.99 23.57 1 4
    18 BULK_LOGGED 11.99 57.5 1 4
    19 FULL 11.99 3.06 1 4
    20 FULL 4.99 17.7 1 4
    21 FULL 9.99 4.96 1 4
    22 FULL 11.99 28.42 1 4
    23 FULL 84.74 9.51 1 120
    24 FULL 38.05 15.97 1 90
    25 FULL 35.68 16.59 1 88
    26 FULL 4.87 9.68 1 6
    27 FULL 117.05 13.47 1 99
    28 FULL 122.8 5.55 1 101
    29 FULL 69.99 10.81 1 112
    30 BULK_LOGGED 1189.3 100 1 218
    32 FULL 1.24 49.53 1 5
    1 SIMPLE 2.49 30.07 1 10
    2 SIMPLE 998.99 13.77 1 8
    3 FULL 5.55 98.3 1 20
    4 SIMPLE 0.74 57.63 1 3
    5 SIMPLE 0.74 44.21 1 3
    6 BULK_LOGGED 0.99 36.32 1 4
    7 FULL 1.74 41.84 1 7
    8 SIMPLE 0.99 45.18 1 4
    9 FULL 4.99 17.29 1 4
    12 BULK_LOGGED 5.05 31.14 1 14
    14 BULK_LOGGED 19.62 21.71 1 59
    15 BULK_LOGGED 6.12 21.12 1 18
    16 BULK_LOGGED 5.05 21.75 1 14
    17 BULK_LOGGED 13.3 22.92 1 43
    18 BULK_LOGGED 192 20.73 1 4
    19 BULK_LOGGED 897.68 2.67 1 231
    20 BULK_LOGGED 17.8 22.65 1 59
    21 BULK_LOGGED 187.31 19.8 1 3
    22 FULL 0.99 41.29 1 4
    23 BULK_LOGGED 295.43 8.27 1 22
    24 BULK_LOGGED 187.31 26.02 1 3
    25 BULK_LOGGED 8.99 26.33 1 32
    26 BULK_LOGGED 187.31 2.11 1 3
    27 FULL 1.24 35.14 1 5
    28 FULL 0.99 41.78 1 4
    29 FULL 0.99 37.45 1 4
    30 FULL 428.37 3.2 1 192
    31 BULK_LOGGED 31.87 12.17 1 52

  13. DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 0.49 64.29 1 2
    2 SIMPLE 4976.43 37.6 1 379
    3 SIMPLE 0.49 83.33 1 2
    4 SIMPLE 3.05 44.12 1 12
    5 SIMPLE 112619.99 0.06 1 338
    6 SIMPLE 459.12 3.88 1 195
    7 FULL 25094.99 1.95 1 37
    8 FULL 9239.87 1.19 1 230
    9 SIMPLE 0.99 50.15 1 4
    10 SIMPLE 19.62 26.4 1 63
    11 FULL 2082.8 42.99 1 259
    12 FULL 15872.3 40.58 1 470
    13 FULL 369.62 47.3 1 175
    14 FULL 22526.99 90.42 1 168
    15 SIMPLE 9.99 20.43 1 4
    16 FULL 24072.8 29.22 1 524
    17 FULL 16.99 3.16 1 4
    18 SIMPLE 3398.87 0.74 1 347
    19 SIMPLE 90.68 9.56 1 123
    20 FULL 417.37 91.75 1 191

  14. From 2 servers:

    1 SIMPLE 2.74 37.32 1 11
    2 SIMPLE 417.37 65.42 1 191
    3 FULL 0.49 48.41 1 2
    4 SIMPLE 34.87 37.59 1 87
    5 FULL 671.99 25.8 1 197
    6 FULL 79.05 80.32 1 108
    7 FULL 82.43 72.08 1 123
    8 FULL 56.3 93.65 1 107
    9 FULL 82.43 72.1 1 123
    10 FULL 190.12 97.48 1 158
    11 FULL 331.05 95.55 1 168
    12 FULL 79.05 90.42 1 108
    13 FULL 150.99 97.22 1 135
    14 FULL 109.74 97.6 1 135
    15 FULL 194.43 94.77 1 145
    16 FULL 4974.55 91.22 1 365
    17 BULK_LOGGED 645.3 93.01 1 196
    18 FULL 79.05 94.19 1 108
    19 BULK_LOGGED 90.68 93.37 1 127
    20 FULL 555.3 99.42 1 189
    21 FULL 79.05 77.28 1 108
    22 FULL 132.87 89.28 1 143
    23 FULL 154.37 81.87 1 136
    24 FULL 2110.3 2.54 1 307
    25 FULL 56.3 93.35 1 107
    26 FULL 3854.05 66.08 1 345
    27 FULL 140.3 93.9 1 132
    28 FULL 194.62 51.53 1 159
    29 FULL 56.3 93.57 1 107
    30 FULL 1257.74 33.87 1 252
    31 FULL 79.05 74.65 1 108
    32 FULL 3166.87 96.37 1 341
    33 FULL 79.05 75.61 1 108
    34 FULL 417.37 31.42 1 191
    35 FULL 3723.3 96.82 1 343
    36 FULL 7825.43 98.09 1 418
    37 FULL 79.05 73.75 1 108
    38 FULL 235.55 93.7 1 167
    39 FULL 79.05 74.7 1 108
    40 FULL 859.37 95.67 1 231
    41 FULL 555.3 97.36 1 189
    42 FULL 61.87 93.79 1 97
    43 FULL 109.74 9.79 1 135
    44 FULL 80.99 91.9 1 109
    45 FULL 79.05 74.69 1 108
    46 FULL 79.05 79.32 1 108
    47 FULL 86.99 99.38 1 112
    48 FULL 56.3 93.14 1 107
    49 FULL 313.3 94.1 1 165
    50 FULL 1343.62 98.7 1 268
    51 FULL 2321.37 98.6 1 315
    52 FULL 26.68 83.1 1 76
    53 FULL 79.05 74.46 1 108
    54 FULL 79.05 73.88 1 108
    55 FULL 79.05 57.38 1 108
    56 FULL 1441.3 31.67 1 275

    1 SIMPLE 0.99 53.94 1 4
    2 SIMPLE 6.74 59.44 1 24
    3 FULL 1.74 94.84 1 7
    4 SIMPLE 132.87 7.6 1 143
    5 FULL 10.93 25.62 1 39
    6 FULL 8.05 30.04 1 11
    7 FULL 571.8 7.21 1 196
    8 FULL 19.62 23.85 1 63
    9 FULL 3398.87 3.28 1 347
    10 FULL 19.62 22.14 1 59
    11 FULL 132.87 7.95 1 143
    12 FULL 5.52 65.07 1 20
    13 FULL 9.87 95.79 1 35
    14 SIMPLE 0.48 77.92 1 2
    15 FULL 99.74 10.68 1 131
    16 FULL 6.12 25.81 1 22
    17 FULL 132.87 81.08 1 143
    18 FULL 7.49 20.47 1 6
    19 FULL 1.49 35.6 1 6
    20 FULL 34.87 18.26 1 87
    21 FULL 313.55 4.31 1 179
    22 FULL 3.74 32.75 1 14
    23 SIMPLE 28.8 19.11 1 79

  15. 1 SIMPLE 1.24 50.63 1 5
    2 SIMPLE 259.12 66.23 1 171
    3 FULL 82.43 95.88 1 123
    4 SIMPLE 21.62 42.07 1 67
    5 FULL 739.49 3.27 1 219
    6 FULL 38.37 16.31 1 91
    7 FULL 111.24 6.3 1 73
    8 FULL 2.74 40.1 1 4
    9 FULL 14.99 8.57 1 12
    10 FULL 89.99 7.52 1 93

    1 SIMPLE 1.24 43.4 1 5
    2 SIMPLE 2.24 55.92 1 9
    3 FULL 82.43 96.3 1 123
    4 SIMPLE 34.87 26.16 1 87

    1 SIMPLE 1.24 47.17 1 5
    2 SIMPLE 28.8 21.58 1 79
    3 SIMPLE 1.74 43.95 1 7
    4 SIMPLE 2.49 47.96 1 10
    5 SIMPLE 1.99 18.77 1 4
    6 FULL 12.38 8.08 1 2
    7 FULL 189.93 7.03 1 144
    8 FULL 0.99 46.16 1 4
    9 FULL 14.99 9.44 1 12
    10 FULL 2.74 32.44 1 4
    11 FULL 24.99 21.13 1 4
    12 FULL 9697.87 0.67 1 435
    13 SIMPLE 2047.99 0.08 1 18
    14 FULL 0.99 35.48 1 4
    15 FULL 99.99 3.98 1 8

    1 SIMPLE 2.24 30.81 1 9
    2 SIMPLE 38.37 45.64 1 91
    3 FULL 5.05 96.87 1 18
    4 SIMPLE 6.49 36.4 1 26
    5 FULL 0.99 50.79 1 4
    6 FULL 3929.3 1.47 1 361
    23 FULL 0.74 43.49 1 3
    24 FULL 26.18 19.58 1 75
    25 FULL 0.48 71.47 1 2
    27 FULL 0.99 46.46 1 4
    28 FULL 99.74 9.2 1 131
    29 FULL 1.74 36.86 1 7
    30 FULL 28.8 19.46 1 79
    33 FULL 8172.12 0.41 1 360

    1 SIMPLE 2.49 37.95 1 10
    2 SIMPLE 34.87 33.65 1 87
    3 FULL 3.05 95.16 1 12
    4 SIMPLE 2.24 47.47 1 9
    5 FULL 4011.99 0.9 1 292

    1 SIMPLE 0.49 62.7 1 2
    2 SIMPLE 194.43 9.7 1 120
    3 FULL 0.49 57.14 1 2
    4 SIMPLE 0.49 80.16 1 2
    5 SIMPLE 1.74 44.34 1 7
    6 SIMPLE 2047.99 5.98 1 20
    7 SIMPLE 0.99 50.44 1 4
    8 FULL 0.54 49 1 2

  16. Server 1
    1 SIMPLE 0.99 51.18 1 4
    2 SIMPLE 1.24 67.57 1 5
    3 FULL 0.49 63.49 1 2
    4 SIMPLE 5.55 22.64 1 20
    5 SIMPLE 16.18 56.9 1 55
    6 SIMPLE 4.12 29.36 1 15
    7 SIMPLE 5.55 37.13 1 20
    8 SIMPLE 1.49 66.75 1 6
    9 SIMPLE 16.18 61.27 1 55
    10 SIMPLE 1.24 60.02 1 5
    11 SIMPLE 14.68 61.38 1 51

    Server 2
    1 SIMPLE 0.74 53.68 1 3
    2 SIMPLE 672.24 14.59 1 211
    3 FULL 5.05 97.3 1 18
    4 SIMPLE 1.24 45.28 1 5
    5 SIMPLE 0.74 48.42 1 3
    6 SIMPLE 2.49 37.36 1 10
    7 SIMPLE 0.99 66.09 1 4
    8 SIMPLE 0.74 66.18 1 3
    9 SIMPLE 0.74 68.36 1 3
    10 SIMPLE 19.62 23.34 1 63
    11 SIMPLE 1.99 34.14 1 8
    12 SIMPLE 1.49 63.61 1 6
    13 SIMPLE 1.99 39.8 1 8
    14 SIMPLE 12.05 52.86 1 43
    15 SIMPLE 0.74 67.11 1 3
    16 SIMPLE 9.99 5.64 1 4
    17 SIMPLE 1.24 57.59 1 5

  17. 1 SIMPLE 4,55 38,59 1 16
    2 SIMPLE 2808,93 22,23 1 331
    3 SIMPLE 0,49 42,86 1 2
    4 SIMPLE 184,55 5,45 2 23
    5 SIMPLE 0,99 56,15 1 4
    6 SIMPLE 74,93 11,38 1 119
    7 SIMPLE 0,8 51,03 1 3
    8 SIMPLE 2,55 36,91 1 10
    9 SIMPLE 0,99 57,97 1 4
    10 SIMPLE 0,99 58,42 1 4
    11 FULL 7096,74 0,94 1 410
    12 SIMPLE 0,48 42,14 1 2
    15 SIMPLE 1,49 65,8 1 6
    16 SIMPLE 0,48 42,14 1 2
    19 SIMPLE 0,99 49,26 1 4
    22 SIMPLE 0,48 42,14 1 2
    23 SIMPLE 0,99 33,71 1 4
    24 SIMPLE 4045,55 1,77 1 347
    39 SIMPLE 24,88 17,87 1 2
    51 SIMPLE 62,38 0,33 1 2
    55 SIMPLE 0,99 44,44 1 4
    56 SIMPLE 31,13 0,66 1 2
    58 SIMPLE 0,99 54,63 1 4

  18. Here is one of my Production Servers.

    1 SIMPLE 1.99 48.43 1 8
    2 SIMPLE 9999.99 58.44 1 16
    3 FULL 16.18 28.22 1 55
    4 SIMPLE 1310.24 1.64 1 267
    5 FULL 6557.37 1.02 1 343
    6 FULL 4976.43 1.52 1 379
    7 FULL 235.55 4.82 1 167
    8 FULL 38686.62 0.53 1 659
    9 FULL 1082.8 1.83 1 251
    10 FULL 18624.18 0.2 1 510
    11 FULL 82.43 10.65 1 123
    12 FULL 17395.3 0.24 1 525
    13 FULL 139.87 5.35 1 92
    14 FULL 11759.74 0.48 1 343
    15 FULL 53352.68 0.09 1 713
    16 FULL 1336.05 1.06 1 208
    17 FULL 0.99 39.67 1 4
    18 FULL 9.8 17.53 1 17
    19 FULL 4112.68 0.69 1 363
    20 FULL 16.99 23.61 1 4
    21 FULL 49.99 14.67 1 4
    22 FULL 27.49 6.47 1 24
    23 FULL 2017.74 1.4 1 113
    24 FULL 16.99 25.62 1 4
    25 FULL 23.3 16.3 1 53
    26 FULL 127.12 6.35 1 88
    27 FULL 16.99 16.07 1 4
    28 FULL 12.99 15.55 1 4
    29 BULK_LOGGED 3.05 31.03 1 12
    30 FULL 21.93 23.09 1 67
    31 SIMPLE 300.05 6.87 1 124
    32 FULL 19.99 7 1 16
    38 FULL 33.87 13.15 1 62
    42 FULL 260.74 4.07 1 136

  19. DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    —— ————— ———————- ———————- ————– ———–
    1 SIMPLE 1,24 61,01 1 5
    2 SIMPLE 1191,12 65,61 1 259
    3 FULL 0,99 54,33 1 4
    4 SIMPLE 3,37 42,69 1 13
    5 SIMPLE 3,12 49,31 1 12
    6 SIMPLE 51,99 30,11 1 103
    7 SIMPLE 1457,18 1,58 1 198
    8 FULL 15683,24 2,12 1 374
    9 SIMPLE 529,99 4,01 1 216
    10 SIMPLE 1621,93 3,49 1 124

  20. 1 SIMPLE 0.99 48.43 1 4
    2 SIMPLE 74.93 19.39 1 119
    3 FULL 0.49 60.32 1 2
    4 SIMPLE 0.99 70.08 1 4
    5 FULL 7295.74 3.37 1 109
    6 FULL 639.69 13.89 1 5

  21. Server 1
    DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 1.49 46.86 1 6
    2 SIMPLE 36.3 46.71 1 27
    3 FULL 10.93 26.13 1 39
    4 SIMPLE 0.74 65.79 1 3
    5 FULL 1119.24 3.51 1 253
    6 SIMPLE 1.31 58.78 1 3
    7 SIMPLE 1104.12 1.8 1 224
    8 SIMPLE 7.55 42.26 1 26
    9 FULL 146.18 38.07 1 147
    10 FULL 11808.05 2.07 1 456
    11 SIMPLE 34.8 48.7 1 79
    12 FULL 285.05 8.23 1 175
    13 FULL 22.18 16.59 1 33
    14 FULL 1622.99 1.79 1 284
    15 FULL 293.3 10.65 1 176
    16 SIMPLE 1.31 47.62 1 3
    17 SIMPLE 21.93 25.9 1 67
    18 FULL 6004.24 1.25 1 394
    19 SIMPLE 21.93 51.83 1 67
    20 FULL 19.68 39.97 1 28
    21 SIMPLE 0.99 65.8 1 4
    22 FULL 33.74 42.92 1 86
    23 SIMPLE 1.99 46.03 1 4
    24 SIMPLE 10.99 23.25 1 4
    25 SIMPLE 10.99 15.92 1 4
    26 SIMPLE 10.99 15.85 1 4
    27 FULL 10.99 91.71 1 4
    28 SIMPLE 4.18 40.74 1 15
    29 SIMPLE 4.18 50.33 1 15
    30 FULL 12.43 21.93 1 9
    31 FULL 8864.3 9.14 1 427
    32 FULL 319.74 7.72 1 180
    33 SIMPLE 1.31 59.6 1 3
    34 SIMPLE 1.31 43.12 1 3
    35 SIMPLE 1.31 67.41 1 3
    36 FULL 912.49 8.33 1 236
    37 FULL 10.99 8.05 1 4
    38 FULL 3056.87 1.02 1 338

    Server 2
    DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 1.49 62.57 1 6
    2 SIMPLE 313.55 23.32 1 179
    3 FULL 0.99 54.33 1 4
    4 SIMPLE 8.18 68.77 1 29
    5 SIMPLE 1.74 36.21 1 7
    6 SIMPLE 0.74 41.58 1 3
    7 SIMPLE 1056.93 2.46 1 250
    8 SIMPLE 363.05 4.91 1 185
    9 SIMPLE 1.99 32.94 1 4
    10 SIMPLE 0.99 53.59 1 4
    11 SIMPLE 0.74 39.01 1 3
    12 SIMPLE 0.99 53.54 1 4
    13 SIMPLE 2.05 46.86 1 8
    14 SIMPLE 0.99 58.02 1 4
    15 SIMPLE 5.99 6.01 1 4
    16 SIMPLE 112.74 2.77 1 13
    17 SIMPLE 0.54 76.72 1 2
    18 SIMPLE 1.23 52.25 1 5
    19 SIMPLE 0.48 80.54 1 2
    20 SIMPLE 3.24 29.22 1 13
    21 SIMPLE 1.99 34.07 1 4
    22 SIMPLE 0.99 50.1 1 4
    23 SIMPLE 3.99 28.38 1 16
    24 FULL 14.68 98.86 1 51
    25 SIMPLE 1.99 31.59 1 4

    Server 3
    DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 21.62 22.34 1 67
    2 SIMPLE 3.74 53.24 1 14
    3 FULL 14.68 93.42 1 51
    4 SIMPLE 22.49 28.19 1 90
    5 SIMPLE 4.12 27.42 1 15
    6 SIMPLE 0.99 61.81 1 4
    7 SIMPLE 0.99 47.1 1 4
    8 SIMPLE 0.48 56.85 1 2
    9 SIMPLE 0.48 54.33 1 2
    10 FULL 14.99 76.87 1 12
    11 FULL 3924.99 99.46 1 3140
    12 FULL 14.99 76.64 1 12
    13 FULL 12.05 96.48 1 43
    14 FULL 19.99 76.7 1 16
    15 FULL 14.99 84.27 1 12
    16 FULL 14.99 69.96 1 12
    17 FULL 84.99 96.8 1 68
    18 FULL 2585.37 92.58 1 324
    19 FULL 705.74 20.12 1 215
    20 FULL 6680.62 92.48 1 407
    21 FULL 1025.49 94.74 1 247
    22 SIMPLE 2808.93 0.88 1 331
    23 FULL 14.99 70.02 1 12
    24 FULL 14.99 69.88 1 12

  22. 1 SIMPLE 2.24 35.69 1 9
    2 SIMPLE 51.18 56.29 1 103
    3 FULL 0.49 44.74 1 2
    4 SIMPLE 2.24 42.99 1 9
    5 SIMPLE 24.88 18.01 1 2
    6 SIMPLE 491.68 3.03 1 211
    8 SIMPLE 619.62 1.24 1 104
    9 SIMPLE 37.3 14.56 1 79
    11 FULL 0.99 40.94 1 4
    13 SIMPLE 14.88 2.79 1 2
    14 SIMPLE 24.12 8.99 1 22
    15 SIMPLE 55.87 4.32 1 30
    17 SIMPLE 14.88 10.34 1 2
    18 SIMPLE 4.99 7.77 1 4
    21 SIMPLE 1262.43 4.14 1 126
    23 SIMPLE 20.12 21.5 1 64
    24 SIMPLE 9.99 3.84 1 4
    25 SIMPLE 14.88 3.9 1 2

  23. SRV1
    DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 0,74 55,92 1 3
    2 SIMPLE 3171,24 45,23 1 200
    3 FULL 1,74 88,71 1 7
    4 SIMPLE 2,24 58,14 1 9
    5 FULL 34376,86 0,11 2 544
    6 SIMPLE 0,99 34,15 1 4
    7 FULL 675,99 3,44 1 58
    8 SIMPLE 255,88 10,63 1 2
    9 FULL 0,74 64,54 1 3
    10 SIMPLE 8,18 26,58 1 29
    11 SIMPLE 8,93 33,84 1 18

    SRV2
    DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 0,74 52,7 1 3
    2 SIMPLE 3,05 63,81 1 12
    3 FULL 0,49 56,85 1 2
    4 SIMPLE 0,74 58,22 1 3
    5 SIMPLE 0,48 47,28 1 2
    6 BULK_LOGGED 0,99 51,03 1 4
    7 FULL 4470,62 1,07 1 202
    9 FULL 0,99 33,46 1 4
    10 FULL 0,99 35,43 1 4
    11 FULL 255,88 2,82 1 2
    12 FULL 2813,49 83,85 1 229

    SRV3
    DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 0,74 47,5 1 3
    2 SIMPLE 13,24 33,19 1 4
    3 FULL 2,24 56,34 1 9
    4 SIMPLE 0,74 62,43 1 3
    5 FULL 49,99 8,73 1 4
    6 SIMPLE 19,99 3,15 1 8
    7 SIMPLE 1605,93 1,78 1 115
    8 SIMPLE 56,3 16,43 1 107
    9 FULL 517,68 2,6 1 200
    10 FULL 523,93 0,55 1 42
    11 SIMPLE 0,99 35,43 1 4
    12 FULL 49,99 3,1 1 4
    13 FULL 3,37 34,98 1 13
    14 SIMPLE 0,74 40,59 1 3
    17 FULL 250,99 13,48 1 24

    SRV4
    DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
    1 SIMPLE 2,49 32,21 1 10
    2 SIMPLE 99,99 6,1 1 8
    3 FULL 6,74 25 1 24
    4 SIMPLE 23,99 53,67 1 96
    5 FULL 1028,98 4,82 2 15
    6 FULL 0,99 69,49 1 4
    7 FULL 0,99 72,39 1 4
    8 SIMPLE 0,99 65,16 1 4

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.