Event Files on Linux

TSQL Tuesday

The second Tuesday of the month is upon us once again. That means it is time for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

This month, just about every SQL Server Data professional is being asked to get out of their comfort zone and talk about Linux (there are some out there that are very comfortable with Linux, but the vast majority are not ).

This is a topic that is near and dear to the heart of our organizer Tracy Boggiano (b | t). In Tracy’s invite, she says “While I know it takes a while to adopt new technologies; I was wondering what it would take for people to adopt SQL on Linux.  Alternating I’m offering up for you to blog about what everyone should know when working with SQL on Linux or anything else related to SQL running on Linux.”

That pretty much leaves the door wide open, right?

Event Files

For the most part, things work the way you might expect them to work in windows – except it is on Linux. Sure some things are different, but SQL Server itself, is largely the same. That similarity, for the most part, boils all the way down into Extended Events (XEvents) as well. There is one significant divergence, however. And that is when it comes to specifying your event_file target for a new session. Let’s dive into that just a bit.

Let’s take a common setup for an XEvent session.

USE master;
GO
-- Create the Event Session
/* this method will fail. The session will create but the session cannot be started due to a non-existent directory 
However, if the directory exists in linux format, and permissions are in place, the session will start.
*/IF EXISTS
(
SELECT
  *
FROM  sys.server_event_sessions
WHERE name = 'PREEMPTIVE_OS_PIPEOPS'
)
DROP EVENT SESSION PREEMPTIVE_OS_PIPEOPS ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION [PREEMPTIVE_OS_PIPEOPS]
ON SERVER
ADD EVENT sqlos.wait_info_external(
 WHERE ([wait_type] = 'PREEMPTIVE_OS_PIPEOPS')
)
ADD TARGET package0.event_file
(SET filename = N'C:DatabaseXEPREEMPTIVE_OS_PIPEOPS.xel')
WITH
(
MAX_MEMORY = 4096KB
  , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 5 SECONDS
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION Preemptive_os_pipeops ON SERVER
STATE = START;
GO

This will fail before the query really even gets out of the gate. Why? The proc xp_create_subdir cannot create the directory because it requires elevated permissions. The fix for that is easy enough – grant permissions to write to the Database directory after creating it while in sudo mode. I will get to that in just a bit. Let’s see what the errors would look like for now.

Msg 22048, Level 16, State 1, Line 15
xp_create_subdir() returned error 5, ‘Access is denied.’
Msg 25602, Level 17, State 23, Line 36
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 5: ‘Access is denied.
‘ while creating the file ‘C:DatabaseXEPREEMPTIVE_OS_PIPEOPS_0_132072025269680000.xel’.

Let’s resolve the folder issue. I will create the Database folder (from a console session on the server), and then I will take ownership of that folder.

$ sudo mkdir /Database/
$ sudo chown -R $User:$User /Database/
$ sudo chgrp mssql /Database/
$ chmod g=rwx /Database/

From there, it is also advisable to grant permissions to this folder to the SQL group via the chgrp command. Once done, re-running the entire session creation will magically work – including that windows based create subdir proc.

Alternative #2

Maybe we just want to do things via the default method. If so, we can do this and it just works.

USE master;
GO
-- Create the Event Session
/* this method succeeds but uses the default log directory */IF EXISTS
(
SELECT
  *
FROM  sys.server_event_sessions
WHERE name = 'PREEMPTIVE_OS_PIPEOPS2'
)
DROP EVENT SESSION PREEMPTIVE_OS_PIPEOPS2 ON SERVER;
GO
CREATE EVENT SESSION [PREEMPTIVE_OS_PIPEOPS2]
ON SERVER
ADD EVENT sqlos.wait_info_external(
 WHERE ([wait_type] = 'PREEMPTIVE_OS_PIPEOPS')
)
ADD TARGET package0.event_file
(SET filename = N'PREEMPTIVE_OS_PIPEOPS.xel')
WITH
(
MAX_MEMORY = 4096KB
  , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 5 SECONDS
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = ON
);
GO

Alternative #3

And of course, there is always this option. Unfortunately, this means keeping multiple session scripts in source control in order to maintain the differences between Windows and Linux as illustrated in these two examples.

USE master;
GO
-- Create the Event Session
/* this method succeeds but requires the permissions to be modified just the same as in the first example */IF EXISTS
(
SELECT
  *
FROM  sys.server_event_sessions
WHERE name = 'PREEMPTIVE_OS_PIPEOPS3'
)
DROP EVENT SESSION PREEMPTIVE_OS_PIPEOPS3 ON SERVER;
GO
CREATE EVENT SESSION [PREEMPTIVE_OS_PIPEOPS3]
ON SERVER
ADD EVENT sqlos.wait_info_external(
 WHERE ([wait_type] = 'PREEMPTIVE_OS_PIPEOPS')
)
ADD TARGET package0.event_file
(SET filename = N'/Database/XE/PREEMPTIVE_OS_PIPEOPS3.xel') --this is correct and matches the same pattern that ssms uses when using linux filesystem
WITH
(
MAX_MEMORY = 4096KB
  , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 5 SECONDS
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = ON
);
GO

With all three sessions now on the server, I can query my target paths to confirm the correct paths have been used.

SELECT ses.name AS SessionName, CASE WHEN ISNULL(xs.name,'') = ''
THEN 'No' ELSE 'Yes' END AS ISStarted
, xs.total_bytes_generated
,REPLACE(CONVERT(NVARCHAR(128),sesf.value),'.xel','*.xel') AS targetvalue, ses.event_session_id
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
LEFT OUTER JOIN sys.dm_xe_sessions xs
ON xs.name = ses.name
WHERE sesf.name = 'filename'
AND ses.name LIKE 'PREEMPTIVE_OS_PIPEOPS%';

This produces the following results for me.

Perfect, everything looks to be working and properly configured. Well, except for that session that is using the default directory – gag.

Wrapping it Up

Extended Events is a powerful tool with plenty of ease of use and flexibility. This flexibility allows the DBA to capably monitor the server for any issue be it small or large. This article demonstrated how to create an Event Session on SQL Server that is running on Linux. If you are in need of a little tune-up for your XE skiils, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.