How would I shrink the Epicor SQL databases?

Epicor ERP 10
1

Hello,

Our SQL databases are getting a little out of hand and I was wondering if there is any certain way to shrink them to get them back under control?

Any ideas/answers are appreciated.

Thanks,

Shawn

2

You can use Database Purge and Summarize.

Our biggest offender is change logs - we’ve had some turned them on for entire tables (from some consulting go-live advice) and now looking at what we can turn off since the dust has settled and folks are trusting the system more.

3

I would recommend getting an idea of where space is being consumed before attempting anything specific.

In SQL Server Management Studio (SSMS), right-click on your Database > Reports > Standard Reports > Disk Usage by Table.

What are the top ~20 tables listed in the report?

image
image.png753Ă—400 33 KB

4 Likes
4

I also looked at this in SSMS and here are our top 5 tables (across 4 databases).

dbo.IMJobOper - 16gb
dbo.TranGLC - 15gb
dbo.IMJobMtl - 13gb
dbo.IMGlbCustCred - 12gb
dbo.abtwork - 10gb

We don’t do anything with GlbCustCred so wondering why that is consuming data space. Any other thoughts on the other tables?

5

dbo.IMJobOper and dbo.IMJobMtl - could be orphaned intercompany transactions, could be orphaned records from MRP. open a support case and they can triage.

dbo.IMGlbCustCred - 12gb <-- the only references I see to this table in our KB are due to issues with intercompany configuration. open a support case on this and they can triage.

dbo.abtwork - 10gb <-- from admin tools, run the following (in a test db first) Conversion 11065: Delete garbage ABTWork records

dbo.TranGLC - 15gb <-- not abnormal

6

What are ABTWorks records before deleting?

7

Where is this admin tools? on the server or conversion workbench or something else all together?

8

ABTwork is the table that stores runtime parameters for each individual GL posting. Our financial support team can provide more detail if you are curious.

9

It should be on the server.

10

I am not seeing it. In the Epicor Admin Console? or another Admin tool?

11

For us, if you go under Start > Programs, it is under the Epicor Software > Epicor 9.05 folder.

image

12

We are on E10. That doesn’t appear to be there.

13

Sorry, this thread should have really been split. My response to Chia was specific to their Epicor ERP 9.05 version and this thread started out as a conversion about Epicor 10.

14

Thanks, so those of us on e10 are not able to do this?

15

Admin Tools doesn’t exist in E10, no.

All SQL customers, regardless of version, can go through the top tables report to determine their largest tables, but, depending on version / the table(s) in question there will be different ways to approach it.

1 Like
16

just cuz i was curious… TranGLC 15GB - Index for TranGLC 52GB seems a little cray

17

New to SQL, but are you talking about your MDF or LDF files? Are you doing transaction log backups? Our logs were getting big, but we were not backing up properly. (we are still in a test environment.)

18

Sorry. I am talking about our ABTHead and ABTWork table is over 50% of the total space of our database. Those tables make up almost 15GB of the total 27GB. Though our total DB is small, those tables seem to be a bit larger than they should be.

19

Hi Guys.

Can we delete records fully from ABTwork table with testing? We are on 10.1.500.15.

BR