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 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
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
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
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?
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
Hey Alex – yup – small typo – now corrected. Thanks!
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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