Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Arriving at Database Growth Factor in SQL Server 2000


Arriving at Database Growth Factor in SQL Server 2000

Author
Message
Suresh Kumar Maganti
Suresh Kumar Maganti
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 125
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sMaganti/arrivingatdatabasegrowthfactorinsqlserver2000.asp


Kindest Regards,

M Suresh Kumar

Mark Sandy
Mark Sandy
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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'
cbarus
cbarus
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 160

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

 


 





Hassan Fahimi
Hassan Fahimi
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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


Steve Bergkamp
Steve Bergkamp
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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


 


Suresh Kumar Maganti
Suresh Kumar Maganti
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 125

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

Suresh Kumar Maganti
Suresh Kumar Maganti
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 125

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

Faiz-397159
Faiz-397159
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 315

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
Mike Good
Mike Good
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 1020
Suresh, even though 3 yrs late, I want to say thanks for posting this!!!



Suresh Kumar Maganti
Suresh Kumar Maganti
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 125
Hi Mike, you are welcome. Some more scripts are coming up shortly. Thanks for the encouragement. ---Best Regards, Suresh.


Kindest Regards,

M Suresh Kumar

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search