Posts Tagged ‘Autogrowth’

Sometimes with SQL server a DBA or Developer might be faced an error saying “The transaction log for database is full” OR “Could not allocate space for object because the filegroup is full” OR may be as “Primary filegroup is full” There may be many reason for same but most probable error is related to Data/log file’s AUTOGROWTH option definition at the time of database creation. To resolve this issue commonly DBA may perform a shrink log operation and do changes with the DATA/LOG files AUTOGROWTH/MAXSIZE option. Here is a script to find out all databases’s files AUTOGROWT values as

SELECT DB_NAME(MF.DATABASE_IDAS DBNAME,MF.NAME AS FILENAME ,SIZE/128.0 AS CURRENTSIZE_MB,DB.RECOVERY_MODEL_DESC RECOVERYMODEL, MF.TYPE_DESC AS ‘FILE TYPE (DATA/LOG FILE)’,
CASE WHEN IS_PERCENT_GROWTH = 0 THEN LTRIM(STR(MF.GROWTH * 8.0 / 1024,10,1))+’ MB, ‘ELSE ‘BY ‘+ CAST(MF.GROWTH AS VARCHAR) +’ PERCENT, ‘END + CASE WHEN MAX_SIZE =1 THEN ‘UNRESTRICTED GROWTH’ ELSE ‘RESTRICTED GROWTH TO ‘ +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ‘ MB’ END AS AUTOGROW, MF.PHYSICAL_NAME

FROM SYS.MASTER_FILES MF INNER JOIN SYS.DATABASES DB ON MF.DATABASE_ID =DB.DATABASE_ID
ORDER BY MF.SIZE/128.0 DESC