All of a sudden I received an email from the database owner –
“pls share file growth if u have so that the sizing can be done better”
We don’t have any mechanism or 3rd party tool that monitored such historical data. Also, no trace was enabled previously on the server. I started searching on internet for a quick way to find the growth size set for all the databases on a SQL instance. Thanks to blog.sqlauthority.com I got my solution with two TSQL scripts that end up my search.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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); |
If you want to Calculate database size ‘Growth Rates’ on daily basis in SQL Server here is another post.
[…] If you want to find database growth size in SQL server instantly here is another […]