Database Growth

dabel wrote:

Does anyone have any techniques or utilities that would help predict the future growth of an existing Db moving from 4.x to NAV 2009? Client is currently at 9.5 GB, that drops to about 5.9 GB after restoring to SQL and taking advantage of the SQL improvements offered under 5.0 SP1 with indexing. However, they want to know how long they might be able to stay with SQL Server Express 2008, which has a limit of 10GB.

Thanks

how much new data do they post per month?

dabel wrote:

How would that information need to be presented, in total or by table and by what measurement, KB's, records, etc.?

After I get this information, i.e. G/L Entry - 10,000 new records, Vendor Ledger - 2,000 new records, etc. how would I extrplolate that into a 5 year growth plan?

Thanks for replying...

Just copy the data into Excel. Go to File->Database->Information->Tables there you have all the sizes of the tables so its pretty easy (though tedious and time consuming) to extrapolate it.

dabel wrote:

OK, what is your recommeded technique for extrapolating?

Thanks

That all depends ont he data and how the client works what they do etc. You need to sit with them and work it out. There is no way to really estimate this without actually working with the customer.

How much time are you spending so this "client" can avoid purchasing a real SQL Server license? Isn't your time worth something? If a "client" is trying to avoid the small expense of a license, how much of a revenue stream do you see them being for your business?

dabel wrote:

Appreciate the concern. But we deal exclusively with NFP organizations who need to be very prudent with their funds in this economy. They realize they will need to go to full blown SQL eventually, but they are currently a non-sql shop being forced to move to SQL because of RTC and want to plan for when they need to actually spend the funds.

I've dealt with many a NFP over my career. One thing I have learn is not to equate "Not for Profit" with "We have no money". Think "Harvard University (Boston)". That's an NFP with a very big bank account.

Alex Chow wrote:

bbrown wrote:

Alex Chow wrote:

Probably off topic, but I think you should help the client regardless of potential revenue to you.

In the end, there are easier ways to determine what's needed than creating unnecessary tasks and calculations.

Speaking for myself here, but I don't work for a charity. My employer provides me with a sizable paycheck. They expect me to justify that salary by generating revenue for the company.

There's the problem.

Not looking to start an argument here, but I take exception to your comment.

I have been in this business for 25 years. This is not my hobby. It is how I earn a living. Giving away services may make some people feel all warm and fuzzy, but it doesn't pay the bills. My clients have a lot more money that I do, so I feel no obligation to give them free service. Instead I provide good honest service at a fair price. There, of course, may be exceptions but these are typically because there is strong potential for downstream revenue. Personally I don't consider someone trying to avoid an ~$800 license to fall into this category. But we all need to make our own business decisions. Just as our clients need to do with their customers.

Hi there,

well, I try to get this discussion back to the original problem: there are PLENTY of reasons to measure exactly the database size and growth!
Not just regarding the SQL eEdition to use, but also regarding storage, data clen-up, archiving, etc..

Here a solution (light version) taken from my "NAV/SQL Perfromance Toolbox":

First I create a table to save the measurement; it actually has two "measurement points" A and B, or "before" and "after", then some columns to display the absolut and relative growth:

if not exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[ssi_TableGrowth]') and objectproperty(id, N'IsTable') = 1)
create table [dbo].[ssi_TableGrowth]
 (
  [name] nvarchar(128) constraint [ssi_TableGrowth$pk_ci] primary key clustered,
  [date_A] varchar(20),
  [rows_A] bigint,
  [reserved_A] bigint,
  [data_A] bigint,
  [index_size_A] bigint,
  [unused_A] bigint,
  [date_B] varchar(20),
  [rows_B] bigint,
  [reserved_B] bigint,
  [data_B] bigint,
  [index_size_B] bigint,
  [unused_B] bigint,
  [growth_rows] bigint,
  [growth_rows_perc] decimal,
  [growth_data] bigint,
  [growth_data_perc] decimal,
  [growth_index] bigint,
  [growth_index_perc] decimal
 )
go

Then I have a little stored procedure (sorry, it's somewhat old and the code could be nicer - I know that!), which performs all the calculation:

if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[ssi_calcgrowth]') and objectproperty(id, N'IsProcedure') = 1)
drop procedure [dbo].[ssi_calcgrowth]
go
create procedure ssi_calcgrowth
with encryption
as

print '*********************************************************'
print '***              STRYK System Improvement             ***'
print '***    Performance Optimization & Troubleshooting     ***'
print '***  (c) 2007, STRYK System Improvement, Jörg Stryk   ***'
print '***                   www.stryk.info                  ***'
print '*********************************************************'
print '              Version 2.04, Date: 27.11.2008             '
print ''
print 'Analyzing data and index sizes, calculating growth:'
print ''

set statistics io off
set nocount on

-- Clean Up
begin transaction
delete from ssi_TableGrowth from ssi_TableGrowth tg where not exists(select * from sysobjects where [xtype] in ('U', 'V') and [name] = tg.[name])
commit transaction

-- Move latest snapshot (B) as first one (A)
print 'Moving old data ...'
declare @name nvarchar(128),
        @date_B varchar(20),
        @rows_A bigint,
        @reserved_A bigint,
        @data_A bigint,
        @index_size_A bigint,
        @unused_A bigint,
        @rows_B bigint,
        @reserved_B bigint,
        @data_B bigint,
        @index_size_B bigint,
        @unused_B bigint,
        @statement varchar(1000)
declare tab_cur cursor for select name,date_B,rows_B,reserved_B,data_B,index_size_B,unused_B 
from ssi_TableGrowth
open tab_cur
fetch next from tab_cur into @name,@date_B,@rows_B,@reserved_B,@data_B,@index_size_B,@unused_B 
while @@fetch_status = 0 begin
  set @statement = 
    'UPDATE dbo.[ssi_TableGrowth]
     SET date_A = ''' + @date_B + ''',
         rows_A = ''' + convert(varchar(18), @rows_B) + ''',
         reserved_A = ''' + convert(varchar(18), @reserved_B) + ''',
         data_A = ''' + convert(varchar(18), @data_B) + ''',
         index_size_A = ''' + convert(varchar(18), @index_size_B) + ''',
         unused_A  = ''' + convert(varchar(18), @unused_B) + ''',
         date_B = null,
         rows_B = null,
         reserved_B = null,
         data_B = null,
         index_size_B = null,
         unused_B = null 
     WHERE [name] = ''' + @name + ''''
  exec (@statement)
  fetch next from tab_cur into @name,@date_B,@rows_B,@reserved_B,@data_B,@index_size_B,@unused_B 
end
close tab_cur
deallocate tab_cur

-- Updating size information
--print 'Updating system size information ...'
--set @statement = 'DBCC UPDATEUSAGE(''' + db_name() + ''') WITH NO_INFOMSGS'
--exec (@statement)

-- Create table (temporary) for new size snapshot
create table [dbo].[#tab_size]
 (
  [name] nvarchar(128),
  [rows] char(11),
  [reserved] varchar(18),
  [data] varchar(18),
  [index_size] varchar(18),
  [unused] varchar(18)
 )

-- Load current size information into temporary table
print 'Making size snapshot ...'
declare obj_cur cursor for select name from sysobjects where (xtype in ('U', 'V')) order by name
open obj_cur
declare @obj_name sysname
fetch next from obj_cur into @obj_name
while @@fetch_status = 0 begin
  set @statement = 
    'INSERT INTO #tab_size(name,rows,reserved,data,index_size,unused)
     EXEC(''sp_spaceused '''''  + rtrim(@obj_name) + ''''''')'
  exec (@statement)
  fetch next from obj_cur into @obj_name
end
close obj_cur
deallocate obj_cur

-- Move temporary data into comparison table
print 'Saving new data ...'
declare  @rows char(11),
         @reserved varchar(18),
         @data varchar(18),
         @index_size varchar(18),
         @unused varchar(18)
set @date_B = convert(varchar(20), getdate())
declare tab_cur cursor for select name,rows,reserved,data,index_size,unused from #tab_size
open tab_cur
fetch next from tab_cur into @name,@rows,@reserved,@data,@index_size,@unused 
while @@fetch_status = 0 begin
  set @statement = 
    'IF EXISTS(SELECT [name] FROM dbo.[ssi_TableGrowth] WHERE [name] = ''' + @name + ''') BEGIN
       UPDATE dbo.[ssi_TableGrowth]
       SET date_B = ''' + @date_B + ''',
           rows_B = ' + convert(varchar(20), replace(@rows, ' KB', '')) + ',
           reserved_B = ' + convert(varchar(20), replace(@reserved, ' KB', '')) + ',
           data_B = ' + convert(varchar(20), replace(@data, ' KB', '')) + ',
           index_size_B = ' + convert(varchar(20), replace(@index_size, ' KB', '')) + ',
           unused_B  = ' + convert(varchar(20), replace(@unused, ' KB', '')) + '
       WHERE [name] = ''' + @name + '''
     END ELSE BEGIN
       INSERT INTO dbo.[ssi_TableGrowth]
       (name,date_A,rows_A,reserved_A,data_A,index_size_A,unused_A)
       VALUES (' +
         '''' + @name + ''',' +
         '''' + @date_B + ''',' +
         '' + convert(varchar(20), replace(@rows, ' KB', '')) + ',' +
         '' + convert(varchar(20), replace(@reserved, ' KB', '')) + ',' +
         '' + convert(varchar(20), replace(@data, ' KB', '')) + ',' +
         '' + convert(varchar(20), replace(@index_size, ' KB', '')) + ',' +
         '' + convert(varchar(20), replace(@unused, ' KB', '')) + '
       )
     END' 
  exec (@statement)  
  fetch next from tab_cur into @name,@rows,@reserved,@data,@index_size,@unused 
