How to Caluculate Growth rate of data file

  • I need find the growth rate of the datafiles for all the databases in the server. Is there way  I also need to find growth of datafile for all the databases in a server from Jan15th to Feb 15th. How can I do this?

  • Well, I found the link below very helpful for evaluating the size increase of a database.  It will work if you are backing up to a backup set. 

    He also mentions looking at sysfiles (pretty easy) or tracing (takes some effort).

    The other way to do it is to create a #temp table, drop to xp_cmdshell and 'dir' the folder contents to the table and then parse it out...  It takes a little more work. 

    Take care.

    http://vyaskn.tripod.com/track_sql_database_file_growth.htm

  • If you're looking for the growth of the data file itself, rather than of the data within the file, you can look through the SQL error log for file growths... assuming that yours goes back far enough.

    John

  • Really you'd need to track your database size on a daily or weekly period and then use that. Error logs work as well, but if you are having regular growths, you have not properly sized the database.

  • Properly sized the database ,what is the meaning of that? How can we do that?

  • Plz check this link. You have a downloadable ppt with arthimetic calculations on planning.

    http://www.sql-server-performance.com/joe_chang.asp


    Get busy living ....or get busy dying....

  • Hi Arul,

    Just try this

     

    -------------------------------

    create proc usp_databases 

    as 

     set nocount on 

     declare @name sysname 

     declare @SQL  nvarchar(600) 

     

     /* Use temporary table to sum up database size w/o using group by */ 

     create table #databases ( 

          DATABASE_NAME sysname NOT NULL, 

          size int NOT NULL) 

     

     declare c1 cursor for  

      select name from master.dbo.sysdatabases 

       where has_dbaccess(name) = 1 -- Only look at databases to which we have access 

     

     open c1 

     fetch c1 into @name 

     

     while @@fetch_status >= 0 

     begin 

      select @SQL = 'insert into #databases 

        select N'''+ @name + ''', sum(size) from ' 

        + QuoteName(@name) + '.dbo.sysfiles' 

      /* Insert row for each database */ 

      execute (@SQL) 

      fetch c1 into @name 

     end 

     deallocate c1 

     

     select  

      DATABASE_NAME, 

      DATABASE_SIZE = size*8,/* Convert from 8192 byte pages to K */ 

      RUN_DT=GETDATE() 

     from #databases 

     order by 1 

    -----------------------------

     

    Jey 

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply