Epicor 10.0.700.4 - SQL Server maintenance (DB, file size, etc.)

Disregard this request. I seem to have found the source of our problems. In case anyone else has similar issues in the future, our problem was a table in the 10.0 DB called cdc.CaptureLog. Apparently it is related to Epicor Social and is meant to delete rows older than 2 days old. There was/is a known bug in 10.0 that prevents these rows from being deleted. We have been live on E10 for about 14 months and the table was just over 85 GB oout of our roughly 110 GB db. Epicor gave me a script (attached below) to run that will clear out the unnecessary rows.

Thanks,
Jay


PROBLEM DESCRIPTION:

Our cdc.capturelog table is growing.  Our understanding is that Epicor Social Enterprise uses this table, but, is there a way to purge some of the data?

 

PROBLEM RESOLUTION:

Related to SCR 140574.  Our intention is to delete records that are older than 2 days from this table.  You can manually run the script below in SQL Management Studio to remove all records on that table that are older than 2 days and can be tied to a SQL agent job if you wish to run automatically.  If you wish to replace the space these records were using, you can shrink the database but you should rebuild indexes afterwards to address the index fragmentation.

 

<----copy below this line----->

-- You NEED to backup your database before executing this query!

-- Change [DatabaseNameHere] to the name of the database that you are running this against

 

use [DatabaseNameHere]

delete from cdc.capturelog

where DATEDIFF(dy, OccurredWhenUTC, getdate()) >= 2

 

<-----copy above this line----->

2 Likes

We have been live on Epicor 10 for a bit over a year now and I have some questions about normal SQL maintenance. Before E10, we were on Vantage 8.03 with a Progress DB, so this adventure into E10 is my first experience with SQL.


What is the normal maintenance that should be done regularly or how do I "maintain" my SQL servers. With Progress, I know I had to go in and truncate the log file occasionally to conserve disk space. I am doing something similar (I think) on the recommendation of Epicor support when I notice our E10 server getting low on disk space, where I go into SQL and backup and shrink the .ldf files.


Problem now is that my mdf file is growing pretty substantially and I am not sure what I should do on a regular basis (maybe scheduled SQL jobs) to keep these items in check.


Our data volume on the SQL server is 450 GB. Our live DB currently has a 111 GB mdf file and a 31 GB ldf file (that is after shrinking the ldf a few weeks ago, as it normally grows to 100 GB+ before shrinking).


I feel sure that our file sizes are abnormally large (we do a lot of jobs, orders, invoices, etc. and have a large amount of part numbers/engineering), but something doesn't seem right. Any help would be appreciated.


Jay