How would I shrink the Epicor SQL databases?


(Shawn Hobdy) #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


(Andris Skulte) #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.


(Nathan your friendly neighborhood Support Engineer) #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?


(Chia Chang) #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?


(Nathan your friendly neighborhood Support Engineer) #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


(Chia Chang) #6

What are ABTWorks records before deleting?


(Ken Nash) #7

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


(Nathan your friendly neighborhood Support Engineer) #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.


(Chia Chang) #9

It should be on the server.


(Ken Nash) #10

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


(Chia Chang) #11

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

image


(Ken Nash) #12

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


(Nathan your friendly neighborhood Support Engineer) #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.


(Ken Nash) #14

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


(Nathan your friendly neighborhood Support Engineer) #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.


(Rob Bucek) #16

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


(MIGUEL S.) #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.)


(Ken Nash) #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.