I have done countless SQL Server upgrades and rarely have issues after applying a Service Pack (SP) in terms of SQL Server and/or database uptime. However, here is one example where I ran into a serious issue with the master database after an SP4 upgrade on SQL 2012. This was one of the production SQL Servers, and the database engine did not come online after the SP4 upgrade. The impact was critical as 20 application databases were hosted on the same database server.
The process of patching is simple, and even simpler when the database server is standalone. I ran