Script to capture the growth of All Database size

Posted: February 5, 2009 in Sqlserver

 

I was asked to create the database growth script which can help us to forecast the growth of all the database of our productions server..

Main intention to build this script is to get the growth rate of the database which can be used to do space management.

 

/*Create a table where you want to manage the data*/

 

Use ABCDatabase

Create table tblDataBaseSizes 

( 

 dbname sysname, 

 dbsize  nvarchar(13) null, 

 AsOnDate datetime 

)

 

/* Create a Stored procedure which can get all the database information and store in the table*/

Use ABCDatabase

Create procedure sp_GetAllDBSize 

@dbname sysname = NULL   — database name 

as 

declare @SqlQuery_exec nvarchar(625) 

declare @ListDB bit 

declare @name sysname 

declare @low nvarchar(11) 

declare @tblDataBaseSizes varchar(600) /* the total description for the db */ 

 

set nocount on 

 

/*  List all database name */ 

 

if @dbname is null 

 select @ListDB = 0 

else select @ListDB = 1 

   

select @low = convert(varchar(11),low) from master.dbo.spt_values 

   where type = N’E’ and number = 1 

 

/*   Initialize tblDataBaseSizes from sysdatabases  */ 

 

insert into tblDataBaseSizes (dbname) 

  select name from master.dbo.sysdatabases 

   where (@dbname is null or name = @dbname) 

 

/* Check database access level */ 

 

  select @low = convert(varchar(11),low) from master.dbo.spt_values 

   where type = N’E’ and number = 1 

 

declare db_Cursor cursor global for 

 select dbname from tblDataBaseSizes 

open db_Cursor 

fetch db_Cursor into @name 

while @@fetch_status >= 0 

begin 

 if (has_dbaccess(@name) <> 1) 

 begin 

   delete tblDataBaseSizes where current of db_Cursor 

   raiserror(15622,-1,-1, @name) 

 end 

 else 

  begin 

 

   /* Insert row for each database */ 

  

   select @SqlQuery_exec = ‘update tblDataBaseSizes 

        set dbsize = (select str(convert(dec(15),sum(size))* ‘ + @low + ‘/ 1048576,10,2)+ N” MB” from ‘ 

         + quotename(@name, N'[‘) + N’.dbo.sysfiles) WHERE current of db_Cursor’ 

 

    /*Update insertion Data insertion date*/

   

    update tblDataBaseSizes set AsOnDate=getdate() where AsOnDate is null

  

   execute (@SqlQuery_exec) 

  end

 fetch db_Cursor into @name 

end 

deallocate db_Cursor 

/*Below queries can be used to execute the above created stored procedure*/

Exec  sp_GetAllDBSize

Select dbname, dbsize, asondate from tblDataBaseSizes

 

Create a sql job and schedule to run as per your requirement. In my case we have schedule it to run every week.


 

 

Advertisements
Comments
  1. praveen kumar says:

    https://hassanszone.wordpress.com/2009/02/05/script-to-capture-the-growth-of-all-database-size/

    Hi Hasan,

    I’ve executed your shared script to capture the growth of all database size but i’m getting syntactic errors in the below part of the script.

    /* Insert row for each database */

    select @SqlQuery_exec = ‘update tblDataBaseSizes

    set dbsize = (select str(convert(dec(15),sum(size))* ‘ + @low + ‘/ 1048576,10,2)+ N” MB” from ‘
    + quotename(@name, N’[‘) + N’.dbo.sysfiles) WHERE current of db_Cursor’

    Please correct the script and re-send it to this mail id as soon as possible.

    Thanks
    Praveen

  2. praveen kumar says:

    Hi Hassan,

    Your posts are very good and very helpful. Keep on posting such nice articles

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