Jira server functionality fails when using MS-SQL and READ_COMMITTED_SNAPSHOT is not set
症状
There are multiple scenarios in which this error can manifest when MS SQL server is used for JIRA:
- The upgrade process to JIRA 4.4 slows down to a halt during the upgrade procedure named "Converting Custom field values for Select and MultiSelect types to store the id of the option rather than the value". This has also been observed when upgrading from JIRA 4.3 to 5.1.
- Deadlocks are observed in MS SQL Server during normal operation.
- This can include larger jobs like created system backups
- Deleting custom fields does not delete reference link to custom field options and other reference link to it properly.
The following appears in the atlassian-jira.log in case of an upgrade:
2011-09-20 12:12:54,062 JiraImportTaskExecutionThread-1 INFO test 684x46x1 g5oswj 172.20.5.225 /secure/admin/XmlRestore.jspa [atlassian.jira.upgrade.UpgradeManagerImpl] Performing Upgrade Task: Converting Custom field values for Select and MultiSelect types to store the id of the option rather than the value.
Other symptoms include the upgrade process to halt later in the upgrade process while updating custom fields:
2012-08-28 13:53:59,725 main INFO [jira.upgrade.tasks.UpgradeTask_Build633] Updating custom field 'Application Server', 1 of 66
2012-08-28 13:53:59,986 main INFO [jira.upgrade.tasks.UpgradeTask_Build633] Updating custom field 'Automated', 2 of 66原因
This is caused by an MS SQL database misconfiguration for reading committed snapshots. The database is not configured correctly if this query returns a 0.
Please ensure that <JIRA-DATABASE> is replaced with your actual your database name.
SELECT is_read_committed_snapshot_on FROM
sys.databases WHERE name= '<JIRA-DATABASE>'ソリューション
- Shutdown JIRA, backup your database, and open up your MS-SQL Query console
Perform the below query on your database, please substitute JIRA-DATABASE for your database name. If your database is large this process may take some time.
ALTER DATABASE [JIRA-DATABASE] SET READ_COMMITTED_SNAPSHOT ONTest to see that the above query returns a 1 to confirm the settings change:
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'JIRA-DATABASE'- Start JIRA back up when this is complete and attempt the upgrade again.
- After the upgrade is successful the user may decide to turn off READ_COMMITTED_SNAPSHOT
Perform this on a test backup system first to confirm the results, if these changes perform any damage during testing then it is recommended to roll back to the database backup.