September 19, 2007 at 9:24 am
I've been running Profiler traces everyday for months in SQL 2000 with no problem. (dumping trace data into a 2000 table)
In 2005, I setup the exact trace characteristics but receive error msg: Failed to start a new trace.
Steps I use to setup the 2000 and 2005 trace:
o Remote Desktop onto SQL Server 2000 or 2005; each w/ Windows 2003 Server
o Start SQL Profiler (via Tools menu)
o Select File, New Trace, Trace Name: TRACE, Save to Table MyDB.dbo.TRACE
o Enable trace stop time: 1 hour from now
o Event Selection (I use the defaults provided)
o Run (and in SQL 2005 I almost immediately receive the failure messsage)
I am SA on every server in my environment. Any thoughts on this one?
September 19, 2007 at 11:50 am
Run this to see if there are any server-side SQL traces running... I'm betting there is one other than the default.
SELECT * FROM fn_trace_getinfo(0)
September 19, 2007 at 12:11 pm
Interesting. I do have a trace running.. (E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_16.trc) as per:
http://sqljunkies.com/WebLog/outerjoin/archive/2005/10/16/17157.aspx
To stop the MS SS2005 default trace, I ran:
sp_configure 'default trace enabled', 0
RECONFIGURE
Question is -- Can't I have 2 traces running in parallel? (the default trace and my custom trace?)
September 19, 2007 at 12:18 pm
Sure, you can have bunches of traces running. However if you export the trace data to the same location (I.E. the same file) you can't. I run other traces in addition to the default trace all the time with no problem at all.
**I keep editing this post, sorry**
Try running your trace as a server side trace in T-SQL instead of using the SQL Profiler. In profiler, script the trace and save the T-SQL. Then pull the SQL into Query Analyzer and run it. Make sure when you define the server side trace that you set the option to allow the server to process the trace data, and if you are using file output, specify a UNC path for the file.
September 24, 2007 at 11:32 am
I cannot get a SQL Profiler trace, dumping to a SQL 2005 table, to run on our new SQL SERVER 2005 environment. I continually receive the error msg "Failed to start a new trace." I am sysadmin on all the SQL servers. I tried invoking from both my desktop and via Remote Desktop to the SQL Server.
I verified the only trace running was the default trace: select * from sys.traces
I stopped the default trace using this SQL: sp_configure 'default trace enabled', 1; RECONFIGURE
One caveat, we moved TEMPDB to a different drive and also relocated master, model, and msdb to different data/log drives than the default installation. (something we always did in 2000 as well)
The SQL Logs contain 2 msg's; 1 for the start and 1 (gen'd after the FAILED msg) 1 for the stop:
Message 1: SQL Trace ID 2 was started by login "MyDomain\myUserID'.
Message 2: SQL Trace stopped. Trace ID = '2'. Login Name = MyDomain\myUserID'.
Thoughts?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply