{"id":1852,"date":"2013-09-25T19:43:27","date_gmt":"2013-09-25T23:43:27","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=1852"},"modified":"2017-04-13T12:55:45","modified_gmt":"2017-04-13T16:55:45","slug":"logging-extended-events-changes","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/","title":{"rendered":"Logging Extended Events changes to the ERRORLOG"},"content":{"rendered":"

A question came up in class today about the difference between SQL Trace and Extended Events for logging information to the ERRORLOG file. Joe and I have both written about the observer overhead of Trace and Extended Events in the past (Observer Overhead and Wait Type Symptoms<\/a> and Measuring \u201cObserver Overhead\u201d of SQL Trace vs. Extended Events<\/a>), and one of the things we teach is to check whether a trace or event session may be running and impacting performance as a part of general troubleshooting performance problems in SQL Server. Anytime a user starts or stops a SQL Trace, information is logged in the ERRORLOG.<\/p>\n

SQL Trace ID 2 was started by login “SQL2K8R2-IE2\\Jonathan Kehayias”.
\nSQL Trace stopped. Trace ID = ‘2’. Login Name = ‘SQL2K8R2-IE2\\Jonathan Kehayias’.<\/p>\n

\"SQL
SQL Trace messages<\/figcaption><\/figure>\n

However, for Extended Events nothing is logged when a user starts or stops an event session on the server. The question in class today was whether it was possible to make Extended Events log entries like SQL Trace and the answer is yes, with a DDL Trigger.<\/p>\n

\r\nCREATE TRIGGER XEventLogging\r\nON ALL SERVER \r\nFOR DDL_EVENT_SESSION_EVENTS\r\nAS\r\nBEGIN\r\n    SET NOCOUNT ON;\r\n    DECLARE @EventData XML = EVENTDATA();\r\n    DECLARE @EventType NVARCHAR(256) = @EventData.value('(EVENT_INSTANCE\/EventType)[1]', 'NVARCHAR(256)')\r\n    DECLARE @SessionName NVARCHAR(256) = @EventData.value('(EVENT_INSTANCE\/ObjectName)[1]', 'NVARCHAR(256)')\r\n    DECLARE @LoginName NVARCHAR(256) = @EventData.value('(EVENT_INSTANCE\/LoginName)[1]', 'NVARCHAR(256)')\r\n\tDECLARE @Command NVARCHAR(MAX) = @EventData.value('(EVENT_INSTANCE\/TSQLCommand\/CommandText)[1]', 'NVARCHAR(MAX)');\r\n\tDECLARE @msg NVARCHAR(440) = CASE \r\n\t\tWHEN @EventType = 'CREATE_EVENT_SESSION'\r\n\t\t\tTHEN 'Extended Event session created. Session Name = ''%s''. Login Name = ''%s''.'\r\n\t\tWHEN @EventType = 'ALTER_EVENT_SESSION' AND LOWER(@Command) LIKE LOWER('%STATE%=%START%')\r\n\t\t\tTHEN 'Extended Event session started. Session Name = ''%s''. Login Name = ''%s''.'\r\n\t\tWHEN @EventType = 'ALTER_EVENT_SESSION' AND LOWER(@Command) LIKE LOWER('%STATE%=%STOP%')\r\n\t\t\tTHEN 'Extended Event session stopped. Session Name = ''%s''. Login Name = ''%s''.'\r\n\t\tWHEN @EventType = 'DROP_EVENT_SESSION'\r\n\t\t\tTHEN 'Extended Event session dropped. Session Name = ''%s''. Login Name = ''%s''.'\r\n\t\tEND\r\n\t\r\n\tRAISERROR(@msg, 10, 1, @SessionName, @LoginName) WITH LOG;\r\nEND\r\nGO\r\n<\/pre>\n
Now anytime an event session is created, started, stopped, or dropped, information will be logged into  the ERRORLOG file.<\/p>\n
\"New
New Extended Events messages<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"
A question came up in class today about the difference between SQL Trace and Extended Events for logging information to the ERRORLOG file. Joe and I have both written about the observer overhead of Trace and Extended Events in the past (Observer Overhead and Wait Type Symptoms and Measuring \u201cObserver Overhead\u201d of SQL Trace vs. […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,23,38,39],"tags":[],"class_list":["post-1852","post","type-post","status-publish","format-standard","hentry","category-database-administration","category-extended-events","category-sql-server-2008","category-sql-server-2012"],"yoast_head":"\nLogging Extended Events changes to the ERRORLOG - Jonathan Kehayias<\/title>\n<meta name=\"description\" content=\"Demonstrates how to log Extended Events changes to the ERRORLOG when a user starts or stops an event session in SQL Server for troubleshooting purposes.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Logging Extended Events changes to the ERRORLOG - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"Demonstrates how to log Extended Events changes to the ERRORLOG when a user starts or stops an event session in SQL Server for troubleshooting purposes.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2013-09-25T23:43:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:55:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/09\/tracemessages.png\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Logging Extended Events changes to the ERRORLOG\",\"datePublished\":\"2013-09-25T23:43:27+00:00\",\"dateModified\":\"2017-04-13T16:55:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/\"},\"wordCount\":379,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/09\\\/tracemessages.png\",\"articleSection\":[\"Database Administration\",\"Extended Events\",\"SQL Server 2008\",\"SQL Server 2012\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/\",\"name\":\"Logging Extended Events changes to the ERRORLOG - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/09\\\/tracemessages.png\",\"datePublished\":\"2013-09-25T23:43:27+00:00\",\"dateModified\":\"2017-04-13T16:55:45+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"description\":\"Demonstrates how to log Extended Events changes to the ERRORLOG when a user starts or stops an event session in SQL Server for troubleshooting purposes.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/09\\\/TraceMessages.png\",\"contentUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2013\\\/09\\\/TraceMessages.png\",\"width\":920,\"height\":81,\"caption\":\"SQL Trace messages\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/logging-extended-events-changes\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database Administration\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/database-administration\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Logging Extended Events changes to the ERRORLOG\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Logging Extended Events changes to the ERRORLOG - Jonathan Kehayias","description":"Demonstrates how to log Extended Events changes to the ERRORLOG when a user starts or stops an event session in SQL Server for troubleshooting purposes.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/","og_locale":"en_US","og_type":"article","og_title":"Logging Extended Events changes to the ERRORLOG - Jonathan Kehayias","og_description":"Demonstrates how to log Extended Events changes to the ERRORLOG when a user starts or stops an event session in SQL Server for troubleshooting purposes.","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/","og_site_name":"Jonathan Kehayias","article_published_time":"2013-09-25T23:43:27+00:00","article_modified_time":"2017-04-13T16:55:45+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/09\/tracemessages.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Logging Extended Events changes to the ERRORLOG","datePublished":"2013-09-25T23:43:27+00:00","dateModified":"2017-04-13T16:55:45+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/"},"wordCount":379,"commentCount":0,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/09\/tracemessages.png","articleSection":["Database Administration","Extended Events","SQL Server 2008","SQL Server 2012"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/","name":"Logging Extended Events changes to the ERRORLOG - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/09\/tracemessages.png","datePublished":"2013-09-25T23:43:27+00:00","dateModified":"2017-04-13T16:55:45+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"description":"Demonstrates how to log Extended Events changes to the ERRORLOG when a user starts or stops an event session in SQL Server for troubleshooting purposes.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/09\/TraceMessages.png","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2013\/09\/TraceMessages.png","width":920,"height":81,"caption":"SQL Trace messages"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/logging-extended-events-changes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Database Administration","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/database-administration\/"},{"@type":"ListItem","position":3,"name":"Logging Extended Events changes to the ERRORLOG"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1852","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=1852"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1852\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=1852"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=1852"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=1852"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}