Performance Tuning Using Extended Events: Part 2

  • bdavey - Wednesday, April 26, 2017 9:28 PM

  • I set this up and I think it's great. Some good information we are already using to identify issues. However, I am getting some errors. I have listed them below.

    exec [dbo].[event_GetEventData] @beginDateTime = '2019-12-16', @endDateTime = '2019-12-17', @eventname = 'DurationGT25Seconds';

    Msg 25718, Level 16, State 3, Procedure event_GetEventData, Line 496 [Batch Start Line 6]

    The log file name "(null)" is invalid. Verify that the file exists and that the SQL Server service account has access to it.

    The statement has been terminated.

    exec [dbo].[event_GetEventData] @beginDateTime = '2019-12-16', @endDateTime = '2019-12-17', @eventname ='Errors';

    Msg 25718, Level 16, State 3, Procedure event_GetEventData, Line 571 [Batch Start Line 8]

    The log file name "(null)" is invalid. Verify that the file exists and that the SQL Server service account has access to it.

    The statement has been terminated.

    Msg 208, Level 16, State 1, Procedure event_GetEventData, Line 597 [Batch Start Line 8]

    Invalid object name 'Engagement20.dbo.fn_GetIntTableFromList'.

     

  • There seems to be a missing variable declaration and set. In the section that starts:

     WHERE s.name = CASE WHEN @eventName = 'BlockingReport' THEN 'Blocking' ELSE @eventName END
    AND t.target_name = N'event_file') AS tab

    ...there is no @eventName declared or set in the preceding code.

    Also, do the values need to be changed depending on which of the 4 reports (built in Part 1)? Like, if I want to look at deadlocks, do I change the @eventName='deadlocks'?

    Thanks!

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA

    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • I did provide the @eventName variable. What is strange too is the dates. If I want to look at what just happened I have to put todays date as the BeginDate and tomorrows date as the endDate. It would be great to focus on a specific time too.

  • Replace Engagement20.dbo.fn_GetIntTableFromList with string_split. I thought I removed that reference years ago but some how it's still there.

  • Comments posted to this topic are about the item Performance Tuning Using Extended Events: Part 2

  • After recreating the sessions, I get this error when I try extract the report. Can you help please

    Msg 9420, Level 16, State 1, Procedure dbo.event_GetEventData, Line 496 [Batch Start Line 0]

    XML parsing: line 10, character 221, illegal xml character

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply