MS SQL2008 R2 Database of NAV is increasing

sakameeran

Hi Experts,
In my organization we are Using MS NAV 2009 R2 with MS SQL2008 R2 Database.
MS SQL2008 R2 Database of NAV is become very huge and increasing like anything. Ex: 10 GB per month.
Here the mdf, ndf and ldf file sizes:
mdf --> 435MB
ndf --> 153GB
ldf --> 6.17GB

I have shrinked the database then,by below shrink query and the ldf file came to under 50MB. but still the ndf file size is not shrinked.

ALTER DATABASE [Database]
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE ([Database_Log], 1);
GO

ALTER DATABASE [Databse]
SET RECOVERY FULL;
GO

Could you please help me out on controlling this database size growing?

Expecting your favorable reply.

Regards
S.A.K.Ahamed Meeran

bbrown

Why are you worried about the growth?

Running Shrinkfile is the worse thing you can be doing.

sakameeran

Hi Brown,
Thanks for your immediate response.
Actually, My Whole database size is arround 160GB. We have gone live on one year back only.
For one year 160GB means, it is very huge. Specially while taking daily autobackup.
Is there any way to solve or we can control the size by doing some thing.
Please note that MDF size only 430MB only.

Thanks in Advance

sakameeran

Hi Experts,
My Concern is, why the Database ndf file is more than 150GB in size?
Is there any setup related things gone, while implementing the NAV?

Thanks for your answers in Advance

bbrown

First, there is nothing wrong with an ndf file of 150 GB or even larger. There are many NAV systems out there like that. The question you want to ask, and answer for your system, is whether that growth and size is legimate. If it is, then you should provide adequate systems to support it.

Where is the growth occurring? WHat are your 10 largest tables in terms of record counts? In terms of size?

You seem to have a concern with backup times. How long does it take you to do a full database backup? What are you backing up to? Do you also to transaction log backups?

Can you descibe your hardware?

Cem_Karaer

Hello,

Unnecessary dimensions, partial shipments/receipts of large orders, unnecessary analysis reports ect. may create huge volume increase. If it is certain that every and each setup is necessary, then you may try SQL partitioning of ndf files over disks.

Regards.

bbrown

cemkaraer wrote:

Hello,

Unnecessary dimensions, partial shipments/receipts of large orders, unnecessary analysis reports ect. may create huge volume increase. If it is certain that every and each setup is necessary, then you may try SQL partitioning of ndf files over disks.

Regards.

Why even be concerned with a 150 GB database? What issues is it causing or perceived to cause? We support many databases of 150 GB or even larger. Of course, if the growth is not legit then it should be addressed.

Cem_Karaer

I think 150 GB per year is something to be concerned.

bbrown

cemkaraer wrote:

I think 150 GB per year is something to be concerned.

Why? You can't make a generic statement like that. You have to first determine where the growth is from and if it is legit. Yes, there are cases where it would not be legit. For example, a database that is too heavily indexed.

But I would not say this growth is a problem, without more information.

davmac1

Have you looked to see which tables are growing?
In addition to the tools SQL Server supplies, NAV provides a very nice view of the record count in each table.
You just need to do a little analysis.

sakameeran

Thanks all. Specially Item Ledger Entry, Value Entry, GL Entry and one customized table.

Is that advisable to optimize the tables? Coz, I have created one test database (Replica of Live) and optimized Value Entry table, then table size came from 13GB to 9GB (Found thru database information-->Tables in NAV).

Is that advisable to optimize all tables?

Thanks

Cem_Karaer

In fact, optimizing tables means to reindexing them. Reindexing has positive and negative consequences. Positive effect is decrease in size but negative one is performance issues. In fact NAV can easily handle hundreds of GB as long as these volumes do not impose any performance degradation. Sooner or later you will need some hardware upgrade. Hardware upgrade for performance is much more costly than that for volume handling.

Regards.

mdPartnerNL

Just checked with a starting customer after 5 month (5000 invoice p/month):
mdf size = 900MB
ldf size = 9000MB

So this seams normal!?

Cem_Karaer

Well, if your recovery model is full, then you must take a log-backup with TRUNCATE ON. But I think, the full recovery model is unnecessary in most cases. If you set the recovery model to simple and make a log-file shrink at SQL-server side, you will get rid off the log file.

Regards.

bbrown

mdPartnerNL wrote:

Just checked with a starting customer after 5 month (5000 invoice p/month):
mdf size = 900MB
ldf size = 9000MB

So this seams normal!?

How big is your ndf? That's where all the growth is.

No, 150 GB per year may not be typical of many NAV sites. But it's not abnormal either.

bbrown

cemkaraer wrote:

Well, if your recovery model is full, then you must take a log-backup with TRUNCATE ON. But I think, the full recovery model is unnecessary in most cases. If you set the recovery model to simple and make a log-file shrink at SQL-server side, you will get rid off the log file.

Regards.

I would disagree with this in most situations.

1. With full recovery, you just need to do a normal log backup. Not special switches are needed.

2. I would say that FULL recovery is the more common settign for NAV databases. If you use SIMPLE recovery, then your restore points are only at the times of your backups. FULL recovery gives you must broader recovery points. Now, some sites, may be OK with the few recovery points provided by SIMPLE. A site by site decisions.

