SQL process fragment just display "SELECT"

xexexxexex Posts: 56
edited November 22, 2010 4:40AM in SQL Monitor Previous Versions
I've received a Long-running query alert, when I open the alert detail in WebUI, it show:

*****************************

Process ID: 369
Process name: Process_Name
Host: Copmputer_Name
User: User_Name
Process login time: 15 Nov 2010 11:49 AM


SQL process fragment
Command Type: SELECT

*****************************

The report is too simple and I don't know what's wrong in our SQL statement, how can I obtain more statement information in this event?

Comments

  • Hi,

    Sometimes this is just the first word or entire block of SQL. This information is taken as-is from SQL Server. However, if you enable trace (small performance hit on the server you're monitoring, so do this only when debugging an active issue) you can view full SQL transcripts. In the alert details, open the SQL Process / profile trace tab and click on a process to display the captured trace.

    Hope this helps,
    Robin Anderson
    Development
    Red-Gate Software
  • I've enable "Trace" function (Configuration > Trace).

    However, when I click "SQL processes / Profiler trace", it show:
    No SQL statements to display.
    No statements captured for the selected process or trace was not enabled for this SQL Server instance at the time the alert was raised.

    The statement can be shown in V1.3 (Alert Details => Performance snapshot)
  • Hi,

    Activating trace only affects alerts generated from that point onwards; old alerts can't be updated as trace is not retroactive. Additionally, there is a small delay (0~15 seconds) from when the alert is first generated to when trace data is attached.

    Are you finding that new alerts are not being tagged with trace data?

    Regards,
    Robin Anderson
    Development
    Red-Gate Software
  • I've enable "Trace" after I setup SQL Monitor, it should be enabled before event started.

    P.S. How can I upload screenshot on this forum?
  • To upload an image, use imageshack.us and paste the link into your reply like this:

    imageUrl.jpg
    Robin Anderson
    Development
    Red-Gate Software
  • Compare with SQL Response, SQL Monitor only show the TYPE of statement
    alertdetailssqlresponse.jpg

    After I enable TRACE in SQL Monitor, it doesn't show the statement.
    alertdetailssqlresponse.jpg
  • Hi xexex,

    Thanks for the feedback. I don't know why the trace data isn't being appended to the alert - I'll move some test effort on to this area. As for the truncated fragment, I'll put the relevant developer on the issue and hopefully this will be improved for 2.1.

    Thanks,
    Robin Anderson
    Development
    Red-Gate Software
  • Thanks RBA, I've found that one of SQL monitor user may got the same problem with me
    http://www.red-gate.com/MessageBoard/vi ... 4099#44099
    I'm monitoring a SQL Server 2000 too.
    We can't upgrade to SQL monitor 2 before we fix this issue.
  • Here is more information for you,
    The SQL process fragment was not correct in SQL monitor, compare with SQL response
    alertdetailssqlresponse.jpg

    For the WebUI, only a part ofstatement can be displayed in Profiler Trace.
    alertdetailslocallongru.jpg
  • Hi,

    Thanks for the further info. The width behaviour of the viewer has been changed and SQL 2000 related trace issues are presently being improved. Both fixes will be in 2.1 when it goes out (TBA).

    Regards,
    Robin Anderson
    Development
    Red-Gate Software
Sign In or Register to comment.