SQLServer: Failed to re-open log file 5 times. Logging is deactivated.

Document ID : KB000101549
Last Modified Date : 14/06/2018
Show Technical Document Details
Issue:
sqlserver alarm: SQLServer: Failed to re-open log file 5 times.  Logging is deactivated and Permission denied errors in the sqlserver log.
Environment:
- UIM 8.x or higher
- sqlserver probe
- Windows Authentication
Cause:
The probe cannot create .MET files in the robot's niscache folder where the probe is dep0loyed. This results in the QoS data not being inserted into the database and the data is not visible in USM, and/or a file, log file cannot be written to.
Resolution:
1. Confirm that the configure sqlserver user has full access/security/control rights.
2. Confirm that a full exception has been configured for all Nimsoft programs/dirs/files.
3. Check that the sqlserver probe "Access Prerequisites" here in the release notes have been met:
https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-sql-server-monitoring-release-notes#sqlserver(SQLServerMonitoring)ReleaseNotes-AccessPrerequisites

4. For the 'Failed to re-open log file sqlserver_monitor alarm', please apply this key via sqlserver probe Raw Configure mode to address this issue.

Follow these steps:

    a. Select Raw Configure-> setup
    b. Set the value of the flag_reverttoself option to yes
    c. Apply the change, then cold start (deactivate, then activate) the sqlserver probe.

This should allow the probe to roll over and re-open the sqlserver_monitor.log file to continue logging.

This also documented here:
https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-troubleshooting

You also may have to check with your DBA to make sure the correct role/permissions get added.

In many other cases where this error  occurs, what  is described above, resolved the problem.

If the above doesn't resolve the errors, it is quite possible that the probe when the sqlserver probe is configured to use Windows Auth for the database connection may get caught in a context switch when the log file is to be rolled to the backup (meaning that instead of the commands being executed as the Local SYSTEM Account (which the robot runs as) it may be in the SQL Windows NT User which does not have access to the local filesystem (niscache, and probe directory) and results in access being denied.

5. If you added the new parameter and still experience the problem/errors, please have your DBA grant the database user access to the robot niscache and sqlserver probe folders and then restart the probe and see if the errors are alleviated.