Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Monday, March 19, 2018 1:16 PM | 1 vote
Last week I performed an upgrade in place of an SQL Server 2016 Standard Edition to SQL Server 2017 Standard Edition. The upgrade failed and the Database Engine would not start. Part of the SQL Error Log Shows the following:
Database 'master' is upgrading script 'ISServer_upgrade.sql' from level 0 to level 500.
2018-03-15 14:06:20.43 spid6s
2018-03-15 14:06:20.43 spid6s Starting execution of ISServer_upgrade.SQL
2018-03-15 14:06:20.43 spid6s
2018-03-15 14:06:20.43 spid6s
2018-03-15 14:06:20.43 spid6s Taking SSISDB to single user mode
2018-03-15 14:06:20.44 spid6s Setting database option SINGLE_USER to ON for database 'SSISDB'.
2018-03-15 14:06:20.80 spid6s Error: 1712, Severity: 16, State: 1.
2018-03-15 14:06:20.80 spid6s Online index operations can only be performed in Enterprise edition of SQL Server.
2018-03-15 14:06:20.80 spid6s Error: 917, Severity: 15, State: 1.
2018-03-15 14:06:20.80 spid6s An upgrade script batch failed to execute for database 'master' due to compilation error. Check the previous error message for the line which caused compilation to fail.
2018-03-15 14:06:20.80 spid6s Error: 912, Severity: 21, State: 2.
2018-03-15 14:06:20.80 spid6s Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 917, state 1, severity 15. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2018-03-15 14:06:20.81 spid6s Error: 3417, Severity: 21, State: 3.
2018-03-15 14:06:20.81 spid6s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2018-03-15 14:06:20.81 spid6s SQL Server shutdown has been initiated
Added Trace Flag 3601 to startup and looked at SQL Error Log again to see what was being run in the script. 'ISServer_upgrade.SQL' was adding tables and indexes to database SSISDB.
The last script line executed is:
CREATE NONCLUSTERED INDEX [index_tasks_ReadyForDispatchTime]
ON [internal].[tasks]
(
[ReadyForDispatchTime] ASC
)
INCLUDE ([CreatedTime], [CreateWorkerAgentId], [ExecutedCount], [ExpiredTime], [InputData], [IsCritical], [JobId], [LastUpdatedTime], [MaxExecutedCount], [Priority], [Status], [TaskType])
WITH (ONLINE = ON)
Followed by the Errors:
2018-03-16 14:54:26.07 spid6s Error: 1712, Severity: 16, State: 1.
2018-03-16 14:54:26.07 spid6s Online index operations can only be performed in Enterprise edition of SQL Server.
2018-03-16 14:54:26.07 spid6s Error: 917, Severity: 15, State: 1.
2018-03-16 14:54:26.07 spid6s An upgrade script batch failed to execute for database 'master' due to compilation error. Check the previous error message for the line which caused compilation to fail.
2018-03-16 14:54:26.07 spid6s Error: 912, Severity: 21, State: 2.
2018-03-16 14:54:26.07 spid6s Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 917, state 1, severity 15. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
Problem should be easy to correct. Open 'ISServer_upgrade.SQL' and remove the syntax WITH (ONLINE = ON) in the create index script since this is a Standard Edition Upgrade. But I cannot locate the script. I have checked <Drive>:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Install, but it is not there. It does not appear to be on the install media either.
Can someone point me to this scrip? Thanks,
Wednesday, April 4, 2018 11:04 AM ✅Answered | 4 votes
Hi John,
I guess you have already opened a support case with SQL Team and I got a chance to review that.
The failing script is part of the DLL and that's why you are not able to see ISServer_upgrade.SQL file in the OS.
Looks like RTM installation has this issue where upgrade script was always building index ONLINE and hence failing.
The new (fixed) code takes care of checking edition and then creating index ONLINE only for ENT and DEV edition.
Here is the plan for you to fix the issue:
++ After upgrade start SQL with trace flag 902 (you have done this already)
++ Apply RTM CU5. It should get applied
++ Remove trace flag and let the upgrade script run.
Balmukund Lakhani
Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog | Team Blog | @Twitter | Facebook
Author: SQL Server 2012 AlwaysOn - Paperback, Kindle
Tuesday, March 20, 2018 8:29 AM
Hi JCRsr,
What's version of SQL Server 2016 did you use? SP1 or RTM?
>>Open 'ISServer_upgrade.SQL' and remove the syntax WITH (ONLINE = ON) in the create index script since this is a Standard Edition Upgrade.
It looks like that this is an invisible script which only uses inside SQL Server. I suggest you opening a case in https://feedback.azure.com/forums/908035-sql-server
As a workaround, we can also use the side-by-side upgration, we can first use /T902 to start this instance and then migrate to the SQL Server 2017 instance.
Best Regards,
Teige
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Tuesday, March 20, 2018 11:30 AM
Teige,
I am upgrading SQL 2016 SP1 to SQL 2017. This is not an Azure instance.
I have used the 902 Flag to start the instance and all the upgrades are complete except the update to SSISDB. The script 'ISServer_upgrade.SQL' updates this database. I cannot locate this script on my server or the install media to make changes to it. If the script is a hidden file that is not available to me, my only workaround appears to be to uninstall the instance and re-install it. This is not a good solution.
Thanks,
Wednesday, April 4, 2018 11:46 AM
Balmukund,
I followed the steps in your reply and the SQL instance is online after upgrading.
To restate your answer.
1). SQL Instance is running with /902 flag.
2). Patch instance with SQL 2017 Cumulative Update 5 (14.0.3023.8)
3). When patch is complete, remove /902 flag and restart instance.
4). Instance starts normally and completes 'master', 'msdb', & SSISDB updates.
Thanks,
John R.
Wednesday, April 4, 2018 2:59 PM
Thanks for the confirmation, John.
Balmukund Lakhani
Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog | Team Blog | @Twitter | Facebook
Author: SQL Server 2012 AlwaysOn - Paperback, Kindle