Find the database file growth for all the databases in SQL Server

Introduction

Find the database file growth for all the databases. It is not a good practice to keep your database file growth limited. In that case, you may get an error like “The Transaction log for database XXXXX is full.

Script

/*-------------------------------------------------------------------------
-- Script: T-SQL Script to Generate Database Growth Commands
-- Author: SOYELUDDIN BISWAS
-- Date: 07/10/2019
-- Email: st.biswas99@gmail.com
-------------------------------------------------------------------------*/

-- Selecting information about database files

SELECT 
    DBS.name as [Database Name],
    DBS.recovery_model_desc as [Recovery Model],
    DBMF.name as [File Name],
    CASE 
        WHEN DBMF.is_percent_growth=1 THEN CAST(DBMF.growth as VARCHAR(10))+' %'
        WHEN DBMF.is_percent_growth=0 THEN CAST(((DBMF.growth*8)/1024) as VARCHAR(10))+' MB'
    END as [File Growth type and Value],
    CASE
        WHEN DBMF.max_size=-1 THEN 'Unlimited'
        WHEN DBMF.max_size!=-1 THEN CAST((CAST (DBMF.max_size AS BIGINT)) * 8 / 1024 AS VARCHAR(10))+' MB'
    END as [File Growth],
    DBMF.physical_name as [File Location]
FROM 
    sys.databases DBS
    INNER JOIN sys.master_files DBMF ON DBS.database_id = DBMF.database_id
WHERE 
    DBS.database_id>4
ORDER BY 
    1 ASC

Result

Also, you clear the transaction log by taking backup which will allow you to perform further transactions. Run the below script to see if there is any log backup pending. If you find “LOG_BACKUP” in log_reuse_wait_desc column then you can take backup else you will need to set the file auto-growth unlimited and extend the storage if you have less storage.

Script

select name, log_reuse_wait_desc from sys.databases