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

Advertisements
Comments
  1. Thanks for the marvelous posting! I truly enjoyed reading it, you will be a great author.

    I will make certain to bookmark your blog and will come back later in life.
    I want to encourage you to continue your great posts, have a nice morning!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s