SQL SERVER – Msg 19062, Level 16, State 1. Could Not Create a Trace File. Error = 0x80070005

One of my clients reported a strange problem and I learned something new from it. Here is the conversation where we discussed how to fix error 19062 – could not create a trace file.

<EMAIL>
Hi Pinal
I have a stored procedure which is executed with account domain\appuser and we get the below error:

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070005(failed to retrieve text for this error. Reason: 15105).

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070005(failed to retrieve text for this error. Reason: 15105).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.
Error creating trace : 12

Now, interesting piece is that domain\appuser account is having full control on the folder where we are generating trace file.

Do you know how it that possible? Why we get access denied even though I have full control.
Thanks!
John

</EMAIL>

I did some research and found that error code 0x80070005 corresponds to access problem ‘ERROR_ACCESS_DENIED’. This indicates there are insufficient permissions to modify system settings and resources.  But John already mentioned that domain\appuser account is having full control of the folder where files are getting generated. I tried to review the process monitor and saw that when the stored procedure is executed, the folder is created and we get access denied for creating the trace file. We saw in the proc-mon that it asks domain\sqlserviceaccount to have full control rather than logged in user. Finally, I learned that this cannot be accomplished with another account. It will require a SQL Service account to have full permissions on folder to resolve the issue.

WORKAROUND/SOLUTION

The folder in which trace is getting created, we need to provide full control of the service account.

In the latest version of SQL, we can use below query to identify service account using T-SQL

SELECT servicename, service_account
FROM   sys.dm_server_services
GO

SQL SERVER - Msg 19062, Level 16, State 1. Could Not Create a Trace File. Error = 0x80070005 trace-error-01

Or, we can also use SQL Server configuration manager.

SQL SERVER - Msg 19062, Level 16, State 1. Could Not Create a Trace File. Error = 0x80070005 trace-error-02

Have you faced a similar error where you were chasing different account?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Best Practices About SQL Server Service Account and Password Management
Next Post
SQL SERVER – Stop Growing MSDB Database by Removing sysmail_mailitems History

Related Posts

No results found.

10 Comments. Leave new

Leave a Reply