Recently, I ran into a critical error while I was helping a customer troubleshoot an issue in SQL Server. That may not sound like a big deal, but we were installing Cumulative Update 6 for SQL Server 2012 SP2 to fix our initial problem when we encountered the following error.
What gives SQL Server? We just upgraded several other instances on this same server. Why does this one have to fail right now? Well, a quick peak in the errorlog sure points me in the right direction.
Starting execution of SSIS_HOTFIX_INSTALL.SQL
:
:
Error: 9002, Severity: 17, State: 4.
The transaction log for database ‘SSISDB’ is full due to ‘ACTIVE_TRANSACTION’.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 3602, state 251, severity 25. 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.
Error: 3417, Severity: 21, State: 3.
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.
SQL Server shutdown has been initiated.
This is definitely not good. Here we are trying to apply CU6 to fix one problem, and it we get another one in return. Needless to say, the CU6 upgrade failed, and SQL Server was dead in the water. Restarting the SQL Server service will just continue to run the internal upgrade and return the same error message.
It’s easy to see the root cause of the problem; the transaction log for the SSISDB database is full. But if we can’t start the service how will we ever get the error fixed? Lucky for us, Microsoft has a trace flag for that. Trace Flag 902 will bypass the internal upgrade script on startup. Just open SQL Server Configuration Manager and add -T902 to the Startup Parameters, save the changes, and then start the service.
SQL Server started up just fine and fully recovered all databases; including SSISDB. However, there was a warning about 1 transaction being rolled back for SSISDB.
1 transactions rolled back in database ‘SSISDB’ (5:0). This is an informational message only. No user action is required.
This is most likely from the SSIS_HOTFIX_INSTALL.SQL script that failed during the upgrade. Next, we need to check the configuration and the file sizes of the SSISDB database. Sure enough, the log file is still at its original size of 1MB with autogrowth was disabled.
For now, we can enable autogrowth for the log file and then save the changes. Next, we’ll need remove the T902 trace flag from the Startup Parameters, and then restart the SQL Server service. Finally, SQL Server was able to startup without any errors. In the errorlog we can see the internal upgrade script ran successfully.
Starting execution of SSIS_HOTFIX_INSTALL.SQL
Start applying SSIS_HOTFIX_INSTALL changes.
:
:
Schema build in SSISDB has been updated to 11.0.5592.0
Execution of SSIS_HOTFIX_INSTALL.SQL completed
At this point, we need to rerun the CU6 setup again, so it can verify everything was correctly installed.
It shows the current patch level at 11.2.5592.0, which is correct, but the status is “Incompletely installed”. Just click next to continue with the upgrade to clear out the previous install failures.
So what is the lesson learned from this error? Should we leave autogrowth enabled on all databases before running a patch? Not necessarily, but it is an option. I think a better option is to keep a mental note of Trace Flag 902. With that trace flag, we were able to get SQL Server online long enough to correct the root problem, and we didn’t have to worry about changing any settings on a database.
References:
