Recently we received a request from the customer to run SQL Profiler trace on a Production Instance for a weeks time. No, not to track any performance issues. They wanted to check if someone is still using one of the databases on that instance. If not, they were planning to decommission it.
I had discussed about the benefits of Server Side traces over SQL Profiler trace here. Team was well aware of this and were all set to run a Server Side trace for one week. They had scripted out a standard profiler trace and did the modifications. When the script was executed, it returned Error Code 12.
As per the official documentation, Error Code 12 meant File not created. Since the error message was regarding the Trace Output file not being generated, the sp_trace_create statement in the script which defines the trace output should have some issues.
The missing link in the script was glaring. The Output File parameter did not have the Trace File Name, instead it ended with only the folder name. Quickly this path was modified and it was \\ServerName\ShareName\TraceOutputFolder\TraceName. The script was executed again but surprisingly same error message. ErrorCode 12! The folder existed, the service account has full permissions on that folder. What is missing now?
A closer look at the Trace Output folder revealed that there was a leading space just before the \\ServerName. Huh! Removed that space and the trace started running smooth. This is an example of missing small things while the focus is on the bigger issue.