SQL SERVER – Assembly ‘ISSERVER’ Was Not Found in the SQL Catalog of Database ‘SSISDB’

Due to my connect with my many clients, learning never stops for me. Almost every day someone sends me email for quick 55 minute consultation. You can read more about it over here about how to fix error related to SQL Catalog.

This time it was a business down scenario where the SQL patch was applied, and SQL was not getting started. As usual, I always start looking into ERRORLOG log.

2017-10-25 18:28:19.41 spid7s Altering assembly [ISSERVER] 2017-10-25 18:28:19.42 spid7s Error: 50000, Severity: 16, State: 127.
2017-10-25 18:28:19.42 spid7s Cannot drop the assembly ‘ISSERVER’, because it does not exist or you do not have permission.
2017-10-25 18:28:19.42 spid7s Creating function internal.is_valid_name
2017-10-25 18:28:19.43 spid7s Error: 6528, Severity: 16, State: 1.
2017-10-25 18:28:19.43 spid7s Assembly ‘ISSERVER’ was not found in the SQL catalog of database ‘SSISDB’.
2017-10-25 18:28:19.43 spid7s Error: 912, Severity: 21, State: 2.
2017-10-25 18:28:19.43 spid7s Script level upgrade for database ‘master’ failed because upgrade step ‘ISServer_upgrade.sql’ encountered error 6528, state 1, severity 16. 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.

In errors like above, the only way to start SQL Service is via trace flag 902. As per articles, it would skip the upgrade scripts and we can fix the real cause of the error. Once the cause is identified and fixed, we should remove the trace flag and start SQL normally.

WORKAROUND / SOLUTION

As I mentioned earlier, first we started SQL with trace flag 902. I started SQL using trace flag 902 as below via command prompt.

NET START MSSQLServer /T902

Refer: SQL SERVER – 2005 – Start Stop Restart SQL Server from Command Prompt

As soon as SQL was started, we were able to connect because upgrade didn’t run. When I checked SSISDB, we found that ISSERVER is indeed missing. Now, I had no idea how to recreate it. There was no backup available for SSISDB database. We saw that Integration Services Catalog was created. We also saw a few projects created. But the folders were empty.

It looks like the SSIS catalogs are not used here. My client agreed to create a new catalog by deleting the old one. I deleted catalog and SSISDB database from management studio. Then we created a new catalog using below option in SSMS.

In new catalog, we can see the assembly. So, we removed 902 and restarted SQL Service.

Using above trick, we were able to fix the issue. If there is a way to do the same without dropping catalog, please share as it would help other readers.

Reference: Pinal Dave (https://blog.sqlauthority.com)