Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Arriving at Database Growth Factor in SQL Server 2000 Expand / Collapse
Author
Message
Posted Monday, November 07, 2005 4:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:51 AM
Points: 141, Visits: 113
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sMaganti/arrivingatdatabasegrowthfactorinsqlserver2000.asp


Kindest Regards,

M Suresh Kumar

Post #235597
Posted Tuesday, December 06, 2005 3:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 08, 2013 8:00 AM
Points: 6, Visits: 17
On our system, the script throws an exception 'integer overflow'. Change the formula : All
'size * 8192'
should read
'size * 8192.0'

Post #242178
Posted Tuesday, December 06, 2005 8:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:37 AM
Points: 80, Visits: 135

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
   AS A
 ORDER BY DB_NAME(DBId),FileId,GroupId

END

Just do a filter on the DBs you are looking for

 

Regards

Ram Ramamoorthy

 


 




Post #242265
Posted Tuesday, December 06, 2005 12:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 23, 2006 12:25 PM
Points: 4, Visits: 1
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 www.quest.com/beta and click on Capacity Management for SQL Server 1.0
Regards,
Hassan Fahimi

Post #242348
Posted Thursday, December 08, 2005 3:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 16, 2012 2:10 PM
Points: 21, Visits: 222

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


 

Post #243036
Posted Thursday, December 15, 2005 3:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:51 AM
Points: 141, Visits: 113

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

Post #244675
Posted Wednesday, May 10, 2006 12:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:51 AM
Points: 141, Visits: 113

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

Post #279038
Posted Thursday, April 12, 2007 7:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 3:43 PM
Points: 6, Visits: 314

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
Post #357808
Posted Saturday, June 21, 2008 2:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:31 AM
Points: 275, Visits: 739
Suresh, even though 3 yrs late, I want to say thanks for posting this!!!



Post #521315
Posted Friday, May 29, 2009 3:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:51 AM
Points: 141, Visits: 113
Hi Mike, you are welcome. Some more scripts are coming up shortly. Thanks for the encouragement. ---Best Regards, Suresh.


Kindest Regards,

M Suresh Kumar

Post #726086
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse