We go live on Sept 2020. I found that our live database increasing size quite huge.
Sept = 2.5gb Oct = 9.6gb Nov = 16gb Dec = 23gb Jan = 31gb Feb = 39.5gb
Each month increase around 7gb.
Expect live database file size will be 300gb on 2024
We are using Ms SQL Server 2016 and epicor 10.2.500.20
Is it consider abnormal?
Do you have ESE deployed?
Could you guide me how to check ESE? I check in SQL server configuration manager didn’t found ESE. thanks for your reply.
Definitely something wrong there.
Is it the datafile or the logfile that is increasing in size? in SSMS right click on the db and select properties.
If its the logfile check your database recovery option and your backup plans.
If you have full recovery and there are no backups happening then the logfile will continue to grow.
If it is the data file then run the top tables report from SSMS. This will tell you which tables are using all the disk space.
Brett
1 Like
Hi Brett,
Thank you very much for guide me to troubleshoot.
Found that cdc.CaptureLog using 35gb.
Could you guide me how to reduce this table usage size?
1 Like
Do you have Epicor Social / Epicor Collaborate installed?
Not, I didn’t install and Epicor server don’t have Epicor Social/ Collaborate.
See the following
https://www.sqlshack.com/change-data-capture-for-auditing-sql-server/
Make sure a CDC Cleanup job exists
1 Like
Guys, thank you very much! You all help me a lot.
I thought there was a CDC cleanup routine somewhere - thanks @ckrusen.
I would also keep digging to find out whats creating the records. 7Gb of CDC data each month is a lot considering your database started out at 2.5Gb. Even if it is cleaned up that is still a lot of churn going on there. Have a look at the records, the data in there might give a clue to whats creating the records.
Brett
Yeah, I plan to export cdc.CaptureLog to excel for further checking before cleanup process. Anyway, thank you so much. You save me
The link to the sqlshack page goes into depth as to what CDC is, how to determine if it is enabled, how to enable it, and how to disable it.
1 Like
You’ll need to set up maintenance routines to run weekly, or thereabouts. To truncate the change log table and compress the Db. If you are not comfortable, find a SQL consultant, they can assist to set up the routine. Once in place you won’t have any more issues.