No Profile Trace on SQL 2000 Database

samalexsamalex Posts: 20
edited November 19, 2010 1:42PM in SQL Monitor Previous Versions
We have SQL Monitor tracking two databases, one being SQL 2005 and the other SQL 2000. I have Profile Traces enabled on both, but for alerts on our SQL 2000 server there's never any trace information presented. For Example I've seen a couple of Blocked Processes but the [SQL processes / Profiler trace] tab just says "No SQL processes to display."

Any suggestions? This was working great under SQL Response.

Thanks,

Sam

Comments

  • On our SQL 2000 box I just had a Long-running query alert, and as usual there was no Profiles Trace, but it did show this:
    SQL process fragment
    Command Type: AWAITING COMMAND

    I'm not sure if this'll help identify the problem.

    Thanks --

    Sam
  • It would be useful to know if trace is running on the 2000 server. This t-sql snippet should display all defined traces
    SELECT * FROM :: fn_trace_getinfo(DEFAULT)
    

    If SQL Monitor is still collecting trace, the results should include a trace specific to our product. The value of property 2 will end with "..MONITOR.trc". If our trace doesn't appear then the trace will have either not started correctly or started then terminated.

    You could maybe try switching trace off for that server (in the SQL Monitor UI > Configuration > Trace), saving settings and then switching it back on again and seeing if a trace does get started.

    Note that it might take a few minutes from starting trace for the information to actually start appearing in the details of new alerts.

    Hope this helps
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • Hi Chris,

    I sent the output of the query via PM. Looks like both SQL Response and SQL monitor are both listed. Should I uninstall or remove SQL Response?

    Thanks,

    Sam
  • Hi Sam

    Thanks for sending the results. The output confirms that SQL Monitor is running trace on the 2000 server. Can you check to see if new Blocked Process or Long Running Query alerts have started displaying SQL Processes and trace information?

    SQL Response and SQL Monitor should be able to run side-by-side so you don't have to remove Response. As far as I know, the only issue with both products running trace on the same server would be the performance overhead. For this reason we recommend using the trace options for diagnostic purposes only.

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • Chris,

    I uninstalled both SQL Response applications and now here's the output of the query:
    SELECT * FROM :: fn_trace_getinfo(DEFAULT)

    traceid property value
    1 1 2
    1 2 C:\Program Files\Microsoft SQL Server\MSSQL\LOG\20101116172411-[servername]-MONITOR
    1 3 250
    1 4 2010-11-16 11:30:11.400
    1 5 1

    Problem though is this path doesn't exist on our server. Where can I change it so it's using E: instead of C: since our C: is limited on space. I bet this is the problem.

    EDIT:: Nevermind, I was looking for this path on the server running SQL Monitor not the Database Server itself .. my mistake. I'll need to get our network guys to verify the path exists on the database server.

    Thanks --

    Sam
  • I don't think the trace would even start up if that path didn't exist on the database server but it's worth double checking.

    It looks like the trace-running mechanism is working correctly. If you're still not seeing trace on the Blocked Process alert details then it's likely to be an issue further down the line. Are there any connection issues on Configuration > Managed Servers page? If you click the 'Show Log' link for [servername] then it'll take you to a useful diagnostic page. Any data collection errors would appear here.

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • Hi Chris,

    Under Configuration > Monitored servers it shows both servers are Monitoring and Connected (green checks) and no errors are listed under the Show Log. I'll send you the Logs via PM though for the SQL 2000 server if that might show what's happening.

    And our network guys did verify the path does exist on the SQL 2000 server, so we are good there.

    Thanks -

    Sam
  • Hi Sam

    Thanks for the logs. They seem to suggest that data collection is in a healthy state.

    Luckily they're not the only logs we produce. Would it be possible for you to zip up and send the log files at this location C:\ProgramData\Red Gate\Logs\SQL Monitor 2 (or C:\Documents and Settings\All Users\Application Data\Red Gate\Logs\SQL Monitor 2 on pre-Vista OS) to chris.spencer@red-gate.com ?

    Note that if you have a distributed setup the log files will be created on the different machines - we’d be interested in the base monitor ones as well as website.

    Many thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • I got the same problem.
    I'm using SQL 2000 ent. and installed on D:
    I've enable TRACE function but SQL monitor report that TRACE is disable.
  • xexex wrote:
    I got the same problem.
    I'm using SQL 2000 ent. and installed on D:
    I've enable TRACE function but SQL monitor report that TRACE is disable.

    How is SQL Monitor reporting the trace being disabled? Is it the Deadlock trace flag disabled alert? The Configuration > Trace page can be used to start a trace on the server but it currently doesn't give any feedback as to whether the trace is running.

    I'm going to investigate running trace on SQL Server 2000 to see if I can reproduce these issues. I'll post here if I find something.

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • Chris,

    I've sent everything to you via Email. Let me know if you find anything that might help us.

    Take care --

    Sam
  • I've raised a bug report (SRP-2547) regarding SQL 2000 trace data not displaying for various alerts. This will be investigated and fixed in a future release.

    Many Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
Sign In or Register to comment.