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

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!

 

1 answer

2 votes
Ann Worley Atlassian Team Jul 19, 2017

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.

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.

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!

Ann Worley Atlassian Team Jul 20, 2017

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

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!

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, Scheduled Job called 'Purge Old Job Run Details' has been introduced to purge scheduled job history.

Best Regards,
Justin

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Thursday in Confluence

What are your project planning tips?

Hello Community,  Jessica here from the Confluence product marketing team! Today I wanted to get your takes on project planning –– what works, what doesn’t, how do you know if you’re doing it r...

168 views 0 3
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you