12

I am running SQL Server 2014 Developer Edition on my PC. I am trying to view the data in the system_health session. In SSMS, I have connected to the database, expanded the server / Management / Extended Events / Sessions. I see AlwaysON_health (stopped) and system_health (running).

When I right-click on the system_health session, I get the following error:

The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage) The Extended Events session named "system_health" could not be found. Make sure the session exists and is started. (Microsoft SQL Server, Error: 25728)

I expand system_health and see the targets package0.event_file and package0.ring_buffer. If I right-click either target and choose "View Target Data", I get this error:

The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage) Cannot view the function 'fn_MSXe_read_event_stream', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

The function does exist. I can run it:

select * from fn_MSXe_read_event_stream('system_health', 0);

When I do, I get this error:

Msg 25728, Level 16, State 10, Line 6
The Extended Events session named "system_health" could not be found. Make  sure the session exists and is started.

I know the system_health session is there. I see in in the list of sessions:

select * from sys.dm_xe_sessions

address name       name
------------------ ------------- 
0x00000001FF6510C1 system_health

I have tried this with my own custom event sessions. I can't watch the live data on them, either.

I can query the system_health ring buffer target data from sys.dm_xe_session_targets.

Why can't I watch live data for any extended events session?

(Note that there is a Microsoft feedback item for this issue.)

8
  • 1
    Can you test this on another instance (using the same copy of SSMS), or on this instance from a different copy of SSMS (e.g. from another machine)? Did you try with both windows authentication and sa? Sounds like a problem with the instance, but always good to rule out combinations. Aug 20, 2015 at 19:00
  • I found the answer. My login's default database was set to the one I usually use instead of master. Setting my default database back to master resolved the error. Aug 20, 2015 at 19:06
  • That seems odd, if you right-click the session in Object Explorer, SQL Server should know where to find it, regardless of your default database. Aug 20, 2015 at 19:44
  • @AaronBertrand Yes, I agree! SSMS should know how to connect to the event session regardless of my default database. I linked a Microsoft Connect item in my question, should anyone want to vote or comment on it. Aug 21, 2015 at 14:28
  • What login are you using? Windows auth or SQL auth? What role(s) are they in? Aug 21, 2015 at 14:48

1 Answer 1

17

I found the problem. My login had a different default database than the master database. When I changed my default database to master, the error went away, and I was able to Watch Live Data on the extended event sessions.

To change the default database, in SSMS, I expanded the server, Security, Logins. I right-clicked on my user credentials. On the Login Properties page for my user, I changed "Default database" to master. The error went away when I closed and reopened the connection in the SSMS Object Explorer.

EDIT 2018-12-11

On the feedback link for this issue, Karl Fasick posted a likely solution:

Object Explorer isn't connected to the master database... To fix - disconnect Object Explorer - Reconnect, but go to Connection Properties and explicitly choose master.

3
  • 1
    There's a Connect item for this EXACT bug. MS closed it as 'could not reproduce': connect.microsoft.com/SQLServer/feedback/details/893993/… Please vote that up if you got here the same way I did (googling on the error - in my case against a 2016 instance). Jan 17, 2017 at 21:11
  • 1
  • Can confirm that disconnecting and reconnecting, with master set as the database under Options > Connection Properties in the connection dialog, was sufficient for me to view the event_file data. This was with a login that does not have master set as the default database. So no need to change the default database, just ensure you're connecting to master when you first log in.
    – Simon Elms
    Mar 5 at 3:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.