In this survey I'd like to see what the distribution of the number of log files per database is for your servers. I'm planning to do similar surveys through the rest of the year around data files and filegroups too.
Please run the following code (with example output):
SELECT
DISTINCT [LogFiles],
COUNT (*) AS [Databases]
FROM (
SELECT
COUNT (*) AS [LogFiles]
FROM sys.master_files
WHERE [type_desc] = N'LOG'
GROUP BY [database_id]) [LogFilesCounts]
GROUP BY [LogFiles];
GOLogFiles Databases
———– ———–
1 28
2 1
And send me the results for as many servers as you want. You can either email me plaintext or a spreadsheet or append the results as a comment to this post. Please do not add any more info to the results (like server name, version etc.) as it's not relevant for this survey and adds a bunch of time to the results processing.
Note that the script will only work on 2005 onwards.
I'll editorialize the results in a week or two.
Thanks!
46 thoughts on “Survey: transaction log files per database (code to run)”
SQLServerCentral production:
Log Files: 1
Databases: 12
1 14
1 27
1 5
1 5
1 5
1 5
1 6
1 6
1 38
1 8
1 6
1 8
2 1 <—- Don’t ask
1 9
1 10
1 6
1 5
LogFiles Databases
———– ———–
1 66
LogFiles Databases
———– ———–
1 13
2 1
LogFiles Databases
1 8
1 58
1 10
1 10
1 10
1 11
LogFiles Databases
1 347
LogFiles Databases
1 32
LogFiles Databases
1 14
LogFiles Databases
1 18
LogFiles Databases
1 9
LogFiles Databases
1 18
LogFiles Databases
1 12
LogFiles Databases
1 19
LogFiles Databases
1 15
LogFiles Databases
1 16
LogFiles Databases
1 43
1 11
1 31
1 74
1 44
1 32
1 43
1 88
LogFiles Databases
1 12
2 1
LogFiles Databases
1 16
LogFiles Databases
———– ———–
1 49
LogFiles Databases
1 154
LogFiles Databases
1 19
1 19
1 16
1 9
1 8
1 7
1 9
1 8
1 24
1 19
1 22
1 8
1 27
1 18
1 6
1 21
1 15
1 7
1 10
1 17
1 17
1 10
1 7
1 7
1 14
1 8
1 17
LogFiles 1
Databases 68
LogFiles Databases
1 20
1 20
1 6
1 13
1 5
This is from across our environment, test and prod. The database with 4 is in test and belong to an app area that has SA on the instance. Their motto is, "If we can do it wrong, we will."
1 5891
2 27
4 1
LogFiles Databases
———– ———–
1 30
1 48
1 17
1 8
1 23
1 61
LogFiles Databases
1 26
LogFiles Databases
1 8
LogFiles Databases
1 5
LogFiles Databases
1 9
LogFiles Databases
1 12
1 45
1 44
1 24
1 13
1 12
Log files: 1 Prod Databases: 161
LogFiles Databases
1 35
2 1
1 31
2 1
1 35
1 6
1 25
1 41
1 22
2 1
1 29
1 9
1 8
1 9
1 8
1 7
1 45
1 7
1 6
1 11
1 21
1 10
1 13
1 6
1 91
2 1
1 22
2 1
1 4208 <- This is true
1 2089 <- This one too is true
1 12
1 8
1 25
1 31
1 45
1 127
1 5
1 14
1 120
1 4
1 10
1 18
1 137
1 8
1 206
1 200
1 140
1 4
1 4
1 75
1 96
1 192
1 22
1 214
1 169
1 1515
1 62
1 71
1 18
1 30
1 617
1 40
1 83
1 207
1 546
1 52
1 30
1 12
1 152
1 16
1 530
1 74
1 6
1 8
1 36
1 398
1 12
1 6
1 18
1 8
1 4
1 5
1 4
1 30
2 1
1 47
1 9
1 7
1 11
1 7
1 57
1 34
2 1
1 74
2 1
1 98
1 21
LogFiles Databases
1 7
1 23
1 28
1 34
1 15
1 43
LogFiles: 1
Databases: 62
LogFiles Databases
1 28
1 32
LogFiles Databases
1 33
1 28
1 26
LogFiles Databases
1 104
1 9
1 11
1 6
1 8
1 6
1 111
1 23
1 10
1 10
1 8
LogFiles Databases
———– ———–
1 144
1 81
LogFiles Databases
1 6
2 2
1 21
1 27
1 14
1 15
1 16
LogFiles Databases
1 30
LogFiles Databases
1 12
1 6
1 12
1 9
1 29
1 63
1 15
1 20
1 8
1 17
1 83
1 27
1 47
1 19
1 6
1 10
1 24
1 8
LogFiles Databases
1 17
1 17
1 19
1 21
1 18
1 33
1 58
1 31
1 10
1 16
1 62
1 12
1 25
LogFiles Databases
1 34
LogFiles Databases
————————–
1 15
LogFiles Databases
1 16
3 1
4 3
7 1
1 16
4 11
I’ve been told that on log file per CPU as HDD was the best solution.
We apply the same concept for database file. It’s mean we have 4 database file per each database instance
No – that’s totally wrong – you should only have one log file for the database (unless you run out of space). SQL Server does not write in parallel to them and there is absolutely no performance gain from having multiple log files.
Hi Paul,
I ran the query and found the below result.
Logfiles Databases
1 89
3 1
As per my understanding 1 log files for 89 DB’s and second line 3 log files for 1 DB. So could you please share the link which you have demonstrated in Detail about it.
https://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/