size

Horse06

Our database used size now has reached 53% and I optimize from time and time. The manual mentions it will not exceed 80%. otherwise it will be expanded. But I looked at the file menu and cannot find the expand. When I created a database in my client computer, I can see it. Any advice? Thanks!

Savatage

sql has an auto expand but I have read that it is not wise to have it automatically expand because it could happen when you least want it to, during the middle of the day when people are working.

manual mentions it will not exceed 80%

Will not or Should not - very different

File->Database->Expand

DenSter

I've heard rumors that size doesn't matter, but I suspect that's not true

Neither the native database server nor SQL Server has a mechanism that automatically expands the database when used percentage hits 80%. On native it is recommended to have at least 15-20% free space, and the only way to expand the database is manually. On SQL Server it's the file's properties that determines whether the file automatically expandes, and by how much the file expands. It doesn't do that though until the file is full. So at some point the file is 99.9999999% full, a user posts another transaction, file is full, SQL Server will automatically expand the file by the percentage setup in the file properties.

You will want to set the file to big enough not to automatically expand too often, and keep an eye out and manually expand the file during off hours. You should keep the auto-grow setting on though, so that the database doesn't shut down when it hits that spot.

bbrown

The rule I use is to maintain the database between 60% and 80% full. When the database reaches 80 expand down to 60% full. Do not rely on auto-grow except for a safety net. Auto-grow occurs a 100% full. Not at 90%, 95%, 99%, or 99.99%. This system does NOTHING else while it expands the files. This applies to both data and log files.

This is for SQL. But I apply the space rules to native also

Ian_Piddington10199

I have also found that with SQL its safer to tell it to expand by a set size than by a percentage.

bbrown

Ian_Piddington wrote:

I have also found that with SQL its safer to tell it to expand by a set size than by a percentage.

Bottom line is - Do not rely on Auto-Grow.

Percent or size the issue is still the same.

DenSter

bbrown wrote:

The rule I use is to maintain the database between 60% and 80% full. When the database reaches 80 expand down to 60% full.

That's a good rule, and the lesson learned there is that you go in and check the file size periodically to make sure the files are still good. There might even be a way to have SQL Server send you an email when it reaches 80%.

Horse06

Thanks for everone! One year ago, I read the post by Savatage, but for some reason I just read a few lines. Could anyone send me a link.

BTW, I cannot find expand from database of the file menu. I am wondering if we don't have licence or some other reasons. Thanks a lot!

bbrown

Horse06 wrote:

Thanks for everone! One year ago, I read the post by Savatage, but for some reason I just read a few lines. Could anyone send me a link.

BTW, I cannot find expand from database of the file menu. I am wondering if we don't have licence or some other reasons. Thanks a lot!

For SQL (Database - File - Alter). Just change the file size and click OK

David_Singleton

bbrown wrote:

...

For SQL (Database - File - Alter). Just change the file size and click OK

Interesting :-k I would always do this from SSMS. Any reason for doing it from NAV?

bbrown

David Singleton wrote:

bbrown wrote:

...

For SQL (Database - File - Alter). Just change the file size and click OK

Interesting :-k I would always do this from SSMS. Any reason for doing it from NAV?

Either way the result is the same. I tend to use SSMS a lot myself. However, many clients I deal with have little or no in-house IT capacity. When working with these customers to handle task like database expansion it can be easier to keep them in the NAV client (a familiar place) rather then exposing them to the complexity of SSMS.