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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- ============================ -- -- Owner: SOYELUDDIN BISWAS -- -- Created Date: 07/10/2019 -- -- Email: st.biswas99@gmail.com -- -- ============================ -- 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
|
1 |
select name, log_reuse_wait_desc from sys.databases |
Result
