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
-
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
 
AS AORDER 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.
-
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
Thanks & Regards
Faiz Farazi
MCDBA,MCSA -
Suresh, even though 3 yrs late, I want to say thanks for posting this!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply