Restoring System throws java.sql.SQLException: This index operation requires XXXX KB of memory per DOP
問題
Restoring backup.xml throws the following error
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
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.原因
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.
ソリューション
- 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.
