Plugin scheduler holding tx log open  Topic is solved

Support for Microsoft SQL Server in the context of Jiwa installations.

Plugin scheduler holding tx log open

Postby pricerc » Sat Jul 22, 2017 3:33 pm

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:
Plugin Scheduler Service Transaction.png
Top Tx by Age
Plugin Scheduler Service Transaction.png (19.65 KiB) Viewed 2542 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.
/Ryan
User avatar
pricerc
Frequent Contributor
Frequent Contributor
 
Posts: 168
Joined: Mon Aug 10, 2009 11:22 am
Topics Solved: 5

Re: Plugin scheduler holding tx log open

Postby Mike.Sheen » Mon Jul 24, 2017 10:07 pm

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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Jiwa Shihan
Jiwa Shihan
 
Posts: 1447
Joined: Tue Feb 12, 2008 10:12 am
Location: North Sydney
Topics Solved: 486

Re: Plugin scheduler holding tx log open

Postby pricerc » Mon Jul 31, 2017 8:20 pm

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.
/Ryan
User avatar
pricerc
Frequent Contributor
Frequent Contributor
 
Posts: 168
Joined: Mon Aug 10, 2009 11:22 am
Topics Solved: 5

Re: Plugin scheduler holding tx log open  Topic is solved

Postby Mike.Sheen » Sat Aug 26, 2017 2:11 pm

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
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Jiwa Shihan
Jiwa Shihan
 
Posts: 1447
Joined: Tue Feb 12, 2008 10:12 am
Location: North Sydney
Topics Solved: 486


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron