How to find the auto growth settings for all the SQL Server databases

Introduction

This script is to get all the auto growth for all the SQL Server databases.

Scenarios

Sometimes, people want to get the auto growth of all the databases in a SQL Server instance. This script will provide some help to get the information.

Script

You can use this script in this way:

1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Select the specified database and create a “New Query”, copy the code from DBsGrowth.sql, paste it and run the script.

After the script finishes running, we’ll get the following figure:

 

 

Here are some code snippets for you reference:

SQL
Edit|Remove
mysql
SELECT smf.name AS LogicalName 
,smf.file_id AS FileID
, smf.physical_name AS FileName 
,CAST(CAST(sf.name AS VARBINARY(256)) AS sysname) AS FileGroupName
,CONVERT (varchar(10),(smf.size*8)) + ' KB' AS Size
,CASE WHEN smf.[max_size]=-1 THEN 'Unlimited' ELSE CONVERT(VARCHAR(10),CONVERT(bigint,smf.max_size)*8) +' KB' END AS MaxSize 
,CASE smf.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR(10),smf.growth) +'%' ELSE Convert(VARCHAR(10),smf.growth*8) +' KB' END AS Growth
,Case WHEN smf.[type]=0 THEN 'Data Only' 
WHEN smf.type = 1 THEN 'Log Only' 
WHEN smf.type = 2 THEN 'FILESTREAM Only' 
WHEN smf.type = 3 THEN 'Informational purposes Only' 
WHEN smf.type = 4 THEN 'Full-text ' 
END AS Usage
,DB_name(smf.database_id) AS DatabaseName
FROM sys.master_files AS smf
LEFT JOIN sys.filegroups AS sf ON ((smf.type = 2 OR smf.type = 0) 
AND (smf.drop_lsn IS NULL)) AND (smf.data_space_id = sf.data_space_id)
 

SELECT smf.name AS LogicalName  
,smf.file_id AS FileID 
, smf.physical_name AS FileName  
,CAST(CAST(sf.name AS VARBINARY(256)) AS sysname) AS FileGroupName 
,CONVERT (varchar(10),(smf.size*8)) + ' KB' AS Size 
,CASE WHEN smf.[max_size]=-1 THEN 'Unlimited' ELSE CONVERT(VARCHAR(10),CONVERT(bigint,smf.max_size)*8) +' KB' END AS MaxSize  
,CASE smf.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR(10),smf.growth) +'%' ELSE Convert(VARCHAR(10),smf.growth*8) +' KB' END AS Growth 
,Case WHEN smf.[type]=0 THEN 'Data Only'  
WHEN smf.type = 1 THEN 'Log Only'  
WHEN smf.type = 2 THEN 'FILESTREAM Only'  
WHEN smf.type = 3 THEN 'Informational purposes Only'  
WHEN smf.type = 4 THEN 'Full-text '  
END AS Usage 
,DB_name(smf.database_id) AS DatabaseName 
FROM sys.master_files AS smf 
LEFT JOIN sys.filegroups AS sf ON ((smf.type = 2 OR smf.type = 0)  
AND (smf.drop_lsn IS NULL)) AND (smf.data_space_id = sf.data_space_id) 
 



 

Prerequisites

SQL Server 2005 or higher version