Restoring System throws java.sql.SQLException: This index operation requires XXXX KB of memory per DOP
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
Restoring backup.xml throws the following error
1
2
3
4
5
6
7
8
9
Exception thrown during upgrade: Could not drop index: SQL Exception while executing the following:
DROP INDEX dbo.jiraissue.issue_proj_num
Error was: java.sql.SQLException: Cannot drop the index 'dbo.jiraissue.issue_proj_num', because it does not exist or you do not have permission
java.lang.RuntimeException: Could not drop index: SQL Exception while executing the following:
DROP INDEX dbo.jiraissue.issue_proj_num
Error was: java.sql.SQLException: Cannot drop the index 'dbo.jiraissue.issue_proj_num', because it does not exist or you do not have permission
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build6132.dripIndex(UpgradeTask_Build6132.java:69)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build6132.dripIndex(UpgradeTask_Build6132.java:49)
at com.atlassian.jira.upgrade.UpgradeManagerImpl.doUpgradeTaskSuccess(UpgradeManagerImpl.java:693)The following appears in the atlassian-jira.log
1
2
3
2015-01-13 06:22:45,160 localhost-xxxxxx-1 ERROR [atlassian.jira.upgrade.UpgradeManagerImpl] Exception thrown during upgrade: Could not create index: SQL Exception while executing the following:
CREATE UNIQUE INDEX issue_proj_num ON dbo.jiraissue (issuenum, PROJECT)
Error was: java.sql.SQLException: This index operation requires 1024 KB of memory per DOP. The total requirement of 2048 KB for DOP of 2 is greater than the sp_configure value of 1000 KB set for the advanced server configuration option "index create memory (KB)". Increase this setting or reduce DOP and rerun the query.Cause
From the error message, we can see that the DOP setting is set to 2, while the index operation requires 2048KB for the total of 2 DOP. However, the index creation memory is set to 1000KB, which is too small compared to what's needed. Thus, the solution is to change the index creation memory to a value greater than 2048KB or change it to 0 if you want SQL Server to dynamically manage the index creation memory.
Resolution
Connect to your SQL Server instance in SQL Server Management Studio. Right click on your connected SQL Server instance and choose Properties as per the screen shot below.
Click on the Memory page in the left pane. Your memory property page will appear like the screenshot below. Here you will see our "Index creation memory" option is set to 1000. You change the DOP setting as well on the Advanced page to fix this issue, but changing the DOP value may impact other performance issues for the entire SQL Server instance.
Since our DOP setting is set to 2, we will set our index create memory value to 2048. This is determined by (minimum memory per query (in KB) * DOP setting). So if we look at the screen shot below our minimum memory per query is set to 1024 and our DOP setting is 2, so the value is 1024 * 2 = 2048. If we want SQL Server to dynamically manage the index creation memory we would set the value to 0.
Was this helpful?