While installing SQL Server 2012 in a client environment, they faced an interesting error about for target. Here is the information that is taken from SQL Server ERRORLOG. If you don’t know how to find Errorlog, please refer to my earlier blog. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
The below lines are taken from the ERRORLOG file. I have truncated the date-time column to make it crisp and concise.
Starting up database ‘tempdb’.
Database ‘master’ is upgrading script ‘u_tables.sql’ from level 502 to level 569.
Starting u_Tables.SQL at …
This file creates all the system tables in master.
drop view spt_values ….
Creating view ‘spt_values’.
drop table spt_monitor ….
Grant Select on spt_monitor
Insert into spt_monitor ….
Error: 25641, Severity: 16, State: 0.
For target, “package0.event_file”, the parameter “filename” passed is invalid. Target parameter at index 0 is invalid …
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. 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
SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
When I search for errors found in ERRORLOG file, I found many results that were not relevant.
If you have read my earlier blogs, there are many situations where SQL upgrade was failing with error: “Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it”. In such situations, we can start SQL with special trace flag 902, fix the cause of the error, remove 902 and continue with install/upgrade. In my client’s situation they were getting the “Retry” option with a message – Wait on the Database Engine recovery handle failed. Check the SQL Server error log for a potential cause.
The path for file u_tables.sql is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Install\u_tables.sql”
If you open the file, you can notice that the below section is relevant.
We can see the message “Insert into spt_monitor ….” printed in ERRORLOG just before failure which confirms that create event session is failing.
We tried creating extended event session and it failed with the same error what we are seeing in SQL ERRORLOG. We captured process monitor and found that the SQL Service account did not have permissions on the drive (E drive in our case) where the LOG folder was located. This was confirmed by seeing “ACCESS DENIED” via process monitor.
The solution which would work is to give the SQLServer service Read Permissions at the root level of the disk where ……MSSQL11.MSSQLSERVER\MSSQL\Log\ is located. After giving the account these permissions there were no more errors during the Installation/Upgrade.
Note: The same error can also appear when a service pack is applied. Mostly there were some hardening performed on the server which caused service account permission to get modified.
Reference: Pinal Dave (https://blog.sqlauthority.com)