March 1, 2011 at 6:28 am
Can somebody please give me a clue?
I have a number of SQL 2005 servers where the default trace is up and running and I have access to all the relevant reports. However I have just come across a server where the reports aren't available. Most of the forum comments/blogs centre around using reconfigure to disable and enable the default trace using
exec sp_configure 'default trace' with a 0 & 1 respectively.
This works fine but achieves nothing.
SELECT * FROM sys.configurations WHERE configuration_id = 1568
gives the expected results for an enabled default trace.
SELECT * FROM ::fn_trace_getinfo(0)
gives an empty result set, other servers show the pathname and max size of the individual trace files.
select * from sys.traces
gives an empty result set (implying the table is empty)
Exec sp_trace_setstatus 1, 1
gives a 'Could not find the requested trace' error.
At this precise moment in time I am not too concerned with the why just how do I fix it to get the default trace back up and running. (I rather think the why may have been due in part to the build process which was done in an emergency)
March 1, 2011 at 8:42 am
It is possible that the default trace got stopped due to an error. The SQL Server Error Logs should have that information recorded.
You can restart the SQL Server instance or you can try disabling and enabling back the Default Trace. This should start the Default Trace.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 1, 2011 at 9:25 am
Adiga (3/1/2011)
It is possible that the default trace got stopped due to an error. The SQL Server Error Logs should have that information recorded.You can restart the SQL Server instance or you can try disabling and enabling back the Default Trace. This should start the Default Trace.
Thanks for the reply Pradeep.
He he I knew I would forget something off my detailed cry for help. The log files do give some information but it is not too helpful.
It shows Error 19098, trace file path is invalid or the file is missing. I can confirm there is no path in the trace table (no data at all come to think of it) and there is no trace file in the log folder.
I have already tried disabling and re-enabling the Default trace without any success. I did think about restarting the SQL Server instance but I didn't want that to be a knee-jerk reaction, although one of the entries in the forum did say that the trace appeared the following day as if by magic.
I think it may be an instance restart before business tomorrow morning.
March 1, 2011 at 9:34 am
Since you have tried the other options, I feel restarting the SQL Server should help. There is nothing much we can configure in the Default Trace.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 1, 2011 at 9:37 am
Thanks Pradeep.
Now if only they hadn't removed the ability to write to the system tables.....:-D
May 24, 2012 at 9:15 am
We have the exact same issue as you had on one of our production database servers (MSSQL 2005). Did you fix it? If yes, could you please share your light with us? Thanks.
--------------------------------------------------------------
DBA or SQL Programmer? Who Knows. :unsure:
May 24, 2012 at 11:14 am
I finally found the solution without restarting the SQL server. Here are the steps I did:
1) Check if the Default Trace is enabled
2) If it is disabled, just enable it and the trc file will be created
3) If it is already enabled, disable it first and then re-enable it
--------------------------------------------------------------
DBA or SQL Programmer? Who Knows. :unsure:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply