add growth to database size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

add growth to database size

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-05-04 : 15:12:43

Hi i have the database size and growth but how do i dertermine if it is a % or mg
select * from master.dbo.sysaltfiles
I want to add the growth to my database size and see what amount of space i could possible use up.

I have the details for database size and the growth in a table already.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-05-04 : 17:36:42

If the growth value is greater than 100, then it's growth by MB and not percentage. In this case, the growth value divided by 128 gives you the MB size (ex: if the growth value is 64000, it's actually 500MB).

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-05-06 : 07:53:55

If i run this command
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),grow th)* 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

Is it true to say that if i add the mg or * by percentage to my database size
which i can get from one of the other system commands for the log and data
i can truely say that if there was no used space that this would be the amount of hard space i need...hard space actual disk space

Is it always the 8192

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources