Database Growth

dabel

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

David_Singleton

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

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...

David_Singleton

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

OK, what is your recommeded technique for extrapolating?

Thanks

David_Singleton

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.

Alex_Chow

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

They have to eventually move out of SQL express anyway.

The easiest way is to ask them to monitor the database periodically. When the size gets to 8-9 GB, ask the client to buy SQL Standard.

bbrown

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?

Alex_Chow

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.

dabel

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.

dabel

Agreed Alex!

bbrown

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.

bbrown

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.

kriki

I am using this script I found:

-- http://www.sqlservercentral.com/scripts/Backup/62497/
-- track DB size growth over time

declare @DBname as varchar(50)

set @dbname = 'your NAV DB'

select 
 BackupDate = convert(varchar(10),backup_start_date, 111) 
 ,SizeInGigs=backup_size/(1024 * 1024 * 1024)
 ,SizeInMegs=floor(backup_size/(1024 * 1024))
 ,CAST (DATEDIFF(second,backup_start_date , backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken
from msdb.dbo.backupset 
where 
 database_name = @dbname
 and type = 'd'
order by backup_start_date desc

If you keep your backup history for the last 4 months (or 1 year), you can use it to extrapolate the growth of your DB.

[Topic moved from 'NAV Three Tier' forum to 'NAV/Navision Classic Client' forum]

Alex_Chow

kriki wrote:

If you keep your backup history for the last 4 months (or 1 year), you can use it to extrapolate the growth of your DB.

But the information may not be accurate since they did an upgrade to 5.0 sp1 that reduced the DB size.

kriki

Alex Chow wrote:

kriki wrote:

If you keep your backup history for the last 4 months (or 1 year), you can use it to extrapolate the growth of your DB.

But the information may not be accurate since they did an upgrade to 5.0 sp1 that reduced the DB size.

True, you need it on the same DB and with the same maintenance. (I lately had A DB that went from 13GB to 9GB just be putting an regular indexrebuild on it).

Alex_Chow

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.

bbrown

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.

DenSter

Alex Chow wrote:

There's the problem.

Really? You work for free much?

stryk

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

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.

bbrown

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

David_Singleton

Companies buy Navision with ONE and only one objective in mind. To make more money. Even a Not For Profit. Like all companies they have bills to pay and directors to report to.

The ONLY reason that we sell Navision and work with it is to make money. Even if we are doing free work for a client we do that only so as to make money down the road.

It always seems odd to me that companies use Navision to make money, yet begrudge their partners from making money. It is truly bizarre. And having been in the industry a long time, one thing I see over and over, is that the majority of Navision customers are make much higher profits than their partner is. That is why this industry is dying.

Alex_Chow

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.

Alex_Chow

David Singleton wrote:

Companies buy Navision with ONE and only one objective in mind. To make more money. Even a Not For Profit. Like all companies they have bills to pay and directors to report to.

The ONLY reason that we sell Navision and work with it is to make money. Even if we are doing free work for a client we do that only so as to make money down the road.

It always seems odd to me that companies use Navision to make money, yet begrudge their partners from making money. It is truly bizarre. And having been in the industry a long time, one thing I see over and over, is that the majority of Navision customers are make much higher profits than their partner is. That is why this industry is dying.

Yes, in essence every company has to make money. It's how businesses stay in business. The key point is how the company makes it's money.

If a company's way to make money is through lies, cheat, steal, false PR, etc, it would be a very bad place to work in whether you're a consultant or an employee. That's why you see executives leave behind fat paychecks and resign.

And you probably seen this more than myself. Having been to hundreds of companies, walking into a company that does quite well, the CEO/Owners all share some of the some common attributes. It's balantly obvious as an observer. And the companies that don't do quite as well, it's owners same a lot of common attributes as well. I know you know what I'm talking about David.

The company will try to negotiate a better rate from you and expect you to give them honest solutions when asked. Our job is to stay firm on our pricing to maximize profit and give honest solutions when being asked. No one likes to be profiled, if we give them our word that we will support them and take them on as customers, we need to be honest follow through.