3. NEVER do a shrink on a regular basis.

4. Why would you want to "get rid of the log file"? By the way it's required.

Cem_Karaer

bbrown wrote:

3. NEVER do a shrink on a regular basis.

Why?

bbrown wrote:

4. Why would you want to "get rid of the log file"? By the way it's required.

Why do I need 9 GB of transaction log file?

Regards.

bbrown

3. Constantly shrinking the log file (or datafile) and the system constantly auto-grow it, will fragment the file across the disk hurting performance.

4. The size of the transacation log depends on your system. With full recovery, it need to be large enough to hold all transaction activity between backups. WIth SIMPLE it needs to be large enough to hold the largest set of active transactions. SIMPLE recovery does not "turn off" the log file. As is commonly misunderstood.

Marije_Brummel

I guess this post is high jacked.

To the original poster (sakameeran) my advice would be to first study NAV a bit more. The answers you are looking for are very easy to find on this forum and other NAV community places or MSDN.

To cemkaraer I would like to advice to catch up on some best practices on installing and maintaining SQL databases in general.

Sorry for the interruption.

sakameeran

Thank you All.

Cem_Karaer

bbrown wrote:

3. Constantly shrinking the log file (or datafile) and the system constantly auto-grow it, will fragment the file across the disk hurting performance.

4. The size of the transacation log depends on your system. With full recovery, it need to be large enough to hold all transaction activity between backups. WIth SIMPLE it needs to be large enough to hold the largest set of active transactions. SIMPLE recovery does not "turn off" the log file. As is commonly misunderstood.

Thank you for sharing.

Regards.

mdPartnerNL

mdPartnerNL wrote:

Just checked with a starting customer after 5 month (5000 invoice p/month):
mdf size = 900MB
ldf size = 9000MB

So this seams normal!?

I have no ndf file. Don't need it.

I think the ldf file size is this large (relative to the mdf) because we started with FULL en changed it later on to SIMPLE.

Cem_Karaer

If your preference is SIMPLE recovery mode, you should take log backup or log file shrink. Because the log file will avail no more.

Regards.

mdPartnerNL

cemkaraer wrote:

If your preference is SIMPLE recovery mode, you should take log backup or log file shrink. Because the log file will avail no more.

Regards.

Im not sure if "truncate log" is available/needed with SIMPLE. For cleaning it up once, ok, but (after shrinking) then at what size must I set it? I think a restore of a NAV backup will take up most of the log size, but how much?

Maybe it's easier to set it to FULL..but in our setup we must synchronize with other types of database and restore to a certain time is not needed. Im open to suggestions ofcourse. We can always learn.

Cem_Karaer

You don't need to set it to any size, it is defaulted to 102.400 kB. Its size will increase as NAV is up and running. With SIMPLE recovery mode, after the last full backup, the log file's size will be decreased to minimum again.

Regards.

bbrown

cemkaraer wrote:

You don't need to set it to any size, it is defaulted to 102.400 kB. Its size will increase as NAV is up and running. With SIMPLE recovery mode, after the last full backup, the log file's size will be decreased to minimum again.

Regards.

Please scroll up a few post and read Mark's suggestion.

Regardless of your recovery model, you do not want your transaction log to auto-grow. You also do not want to be shrinking it below the size that it needs to be. What that size is wil vary with how the system is used. But you would never want to keep shrinking it to the minimum size.

Cem_Karaer

Well, I may be wrong and I don't want to high-jack the topic more but as MSDN says:

Typically, truncation occurs automatically under the simple recovery model when database is backed up and under the full recovery model when the transaction log is backed up.

http://msdn.microsoft.com/en-us/library/ms178037(v=sql.105).aspx
And also

Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files.

Do I miss something?

Regards.

bbrown

MSDN says:

Typically, truncation occurs automatically under the simple recovery model when database is backed up...

That is actually not accurate. In older SQL versions the setting "Simple Recovery" use to be called "Truncate on CheckPoint". Which is a more accurate description of what it does. Checkpointing is the process of SQL writing database changes from memory to the disk files. In "Simple Recovery", this also truncates the transaction log. Truncation removes the inactive portions of the log. It does not reduce the physical size of the transaction log file.

In terms of shrinking the log, just because you can doesn't mean you should. That quote from MSDN is a good description of what shrink does. But it's still a bad practice to use it on a regular basis.

Cem_Karaer

bbrown wrote:

MSDN says:

Typically, truncation occurs automatically under the simple recovery model when database is backed up...

That is actually not accurate. In older SQL versions the setting "Simple Recovery" use to be called "Truncate on CheckPoint". Which is a more accurate description of what it does. Checkpointing is the process of SQL writing database changes from memory to the disk files. In "Simple Recovery", this also truncates the transaction log. Truncation removes the inactive portions of the log. It does not reduce the physical size of the transaction log file.

In terms of shrinking the log, just because you can doesn't mean you should. That quote from MSDN is a good description of what shrink does. But it's still a bad practice to use it on a regular basis.

Okay got the point. Thank you very much.

Regards.