Recently, after an security fix GDR (https://support.microsoft.com/en-us/kb/3194716) on SQL Server 2016 RTM, SQL Service refused to start. Though, the patch completed successfully as per
"C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log", the Event viewer under "System" gave the following error
"The SQL Server (I2016) service terminated with the following service-specific error: %%945"
Error didn't make much sense
The eventviewer under "Application" section gave a lot more meaningful error
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\MSSQL2016Root\MSSQL13.I20161708\MSSQL\Binn
\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
The above issue occurred on Windows 10 laptop with SQL Server 2016 Developer edition. Please note that, GDR was applied automatically by windows patch update. Yes, we would not schedule auto update of SQL Security Patches on production machines but suspect the issue would have occurred even had the GDR been manually installed.
Hope this post would help someone, trying to troubleshoot SQL Service failure issue after applying security fix.
"C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log", the Event viewer under "System" gave the following error
"The SQL Server (I2016) service terminated with the following service-specific error: %%945"
Error didn't make much sense
The eventviewer under "Application" section gave a lot more meaningful error
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\MSSQL2016Root\MSSQL13.I20161708\MSSQL\Binn
\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
From the error it was fairly clear that "mssqlsystemresource.mdf", "mssqlsystemresource.ldf" were missing. A windows search of those two files showed that the files were mysteriously moved to "C:\Program Files\Microsoft SQL Server\130\LocalDB\Binn"
Even though, there is no local DB installed, the security fix had likely moved the "mssqlsystemresource.mdf", "mssqlsystemresource.ldf" to a different folder causing the SQL Server to fail while starting.
The fix was simple. Copy "mssqlsystemresource.mdf", "mssqlsystemresource.ldf" files from "C:\Program Files\Microsoft SQL Server\130\LocalDB\Binn" to its original location which was "D:\MSSQL2016Root\MSSQL13.I20161708\MSSQL\Binn\". After the file copy SQL Service started as usual.
The above issue occurred on Windows 10 laptop with SQL Server 2016 Developer edition. Please note that, GDR was applied automatically by windows patch update. Yes, we would not schedule auto update of SQL Security Patches on production machines but suspect the issue would have occurred even had the GDR been manually installed.
Hope this post would help someone, trying to troubleshoot SQL Service failure issue after applying security fix.
1 comment:
Another nearly perfect guide on SQL Security, thank you!
Post a Comment