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

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

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.

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

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