Database Size, History and Clearance

(0) Share

Report

Posted on by Community Member

On reviewing the system I have inherited I have found there is data in the system going back to 2002, AP alone has 360000 invoices per annum and because of the way the business works there is an equivalent number of AR invoices (add this to the 6500 line Chart of Accounts!)

In other systems I have never held that much history in my live file (75gb database size?) as it is mostly redundant. Creating a creditor TB takes around 15 minutes for a summary, a detail one I just don even try!

Should I remove the old data (after creating a pre July 2012 archive company)?

After removing the data will the database size drop or is their a utility to manage this?

Adrian Shiel

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Community Member on at

Like (0)

Report

Was wondering if anyone had any thoughts on this?

I have now removed 10 years worth of Sales/Purchasing/GL Distribution history from the system (we have a pre-archive history file now). I have gone through the recompile process but have not seen any reduction in the size of the database, still 75gb.

We will be removing the actual purchase/sales object next from the tables for the last 10 years and then archiving suppliers/customers/ledger codes etc to streamline.

Logic says the database size should reduce, am I missing some step? Possibly an SQL specific utility?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

You need to do some maintenance in SQLServer.

To shrink all data and log files for a specific database, execute the DBCC SHRINKDATABASE command. To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command.

Was this reply helpful? Yes No
Richard Wheeler 75,852 Moderator on at

Like (0)

Report

Deleting records will not decrease the size of the database file. You can shrink the log file but shrinking the database is not advisable and could lead to performance issues due to fragmentation. It is best to just leave it alone and let it fill back up.

Was this reply helpful? Yes No
Verified answer

L Vail 65,271 on at

Like (0)

Report

alfo68,

Another option is to purchase Professional Advantage's 'Company Data Archive' product. For the information that you don't want to delete, but would still like to archive can be managed with this product. While you have an archive company, you can't add data to it as the years go by. Using company data archive, you can. Also, there are lookups in certain modules that will let you combine information from you 'live' company and your archive company for viewing.

I have clients from 20 years ago, so they run into the same problem. This Company Data Archive problem is top notch, and as far as I know, the only one of its kind.

Kind regards,

Leslie

Was this reply helpful? Yes No