Arriving at Database Growth Factor in SQL Server 2000

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sMaganti/arrivingatdatabasegrowthfactorinsqlserver2000.asp


    Kindest Regards,

    M Suresh Kumar

  • On our system, the script throws an exception 'integer overflow'. Change the formula : All

    'size * 8192'

    should read

    'size * 8192.0'

  • Guys-

    Try this out

     

    BEGIN

     SET NOCOUNT ON

     IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DataFileStats'))

     BEGIN

      DROP TABLE #DataFileStats

     END

     IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#LogSizeStats'))

     BEGIN

      DROP TABLE #LogSizeStats

     END

     CREATE TABLE #DataFileStats

      (DBName  VARCHAR(255),

      DBId  INT,

      Flag   BIT DEFAULT 0,

      Fileid   TINYINT,

      [FileGroup]  TINYINT,

      TotalExtents  DEC(15,2),

      UsedExtents  DEC(15,2),

      [Name]   SYSNAME,

      [FileName]  SYSNAME)

     CREATE TABLE #LogSizeStats

      (DBName  VARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED,

      DBId  INT,

      LogFile  DEC(15,2),

      LogFileUsed DEC(15,2),

      Status  BIT)

     INSERT INTO #LogSizeStats (DBName,LogFile,LogFileUsed,Status)

     EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS')

     UPDATE #LogSizeStats

     SET  DBId   = DB_ID(DBName),

      LogFileUsed  = LogFile*LogFileUsed/100.0

     DECLARE @tblSysFiles TABLE

      (DBId  INT,

      FileId  INT,

      GroupId  INT,

      FileNamePath VARCHAR(400),

      LogiFileName VARCHAR(60),

      FileSize DEC(15,2),

      FileSizeMax DEC(15,2),

      FileSizeGrowth DEC(15,2),

      GrowthType VARCHAR(15),

      Status  INT)

     INSERT INTO @tblSysFiles

      (FileSize,

      FileSizeMax,

      FileSizeGrowth,

      GrowthType,

      Status,

      LogiFileName,

      FileNamePath,

      DBId,

      FileId,

      GroupId)

     SELECT CAST([Size]*8/1024.0 AS DEC(15,2)) AS Size_MB,

      CAST([MaxSize]*8/1024 AS DEC(15,2)) AS MaxSize_MB,

      CASE

       WHEN CAST([Growth]*8/1024 AS DEC(15,2)) = 0.0 THEN CAST(CAST([Size]*8/1024.0 AS DEC(15,2))*10/100.00 AS DEC(15,2))

       ELSE CAST([Growth]*8/1024 AS DEC(15,2))

      END AS Growth,

      CASE

       WHEN CAST([Growth]*8/1024 AS DEC(15,2)) = 0.0 THEN 'MB (%Basis)'

       ELSE 'MB'

      END AS GrowthType,

      Status,

      RTRIM([Name]) AS LogicalFileName,

      RTRIM([FileName]) AS FileNamePath,

      DBId,

      FileId,

      GroupId

     FROM Master..SysAltFiles

     ORDER BY DBId,FileId,GroupId

     DECLARE @SQLString  SYSNAME

     DECLARE @MinId  INT

     DECLARE @MaxId  INT

     DECLARE @DBName  VARCHAR(255)

     DECLARE @tblDBName TABLE

      (RowId  INT IDENTITY(1,1),

      DBName  VARCHAR(255),

      DBId  INT)

     INSERT INTO @tblDBName (DBName,DBId)

     SELECT [Name],DBId FROM Master..SysDataBases WHERE (Status & 512) = 0 /*NOT IN (536,528,540,2584,1536,512,4194841)*/ ORDER BY [Name]

     SELECT @MinId = MIN(RowId),

      @MaxId = MAX(RowId)

     FROM @tblDBName

     WHILE (@MinId <= @MaxId)

     BEGIN

      SELECT @DBName = [DBName]

      FROM @tblDBName

      WHERE RowId = @MinId

      SELECT @SQLString = 'USE ' + @DBName + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'

       

      INSERT INTO #DataFileStats (Fileid, [FileGroup] , TotalExtents , UsedExtents , [Name] , [FileName])

      EXEC (@SQLString)

      UPDATE #DataFileStats

      SET DBName  = RTRIM(@DBName),

       DBId = DB_ID(@DBName)

      WHERE DBName IS NULL 

      SELECT @MinId = @MInId + 1

     END

     UPDATE #DataFileStats

     SET TotalExtents  = TotalExtents*8*8192.0/1048576.0,

      UsedExtents = UsedExtents*8*8192.0/1048576.0  

     SELECT A.DbId,

      A.FileId,

      A.GroupId,

      A.FileNamePath AS PhysicalFileName,

      A.FileSize AS ActSize_MB,

      A.FileUsed AS Used_MB,

      CAST((A.FileSize - A.FileUsed) AS DEC(15,2)) AS UnUsed_MB,

      CASE WHEN A.FileSize <> 0 THEN CAST((100-(A.FileUsed / A.FileSize)*100) AS DEC(15,2)) ELSE 0 END AS [%UnUsed],

      A.FileSizeMax AS [MaxSize_MB],

      CASE WHEN A.FileSizeMax <> 0.00 THEN 'Restricted' ELSE 'Un-Restricted' END AS Growth,

      A.FileSizeGrowth AS SizeGrowth,

      A.GrowthType,

      A.LogiFileName AS LogicalFileName

     FROM (

      SELECT A.DbId,

       A.FileId,

       A.GroupId,

       A.FileNamePath,

       A.LogiFileName,

       A.FileSize,

       Data.UsedExtents AS FileUsed,

       A.FileSizeMax,

       A.FileSizeGrowth,

       A.GrowthType

      FROM @tblSysFiles AS A

       LEFT JOIN #DataFileStats AS Data

        ON A.DBId = Data.DBId

        AND A.FileId = Data.FileId

        AND  A.GroupId = Data.FileGroup

      WHERE A.GroupId <> 0

      UNION

      SELECT A.DbId,

       A.FileId,

       A.GroupId,

       A.FileNamePath,

       A.LogiFileName,

       A.FileSize,

       Data.LogFileUsed AS FileUsed,

       A.FileSizeMax,

       A.FileSizeGrowth,

       A.GrowthType

      FROM @tblSysFiles AS A

       LEFT JOIN #LogSizeStats AS Data

        ON A.DBId = Data.DBId

      WHERE A.GroupId = 0

     &nbsp AS A

     ORDER BY DB_NAME(DBId),FileId,GroupId

    END

    Just do a filter on the DBs you are looking for

     

    Regards

    Ram Ramamoorthy

     

     

  • I just read this article and found it very interesting. I am a Product Manager at Quest Software and currently running a beta program for Capacity Management product. I would be interested to hear your feedback.
     

    Capacity Management for SQL Server provides trend analysis of current disk use, forecasts of future disk space requirements, and notification when there is an immediate storage need.

     

    To register for the beta program please visit http://www.quest.com/beta and click on Capacity Management for SQL Server 1.0

    Regards,

    Hassan Fahimi

  • Suresh:

    I modified your code to handle strange database names and large databases.

    /*********************************************************************************************

    Script Name: Sysfiles_Growth.txt

    Purpose    : Displaying Auto-Growth factors of database files based on SYSFILES.

    Created On : October 31, 2005

    Author     : Suresh Kumar Maganti

    Modified   : Steve Bergkamp.  See "Modified" notes

    *********************************************************************************************/

    set ansi_warnings off

    declare @l_db_name       varchar(95)

           ,@l_sql_string    varchar(2000)

    set nocount on

    if object_id('DB_Growth') is not null

    drop table DB_Growth

    --Modified filename size

    create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(35), File_Size_MB int, Growth_Factor varchar(100))

     

    declare db_name_cursor insensitive cursor

    for

    select name from master..sysdatabases

    open db_name_cursor

    fetch next from db_name_cursor into

                             @l_db_name

    While (@@fetch_status = 0)

    --ceiling((growth * 8192)/(1024.0*1024.0))

    begin

    --Modified size calculation so that G size databases could be handled.

       select @l_sql_string = 'select ' + '''' +@l_db_name +'''' + ', name, ceiling((CONVERT(numeric(9,2),size)* 8192/(1024.0 * 1024.0))), case when status & 0x100000 = 0 then convert(varchar,ceiling((CONVERT(numeric(9,2),growth)* 8192/(1024.0 * 1024.0)))) + '' MB''' + char(10)+char(13)

                      + 'else  convert (varchar, growth) + '' Percent''' + char(10)+char(13)

                + 'end' + char(10)+char(13)

                             + 'from [' + @l_db_name + '].dbo.sysfiles' --Modified database name handler so that "-" could be used

       insert into DB_Growth ([Database_Name], Logical_File_Name, File_Size_MB, Growth_Factor)  

     

       exec (@l_sql_string)

       fetch next from db_name_cursor into

                                @l_db_name

    end

    close db_name_cursor

    deallocate db_name_cursor

    select * from DB_Growth with (nolock)

    if object_id('DB_Growth') is not null

    drop table DB_Growth

    set nocount off

    set ansi_warnings on

    return

    go

     

  • Thanks Steve. When I wrote the script, my concern was only to get data regarding the conventionally named databases. The idea of handling databases with special characters in their names did not occur to me. Thanks for pointing out the same and enhancing it. --Best Regards, Suresh.

     


    Kindest Regards,

    M Suresh Kumar

  • A reader, Tracey wanted to know what thesize of the dta file would be after an increment. Just to put it here, it would be the current file size plus the increment (hich could be in MB or % of the current data file size). The future size of her database file after the next increment would be the current size + X MB or 10% of current size of the data file.

    A second question was whether 8192 is a constant. The answer is it is as it is the size of a page. 8192.0 is a better option for calculations.

          The script gives the growth factor as currently set. It could be in MB or % as the DBA might have set. The script handles both the scenarios.

      I have modified the script slightly to replace 8192 with 8192.0 and as per your suggestion to handle special characters like -. The same is here:

     

    /*********************************************************************************************

    Script Name: Sysfiles_Growth.txt

    Purpose    : Displaying Auto-Growth factors of database files based on SYSFILES.

    Created On : October 31, 2005

    Author     : Suresh Kumar Maganti

    *********************************************************************************************/

    set ansi_warnings off

    declare @l_db_name       varchar(50)

           ,@l_sql_string    varchar(1000)

    set nocount on

    if object_id('DB_Growth') is not null

    drop table DB_Growth

    create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))

     

    declare db_name_cursor insensitive cursor

    for

    select name from master..sysdatabases

    open db_name_cursor

    fetch next from db_name_cursor into

                             @l_db_name

    While (@@fetch_status = 0)

    begin

       select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)

                      + 'else  convert (varchar, growth) + '' Percent''' + char(10)+char(13)

                + 'end' + char(10)+char(13)

                             + 'from [' + @l_db_name + '].dbo.sysfiles'

     

       insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)  

       exec (@l_sql_string)

       fetch next from db_name_cursor into

                                @l_db_name

    end

    close db_name_cursor

    deallocate db_name_cursor

    select * from DB_Growth with (nolock)

    if object_id('DB_Growth') is not null

    drop table DB_Growth

    set nocount off

    set ansi_warnings on

    return

    go

    ---Thanks and Best Regards,

         Suresh


    Kindest Regards,

    M Suresh Kumar

  • I would like to know . How Can I get total growth of my Database.

    like what % is growing. Or if I like to see for one year, month by month

    Like January   DB   growth 20%

    Fefruary     DB    Growth 16%

    Please help.

    Faiz Farazi

    Bangladesh.

     www.databasetimes.net

    Thanks & Regards
    Faiz Farazi
    MCDBA,MCSA

  • Suresh, even though 3 yrs late, I want to say thanks for posting this!!!

  • Hi Mike, you are welcome. Some more scripts are coming up shortly. Thanks for the encouragement. ---Best Regards, Suresh.


    Kindest Regards,

    M Suresh Kumar

Viewing 10 posts - 1 through 10 (of 10 total)

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