end
close tab_cur
deallocate tab_cur

-- Calculating growth
print 'Calculating data and index growth ...'
declare @growth_rows decimal,
        @growth_data decimal,
        @growth_index decimal,
        @growth_rows_perc decimal,
        @growth_data_perc decimal,
        @growth_index_perc decimal
declare tab_cur cursor for select name,rows_A,data_A,index_size_A,rows_B,data_B,index_size_B from ssi_TableGrowth
open tab_cur
fetch next from tab_cur into @name,@rows_A,@data_A,@index_size_A,@rows_B,@data_B,@index_size_B 
while @@fetch_status = 0 begin  
  set @data_A = replace(@data_A, ' KB', '')
  set @data_B = replace(@data_B, ' KB', '')
  set @index_size_A = replace(@index_size_A, ' KB', '')
  set @index_size_B = replace(@index_size_B, ' KB', '')
  
  set @growth_rows = convert(decimal, @rows_B) - convert(decimal, @rows_A)
  if (@rows_A > 0)
    set @growth_rows_perc = round((@growth_rows / @rows_A * 100),0)
  else
    set @growth_rows_perc = 0
  
  set @growth_data = convert(decimal, @data_B) - convert(decimal, @data_A)
  if (@data_A > 0)
    set @growth_data_perc = round((@growth_data / @data_A * 100),0)
  else
    set @growth_data_perc = 0
  
  set @growth_index = convert(decimal, @index_size_B) - convert(decimal, @index_size_A)
  if (@index_size_A > 0)
    set @growth_index_perc = round((@growth_index / @index_size_A * 100),0)
  else
    set @growth_index_perc = 0
  
  set @statement =
    'UPDATE dbo.[ssi_TableGrowth]
     SET growth_rows = ' + convert(varchar(20), @growth_rows) + ',
         growth_data = ' + convert(varchar(20), @growth_data) + ',
         growth_index = ' + convert(varchar(20), @growth_index) + ',
         growth_rows_perc = ' + convert(varchar(20), @growth_rows_perc) + ',
         growth_data_perc = ' + convert(varchar(20), @growth_data_perc) + ',
         growth_index_perc = ' + convert(varchar(20), @growth_index_perc) + '
     WHERE [name] = ''' + @name + ''''
  exec (@statement)  
  fetch next from tab_cur into @name,@rows_A,@data_A,@index_size_A,@rows_B,@data_B,@index_size_B 
end
close tab_cur
deallocate tab_cur

-- Cleanup
drop table [dbo].[#tab_size]

print 'Finished.'

Then I have a periodic job which simply executes this SP:
- with the very first measurement the "A" (before) data is inserted
- with the second measurment the "B" (after) data is measured and the differences (growth) is calculated
- from the third measurement on it works like this: "A" is deleted, old "B" is moved to "A", new "B" and growth is calculated

With all this you could determine the EXACT growth per table precisely on record and kilo-byte level. Once you know the growth in a representative period you could give a somewhat repliable forcast about future growth and size ...

As mentiond, this is a lighter version. Actually I use this to only measure the short-time growth within a week. Then I have additional jobs which save the data monthly for a long-term analysis ...

Hope this helps you a little.

PROVIDED AS IS. NO WARRANTY, NO GUARANTEE, NO SUPPORT. USE AT OWN RISK.

Regards,
Jörg

Alex Chow wrote:

DenSter wrote:

Alex Chow wrote:

There's the problem.

Really? You work for free much?

No, but I know I don't work for money.

That called volunteering.... (also charity) :-k

bbrown wrote:

That called volunteering.... (also charity) :-k

We're really hijacking the topic. Which is rude and for that, I apologize to the original poster.

Nonetheless, we're getting into the philosophical principles that each person lives by. Forums postings are probably not the best place to go into any depth. There are a ton of books out there, religious and non-religious, that goes into very detail. If we're fortunate to meet, if you're willing, I can recommend some books that I found very helpful. And you can share with me on why my ideals does not meet reality.

All I can say is that when I shifted my focus away from the money aspect, I had less financial trouble paying bills, employees, build savings, etc. It's strange, but it works for me.