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