Survey: transaction log files per database (code to run)

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];
GO

LogFiles    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)

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

    1. 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.

  7. 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.

Leave a Reply

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

Other articles

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.