{"id":675,"date":"2010-07-04T05:20:00","date_gmt":"2010-07-04T05:20:00","guid":{"rendered":"\/blogs\/paul\/post\/Log-file-configuration-metrics-for-17000-databases.aspx"},"modified":"2017-08-14T10:16:42","modified_gmt":"2017-08-14T17:16:42","slug":"log-file-configuration-metrics-for-17000-databases","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/log-file-configuration-metrics-for-17000-databases\/","title":{"rendered":"Log file configuration metrics for 17000 databases"},"content":{"rendered":"

(Happy Independence Day to everyone back home in the USA! We’re in Dublin for another week…)<\/p>\n

Back at the end of April I conducted a survey of transaction log characteristics (see here<\/a>) and now I present the results to you.<\/p>\n

I had a great response to the survey from almost 100 people, and the total data set for these observations is 16841 databases on\u00a0847 SQL Server instances (SQL Server Edition is irrelevant).<\/p>\n

This isn’t the most exciting blog post, but I at least found the numbers interesting – hope you do too! I’ve got some pointers in the summary to log management blog posts.<\/p>\n

Number of Databases Per Instance\u00a0<\/strong><\/p>\n

Each plot on the graph below is a count of the number of instances having that many databases, including the four system databases.<\/p>\n

\u00a0\"\"<\/p>\n

Just under 75%\u00a0of instances have 20 or fewer databases (including system databases). The highest number per instance is 571.<\/p>\n

Number of Log Files Per Database<\/strong><\/p>\n

This wasn’t worth plotting. The results are:<\/p>\n

\r\nLog Files\u00a0\u00a0 Number of Databases\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a016657\r\n2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0158\r\n3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9\r\n4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8\r\n5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\r\n7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n24\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n<\/pre>\n
I’m impressed by this – I expected to see more databases with multiple log files. The one with 24 is tempdb<\/em> – someone setting the number of log files to equal the number of processor cores I’d guess.<\/p>\n
System Databases: Log File Size<\/strong><\/p>\n
Each plot on the graph below shows the number of\u00a0log files\u00a0of that size across all the instances, color-coded for master<\/em>, msdb<\/em> and tempdb<\/em>.<\/p>\n
\u00a0\"\"<\/p>\n
89% of master<\/em> database log files are less than 10MB (a number I arbitrarily chose as a comparison point).<\/p>\n
Only 40% of msdb<\/em> log files are less than 10MB. This would suggest that there are a high proportion of msdb<\/em> databases not using the SIMPLE recovery model. However, only 5% of them aren’t using SIMPLE.<\/p>\n
Only 33% of tempdb<\/em> log files are less than 10MB. I would expect this number to be low.<\/p>\n
Looking at the distribution on the graph, master<\/em> log files (blue)\u00a0are generally smaller, tempdb<\/em> log files (green) are generally larger, with msdb<\/em> (red) in the middle. Again, this is what I’d expect to see.<\/p>\n
System Databases: Number of VLFs<\/strong><\/p>\n
Each plot on the graph below shows the number of\u00a0log files\u00a0with that many VLFs across all the instances, color-coded for master<\/em>, msdb<\/em> and tempdb<\/em>. (If you’re unfamiliar with the term ‘VLFs’, see the TechNet Magazine article I link to at the bottom).<\/p>\n
\u00a0\"\"<\/p>\n
98.6% of all master<\/em> log files have less than 100 VLFs (our arbitrary ‘you may have too many VLFs’ tipping point), with the maximum being 331.<\/p>\n
Only 65% of all msdb<\/em> log files have less than 100 VLFs. From the other 35%, only 5% of them aren’t using the SIMPLE recovery model. The maximum number of VLFs is 7646.<\/p>\n
Surprisingly, 75% of tempdb<\/em> log files have less than 100 VLFs. I expected this number to be higher in general, because of tempdb<\/em> autogrowth. The maximum number of VLFs is 59708.<\/p>\n
Looking at the graph, master<\/em> (blue) is as I expected, but msdb<\/em> (red) and tempdb<\/em> (green) are the opposite of what I expected – with more msdb<\/em> log files having more VLFs.<\/p>\n
User Databases: Log File Size<\/strong><\/p>\n
Out of all 13451 user databases in the sample:<\/p>\n