SQL SERVER – Find the Growth Size for All files in All Databases – Part 2

A few years ago, I had written a blog post Find the Growth Size for All files in All Databases. The original script had some error related to counting the size as pointed out by the SQL Server expert John Fowler, however, I never got around to correct the blog post as the first comment itself was there to always point out the necessary correction.  I kept on using a modified version of the same script in my Comprehensive Database Performance Health Check and eventually totally forgot to correct the original blog post.

However, recently SQL Server Expert who goes with the name Thumb posted an interesting comment which actually merged my original script with John’s suggestion. The final result is the best of the two worlds. Here is the script which can help to find the growth size for all files in all databases.

SELECT
DB_NAME([database_id]) AS [Database],
type_desc AS [File Type],
name AS [Logical Name],
CONVERT(bigint, size/128.0) AS [Initial Size (MB)],
CASE is_percent_growth
WHEN 1
THEN CONVERT(nvarchar(16), growth) + N'%'
ELSE CONVERT(nvarchar(16), CONVERT(bigint, growth/128.0)) + N' MB'
END AS [Autogrowth],
CASE max_size
WHEN -1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(16), CONVERT(bigint, max_size/128.0))
END AS [Maxsize (MB)],
physical_name AS [File Path]
FROM
sys.master_files WITH (NOLOCK)
ORDER BY
DB_NAME([database_id]) OPTION (RECOMPILE);

The script above returns the following results:

Thanks to SQL Expert Thumb, I will be now onwards, I will replace the above script in my script toolbox. If you are using any such scripts which may be interested to other people, you may share that with me and I will be happy to post them on the blog with due credit to you.

Here is another blog post which you may find interesting: My Thoughts – Autogrow of File ‘SQLAuth_Log’ in Database ‘SQLAuth’ was Cancelled by User or Timed Out After 30020 Milliseconds. First of all, the error message would come when the database file(s) is set to grow automatically and SQL is not able to complete the growth in time. Let’s assume that you start a transaction from an application that causes the file to grow and growth kicks in. The time taken by the transaction would include real work is done (any DML) and the time is taken to grow the file.

Reference: Pinal Dave (https://blog.sqlauthority.com)