I am a big time advocate of some common best practices that I see in the industry. These have been ingrained into every session that I do be it SQL Server, MySQL or any other databases. Though I work extensively on SQL Server and from time-to-time on other databases, the concepts and basics have remained the same across all these variants. One of the practices that I always advocate is the use of “Auto Growth” settings inside SQL Server. The setting is good but I always say, it is there as a safety net. In reality this setting should never be triggered and as a DBA, it is important that we know when the files are getting expanded. As a DBA, you need to build capacity in your environment whenever these settings hit the server. For example, a 10% growth doesn’t look alarming at initial but can be dangerous because 10% of a 1GB database file grows 100MB while for a 100GB database that will grow by 10GB. As a DBA, we are required to be cognizant of the setting and know when the growth happens.
Not very long ago, one of my blog readers sent me below email
Hi Pinal,
My apologies to send you direct email, but this was really urgent. Do you know a quick way to find the growth size set for all the databases on a SQL instance. I was able to figure out that I can use sp_helpfile. Unfortunately it gives details about the files of the database in which I’m running the stored procedure sp_helpfile.
Any quick reply would be appreciated. Thanks in advance!
As always, I searched for it at search.sqlauthority.com and was surprised to see that there were no results. Here is the query which I came up with.
SELECT 'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END
)
,'growth' = (
CASE is_percent_growth
WHEN 1
THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END
)
,'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id
If you find a better way, please share it via comments. The best way to learn is to learn from each other. Do let me know have you ever encountered this situation in your life?
Reference: Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
Hi Pinal,
I’ve read a lot of your blog entries and I’ve learned a lot from you. When I see new scripts presented in a blog I always check to see if I have an equivalent script in my own library. In this case, I discovered that the, very excellent, diagnostic information queries written by Glenn Barry include a similar query to the one you provided. Here is the specific query:
SELECT DB_NAME([database_id]) AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
is_percent_growth, growth,
CONVERT(bigint, growth/128.0) AS [Growth in MB],
CONVERT(bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
I compared the output of both queries and discovered that the growth column in your query may be incorrect. You list it as a value in KB, but I believe it may be pages. In Glenn’s query he divides that number by 128 to get a value in MB. Please review and update your post as you feel necessary.
Thanks,
John
same her, not working
What do you mean by not working?
I think he’s referring to John’s script where the second argument of the WHERE clause is missing an equal Symbol. Your’s is working Pinal
Thanks for posting this. Here’s a merged version of Pinal’s and John Fowler’s queries:
SELECT
--[file_id],
DB_NAME([database_id]) AS [Database],
--state_desc AS [Status],
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)
--WHERE
-- [database_id] > 4
ORDER BY
DB_NAME([database_id]) OPTION (RECOMPILE);
Very nice. I will soon blog it with due credit to you.
Hi I know how much space my DB needs day to day ?
Can any one help me with the database growth to know for last 6 months ?
Below script will help you to get the month wise details of database growth.
SELECT
[database_name] AS “Database”,
DATEPART(month,[backup_start_date]) AS “Month”,
AVG([backup_size]/1024/1024) AS “Backup Size MB”,
AVG([compressed_backup_size]/1024/1024) AS “Compressed Backup Size MB”,
AVG([backup_size]/[compressed_backup_size]) AS “Compression Ratio”
FROM msdb.dbo.backupset
WHERE [database_name] = N’DLBDN222′
AND [type] = ‘D’
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);