Sunday, November 19, 2017
 
SQL Server Script Repository
Facebook Twitter LinkedIn Google+ Addthis
 
     

SQL Script - Database Filegrowth Information

By Saleem Hakani on 8/25/2014

This script/SPROC allows you to report filegrowth information from single database or all databases of your local SQL Server instance. It provides you with the following information:

1. Local Server Name

2. Database Name

3. Logical FileName

4. Initial File Size

5. File Growth Value/Percentage

6. Any Growth Restrictions

Please run the SPROC without any parameters to obtain help on how to execute this stored procedure.

--Author: Saleem Hakani (Http://sqlcommunity.com)

CREATE Procedure [dbo].[SQLOPS_FileGrowthREPORT] (@Cmd Varchar(20)=null, @DBName Varchar(255)='ALL') as

Set nocount on

--Author: Saleem Hakani (http://sqlcommunity.com)

--Date: Monday, May 12th 2014

--Desc: This SPROC provides you with filegrowth information for all databases of your local SQL Server instance.

If (@Cmd is null or @Cmd <> 'Execute')

Begin

Print 'Get file growth details of all databases for all files from your local SQL Server instance.'

Print ' ========================================================================='

Print 'Author: Saleem Hakani (http://sqlcommunity.com)'

Print ' '

Print 'Syntax: Exec SQLOPS_FileGrowthREPORT <@cmd>'

Print '@Cmd can be '

Print ' '

Print 'To obtain help for this stored procedure:'

Print 'Example: Exec SQLOPS_FileGrowthREPORT ''Help'';'

Print ' '

Print 'To obtain file growth information for all data and log files for all databases:'

Print 'Exec SQLOPS_FileGrowthREPORT ''Execute'';'

Print ' '

Return

End

If (@Cmd='Execute' and @DBName='ALL')

Begin

Select

UPPER(@@ServerName) as 'SERVER_NAME',

UPPER(DB_Name(Database_ID)) as 'DATABASE_NAME',

UPPER(Name) as LOGICAL_FILENAME,

size/128 as 'CURRENT/INITIAL_FILESIZE_IN_MB',

(

Case is_percent_Growth

when '1' then convert(Varchar,Growth) +' %'

else Convert(Varchar,Growth/128) +' MB' End) as 'FILE_GROWTH',

(

Case max_size

When 0 then 'RESTRICTED'

when -1 then 'UNRESTRICTED'

when 268435456 then 'UPTO 2 TB'

Else 'RESTRICTED ('+convert(varchar,Max_Size/128)+' MB)'

End

) as 'RESTRICTIONS'

from sys.master_files

RETURN

End

If (@Cmd='Execute' and @DBName<>'ALL')

Begin

IF Not Exists (Select Name from Sys.Databases Where Name=@DBName)

Begin

Print 'Database ['+@DBName+'] does not exist. Please enter a valid database name.'

RETURN

End

If Exists (Select Name from Sys.Databases Where Name=@DBName)

Begin

Select

UPPER(@@ServerName) as 'SERVER_NAME',

UPPER(DB_Name(Database_ID)) as 'DATABASE_NAME',

UPPER(Name) as LOGICAL_FILENAME,

size/128 as 'CURRENT/INITIAL_FILESIZE_IN_MB',

(

Case is_percent_Growth

when '1' then convert(Varchar,Growth) +' %'

else Convert(Varchar,Growth/128) +' MB' End) as 'FILE_GROWTH',

(

Case max_size

When 0 then 'RESTRICTED'

when -1 then 'UNRESTRICTED'

when 268435456 then 'UPTO 2 TB'

Else 'RESTRICTED ('+convert(varchar,Max_Size/128)+' MB)'

End

) as 'RESTRICTIONS'

from sys.master_files Where Database_ID=db_ID(@DBName)

--where Max_Size not in (-1, 268435456)

RETURN

End

End

GO

 

 

 

File Restrictions
Filegrowth
Percent Growth
Minimize
Script Categories
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM