Enormous SQL log file

Epicor ERP 10
,
1

I got an alert today that our partition which stores our SQL log files is running out of space. Well it seems that the Epicor DB is currently 8x the size of the database itself. I used SSMS to go to Task → Shrink Files and told it to shrink the log file, but there is no change when I do this. That same step works as expected for all other DB’s, but not the live Epicor DB.

Our database recovery model is set to SIMPLE.
(I understand there are very opinionated people on this topic, but I have zero interest in debating SIMPLE vs FULL here in this thread, so please bite your tongue if you feel the need to debate that)

From skimming several SQL articles, it seems that there may be a locked transaction that is forcing the log file to be unable to shrink? Does anyone have any experience with how to fix that?

Here’s a history of the .LDF file size for the Epicor DB based on backups.
76GB - 10/26 @ noon - Thurs
69GB - 10/25 @ noon - Wed
63GB - 10/24 @ noon - Tues
63GB - 10/23 @ noon - Mon
63GB- 10/22 @ noon - Sun
63GB - 10/21 @ noon - Sat
57GB - 10/20 @ noon - Fri
57GB - 10/19 @ noon - Thurs
57GB - 10/18 @ noon - Wed
52GB - 10/17 @ 6PM - Tues
52GB - 10/16 @ noon - Mon

2

what does DBCC OpenTran say?

3

It says this. Does that mean anything to you @Olga ?

Transaction information for database ‘E10Live’.

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (16751:258809:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Completion time: 2023-10-26T13:20:17.2700897-04:00

4

it tells that you don’t have any old open transaction.
Did you see this link SQL SERVER - Huge Transaction Log in Snapshot Replication! - SQL Authority with Pinal Dave

1 Like
5

Wow, you nailed it. That was exactly it! We do have snapshot replication set up. Executing this command:

exec sp_repldone null, null, 0,0,1

and then telling SSMS to shrink the log file brought it down from 76GB to just 255 MB.

Thank you!

1 Like