Troubleshooting 1.00.002: Never forget the Windows Event Log!
This evening during some maintenance I was reminded of one very important rule: when looking into any issue on a windows server, never forget to check the Windows Event Log.
In the SQL Server upgrade log this was just expressed as “Service ‘MSSQL$OURSERVICENAME’ could not be started. Verify that you have sufficient privileges to start system services. The error code is (1814) The specified resource name cannot be found in the image file.”
I was running a SQL Server upgrade and sifting through the sql server log files, and it took me a few minutes to remember to look in the Windows log, where the error was expressed much more clearly: SQL Server was attempting to create tempdb on the D drive (not its usual location) in a directory that didn’t exist. The problem ended up being that the default database locations on the sql instance were still set to be on a disk that was not part of the cluster (and therefore not set properly as a dependency). CU4 apparently needed to create some files temporarily in this location when starting up SQL Services in the background as part of its installation.
So no matter how humble or weird the issue, I always have to remind myself: check the Windows Event log! Always! It’s one of the basics for a reason.
Update: the specific SP3 CU4 upgrade error was blogged by the CSS team here.