When it comes to applying service packs for SQL Server, it is usually a straightforward process. However, there are times when the installation fails, resulting in issues with the SQL Server services. In a recent case, a client faced a problem while installing SQL Server 2014 SP2, which caused the SQL Server services to not start. This issue, commonly known as script upgrade mode failure, can occur in various scenarios.

During the patching process, SQL Server goes through a script upgrade mode, where objects inside the databases are upgraded based on the applied patch. This phase can take varying amounts of time depending on the features installed and the number of databases.

Upon investigating the log files, it was discovered that the script upgrade failure was caused by an error while executing the stored procedure ‘sp_vupgrade_replication’. Additionally, the object ‘MSreplication_objects’ was not found. This object is crucial for replication and needs to be present in the system and user databases.

To resolve this issue, a workaround was implemented using trace flag 902, which bypasses the script upgrade mode. By starting SQL Server with this trace flag, the problem script did not run, allowing access to the database in question. The missing ‘MSreplication_objects’ table was then scripted from another database and created in the affected database. However, the created stored procedure was initially created as a user stored procedure instead of a system stored procedure. To promote it as a system stored procedure, the undocumented stored procedure ‘sp_MS_marksystemobject’ was used.

After successfully executing the script and promoting the stored procedure, SQL Server was restarted without the trace flag, and the services started successfully.

Script upgrade mode failures can occur in various flavors, and it is essential to understand the underlying causes and potential solutions. By following the steps outlined in this article, you can overcome script upgrade mode failures and ensure a smooth patching process for your SQL Server.

Have you encountered any other flavors of script upgrade mode? Share your experiences in the comments below!