SQL 2000 database maintenance

ghane

Hi,

We have a mix of Nav 3.x and 4.0 databases on MS SQL. Some of these are a few years old, and in active use. Everything seems OK.

Although performance is not an issue, I have noticed database sizes seem to be growing, at rates which do not seem reasonable, given the data that is changing. For example, one database is growing by over 1GB per day, for less than a thousand sales orders.

For some databases, I have tried a backup-delete database-recreate-restore, and find 20GB databases fitting in 5GB.

Why would this be so? In each case, the sizes I am measuring is after shrinking the datafiles.

Is there a simpler way to clean out an SQL database, short of backup/restore?

Thanks,

(I have ordered Jörg Stryk's book today from Amazon.de, hopefully will tell me all I want to know)

Slawek_Guzek

Check whether datafiles are growing or log files.

My guess is log files. Log file grows when you are inserting records, deleting many records, etc, basically grows from any data modification, and it just explode if you reindex some big table with many indexes on it.

Check if your maintenance plan contains backup of log files (backup of log files 'shrinks' the log file by allowing SQL engine to resue the same part of physical file - so physical file doesn't grow) , and if it contains any reindexing of any table.

And btw, never shrink data files (unless you abslolutely need to recover some free space on disk) - it destroys the performance not only because SQL has to increase file size during the work, but also dramatially fragments your data and indexes.

Regards,
Slawek

ghane

Slawek,

Thanks for the info.

Slawek Guzek wrote:

Check whether datafiles are growing or log files.

My guess is log files. Log file grows when you are inserting records, deleting many records, etc, basically grows from any data modification, and it just explode if you reindex some big table with many indexes on it.

Datafiles are growing. We have logging set to "Simple", and they do not grow over 100MB. Once in a year or so, I manually shrink them.

The database has grown by 20GB in the last year, and 1GB every day in the last fortnight.

Slawek Guzek wrote:

Check if your maintenance plan contains backup of log files (backup of log files 'shrinks' the log file by allowing SQL engine to resue the same part of physical file - so physical file doesn't grow) , and if it contains any reindexing of any table.

We do not backup logfiles, just data.

The maintenance plan does not do any re-indexing, just consistency check and backup.

Thank you for the tips.

--
Regards

Slawek_Guzek

ghane wrote:

Datafiles are growing. We have logging set to "Simple", and they do not grow over 100MB. Once in a year or so, I manually shrink them.

OK. I've assumed that you have Full recovery model. With Simple all transactions are still written to transaction log, and still reindexing explode the log, but the log is reused immediately after transaction is commited. Anyway it seems that the problem is elsewhere.

Datafiles are growing. We have logging set to "Simple", and they do not grow over 100MB. Once in a year or so, I manually shrink them.

The database has grown by 20GB in the last year, and 1GB every day in the last fortnight.

There are few things to check which might be eventually responsible for such a strange behaviour.

1. Check if somebody turned on or reconfigured Change Log to log some hot tables or log all the changes to all or the most of the tables
2. Make sure that there is no other data loaded to any table - not necessarily NAV table - there might be some other manually created/added table to the same database
3. Try to find if there is some particular table responsible for data growth, or all updated tables grows proportionally
4. Check if somebody changed default Index Fill Factor to some very low value but bigger than 0, either on the database level or on individual indexes on suspected tables, if any

Regards,
Slawek

stryk

Well, if you're experiencing an "unnatural" growth of the database, the it also could be related to those issues:

- too high "Cost per Record" (the relation between write operation in NAV and write operations in SQL). E.g. you insert one record in NAV, but 20 Indexes and 40 SIFT buckets are updated, you have CPR aof 1 to 60.

You should reduce these CPR by primarily reducing the number of SIFT buckets (e.g. see http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx or the book you have ordered 8) ) and by "streamlining" the indexes (e.g. copying from "Key" to "SQL Index" - see http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2008/04/21/sqlindex-property.aspx, especially my comment)

- wrong Index-Fillfactors. Do you fiddle with FF? How? With the appropriate tools you could adjust the FF optimally. Without those tool, you could set the FF to a value of 90 or 95 percent (Caution: In Maintenance Plan you do not assign the Fillfactor like 95, you have to specify the amount of free space like 5%! Extra Caution: there is a :bug: in SQL 2005 9.0.1399 (RTM) - Here "Free Space" of 5% results in a FF of 5% (should be 95) )

Hope this helps you a little.

Slawek_Guzek

stryk wrote:

Well, if you're experiencing an "unnatural" growth of the database, the it also could be related to those issues:

- too high "Cost per Record" (the relation between write operation in NAV and write operations in SQL). E.g. you insert one record in NAV, but 20 Indexes and 40 SIFT buckets are updated, you have CPR aof 1 to 60.

That is potentially good reason, but IMHO not when the database has suddenly started to grow enormously. 1GB increase per 1000 SO IMHO is not possible just by adding a few SIFTs - this has to be be some 'global' change.

Regards,
Slawek

ghane

Slawek Guzek wrote:

That is potentially good reason, but IMHO not when the database has suddenly started to grow enormously. 1GB increase per 1000 SO IMHO is not possible just by adding a few SIFTs - this has to be be some 'global' change.

Exactly. And the Navision coders/admins keep telling me that business is increasing. This is true, but not at this rate.

BTW, the numbers I gave (1GB/day and about 1000 SO) are quite accurate. Database is 63GB today, was 61GB when I ordered Jörg's book :-) . And yesterday was a Saturday, so I doubt the 1000 SOs.

No one has complained about performance yet.

--
Sanjeev

ara3n

go to

File->database->information->table.

filter on size of table and paste the records in here.

ghane

ara3n wrote:

go to

File->database->information->table.

filter on size of table and paste the records in here.

I have pasted the top 20 tables by size. Company names have been changed, but are consistent. There are 8 companies in the database.

I can upload the Excel sheet to a URL, if it will help.

Company_A1	60082	Posted Freight Rate	240253	2,248	527408
Company_A1	380	Detailed Vendor Ledg. Entry	348559	1,644	559520
Company_C3	355	Ledger Entry Dimension	98586	6,093	586608
Company_A1	46	Item Register	566030	1,171	647392
Company_A1	13755	E.R.-1	368997	1,914	689528
Company_A1	21	Cust. Ledger Entry	464634	1,631	739888
Company_A1	123	Purch. Inv. Line	231723	3,538	800728
Company_A1	111	Sales Shipment Line	375066	2,689	984944
Company_A1	113	Sales Invoice Line	486210	2,230	1058688
Company_A1	339	Item Application Entry	1087064	1,004	1065408
Company_A1	45	G/L Register	1081721	1,044	1102568
Company_A1	5700	Stockkeeping Unit	2586656	625	1579032
Company_B2.	5700	Stockkeeping Unit	3084309	524	1579664
Company_A1	379	Detailed Cust. Ledg. Entry	1076813	1,834	1928376
Company_A1	359	Posted Document Dimension	6923857	434	2935096
Company_A1	32	Item Ledger Entry	966976	3,785	3574592
Company_A1	5802	Value Entry	1475484	3,907	5629184
Company_A1	17	G/L Entry	5421318	1,185	6272496
Company_A1	355	Ledger Entry Dimension	29010786	346	9796248
Company_A1	60014	Item Standard Cost History	52955308	327	16907968

Folks, thank you for the time and effort you are putting in. It is deeply appreciated.

--
Sanjeev

kine

As you can see, hot tables are

60014 - customization
355 - how many dimensions you are using? Each dim is space killer...
359 - same as 355
5700 - are you really using all these Stockeeping units or just someone generated it for all items and all locations?

Other numbers looks in normal. But Check that you have good DB maintenance and you have job for deleteing zero SIFT records. This will save some space too... :-)

Slawek_Guzek

Compare this:

Company_C3 355 Ledger Entry Dimension 98586 6,093 586608

with this:

Company_A1 355 Ledger Entry Dimension 29010786 346 9796248

Record length of 6,093 is just insane, especially that dimensions records are usual very short.

Do optimize (from NAV) the table Ledger Entry Dimension in Company_C3, and checks if anything improves. In addition go to SQL table [Company_C3$ Ledger Entry Dimension] and double check for any additional indexes, settings of index fill factor etc.

Regards,
Slawek

DenSter

I'm putting money on that someone set an inverse fill factor. The default value is 0, and it is easy to misunderstand that this actually means exactly the same as 100. I would not be surprised if someone set that at 10 or something, but that they meant to have 10% room in the index pages.

Especially with a severely overindexed database, this will have dramatic effects.

ghane

ghane wrote:

I have ordered Jörg Stryk's book today from Amazon.de, hopefully will tell me all I want to know

8:30pm, and Amazon delivered. Now to read it.