How to find database growth size in sql server
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.
Table of Contents
Here is the TSQL script
|
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 |
Here is the result
Here is another TSQL script
|
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); |
Here is the result
If you want to Calculate database size ‘Growth Rates’ on daily basis in SQL Server here is another post.
Facebook Comments Box
Comments
[…] If you want to find database growth size in SQL server instantly here is another […]