One of the common issues that SQL Server users may encounter is error 19062 – “Could not create a trace file.” This error occurs when there are insufficient permissions to modify system settings and resources. In this article, we will discuss the possible causes of this error and how to resolve it.
Let’s consider a scenario where a stored procedure is executed with a specific account, but the user receives error 19062. The error message indicates that a Windows error occurred while running SP_TRACE_CREATE and that a trace file could not be created.
Upon further investigation, it is discovered that the account used to execute the stored procedure has full control over the folder where the trace file is supposed to be generated. This raises the question of why access is denied despite having full control.
The error code 0x80070005, which corresponds to ‘ERROR_ACCESS_DENIED,’ suggests that there are insufficient permissions to modify system settings and resources. However, in this case, the account already has full control over the folder.
Further analysis using the process monitor reveals that when the stored procedure is executed, the folder is created successfully. However, an access denied error occurs when attempting to create the trace file. The process monitor shows that the SQL Service account, rather than the logged-in user, requires full control over the folder.
Based on this information, it becomes clear that resolving the issue requires granting full permissions to the SQL Service account on the folder where the trace file is generated.
Workaround/Solution
To resolve error 19062 – “Could not create a trace file,” follow these steps:
- Identify the SQL Service account using T-SQL:
SELECT servicename, service_account
FROM sys.dm_server_services;Alternatively, you can use SQL Server Configuration Manager to identify the SQL Service account.
Once you have identified the SQL Service account, grant it full control over the folder where the trace file is generated. This can be done by modifying the folder’s permissions and adding the SQL Service account with full control.
By following these steps, you should be able to resolve the error and successfully create the trace file.
Have you encountered a similar error where you were chasing different accounts? Share your experiences in the comments below!