Plugin scheduler holding tx log open

[pricerc]

we've had an incident where the customer's log file grew to 16GB (what we've set its max to), on a data size of ~4GB, despite the presence of regular backups, both full and log.

This was discovered when users were no longer able to log on because the transaction log was 'full'

In SQL Management Studio, on running the standard report "Top Transactions by Age", there is/was a transaction lurking there that belongs to the JIWA plugin scheduler, that has been there for several weeks. We restarted the service, and things returned to normal; the log was 'shrinkable' to (nearly) zero.

36 hours later, I can see that the transaction started when we restarted the service is there:

Top Tx by Age

Plugin Scheduler Service Transaction.png (19.65 KiB) Viewed 26157 times

and the transaction log is now stuck on 3GB in 'limbo'.

The last executed SQL is

Code: Select all

(@LicenceID char(20),@ResourceID varchar(33),@HR_Staff_StaffID char(20))INSERT INTO SY_LicenceUsages(LicenceID, ResourceID, HR_Staff_StaffID, DateUsed) VALUES(@LicenceID, @ResourceID, @HR_Staff_StaffID, GETDATE())

and DBCC OPEN_TRAN confirms the problem:

Code: Select all

Transaction information for database 'JiwaLive'.

Oldest active transaction:
    SPID (server process ID): 96
    UID (user ID) : -1
    Name          : 5f95a656-a960-4227-86c4-7d881525
    LSN           : (2254:2096653:1)
    Start time    : Jul 21 2017  9:00:53:243AM
    SID           : 0x911628f138769d4fb25eec3431b29384
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I don't know what I might be doing in the plugins we're calling from in there (there are quite a few) to trigger this, but I thought I'd ask if this has been encountered before.

[Mike.Sheen]

I've logged DEV-6153 to look into this.

In the meantime, if you're able to - then scheduling a restart of the plugin scheduler service may mitigate the issue until we have a fix.

[pricerc]

I'd comment in the actual ticket, but I don't have/need access to your JIRA

Perhaps you could have the plugin scheduler "log off/log on" periodically, to close the open transaction, instead of restarting the whole service; which I've found is itself a little unreliable.

[Mike.Sheen]

Hi Ryan,

The changes needed to address this are in progress on DEV-6197 and will be in the next development build, 7.00.186.00.

Unfortunately we won't be able to patch previous versions with this change.

Mike