How to resolve "DELETE from scheduler_run_details" Confluence Query locking up SQL database?

[Joshua Cook]

For the past month or so, after moving our instance of confluence from one server to another, we've been running into a sporadic problem in which a specific query that seems to automatically run against a confluence table in the Confluence SQL database seems to lock up all of the databases on our server.

The query in question is "(@P0 datetime) DELETE FROM scheduler_run_details WHERE start_time < @P0"

Looking at the sys.dm_exec_query_memory_grants table shows that this query is not being allocated memory and, once this happens, subesquent queries to unrelated tables are also not granted memory. The only way we've been able to resolve this problem, once it occurs, is to restart the server.

After a restart, everything will run just fine for a week or so, and then the problem will pop up again.

We do not know what is causing this query to run or why it is not being allocated memory in SQL. Any information on how to resolve this problem would be greatly appreciated. Please let me know what other details are need and thanks for your help in advance!

[AnnWorley]

I understand that you have to restart your MS SQL server when Confluence is running the query "(@P0 datetime) DELETE FROM scheduler_run_details WHERE start_time < @P0"

That query is for cleaning up the scheduled jobs table and runs so frequently that it may be a coincidence that it is the query that is running when MS SQL stops allocating memory.

• Since restarting the SQL server fixes the issue temporarily, I recommmend checking the resources (RAM) on the db server to make sure SQL has enough to allocate to all the databases all he time.
• Please let me know the version of SQL and Confluence you are running, to help me research further.
• Please consider moving Confluence to it's own instance of MS SQL so it doesn't impact all your databases while we figure this out.

[Joshua Cook]

Thanks for your response! 

We are running SQL Server 2012 (11.0.6248). Our confluence version # is 6.0.4.

I'm looking into the first and third bullet points now. Moving confluence to its owner instance of MS SQL may not be an option for now, but I will look into whether we can do this. I'll get back to you soon regarding server resources.

Thanks.

[Joshua Cook]

Thanks, again, for your help. It seems like this problem may be related to the server memory, after all. We've expanded the server resources from 8 gigs to 12 and will keep an eye on it over the next week. Thank you!

[AnnWorley]

Oh, good, I look forward to hearing the results. :)

[Joshua Cook]

It looks like the problem did end up being related to a lack of server resources (memory). Sorry for the confusion and thanks for all your help!

[Justin W.]

Hi Joshua,

I wanted to follow up on this issue as it's actually a common bug which we have fixed: https://jira.atlassian.com/browse/CONFSERVER-53381

I've listed a few workarounds (bandaids) in the bug linked above to reduce the frequency of the issue, but to fix this issue 100%, you'll need to upgrade to a fixed version, of 6.4.3 or greater. 

In the fixed versions, A Scheduled Job called 'Purge Old Job Run Details' has been introduced to purge scheduled job history.

Best Regards,
Justin