{"id":1728,"date":"2013-03-18T11:41:11","date_gmt":"2013-03-18T15:41:11","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=1728"},"modified":"2013-03-18T13:13:04","modified_gmt":"2013-03-18T17:13:04","slug":"tracking-sql-server-database-usage","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/","title":{"rendered":"Tracking SQL Server Database Usage"},"content":{"rendered":"<p>One of the challenges with inheriting an existing set of SQL Servers and databases when you start a new job can be determining which of the databases are actively being used or not, especially on development and testing systems where there may be multiple copies of the same database that were used by different projects over time.  This can also be a challenge for multi-tenant software as a service (SaaS) providers that create a new database for each client they provide service for.  An easy way to track whether a database is being used is with Extended Events and the lock_acquired event by filtering for the shared transaction workspace (SharedXactWorkspace) lock that is acquired anytime a user connects to the database.<\/p>\n<p>To start off, we first need to look up the columns returned by the lock_acquired event, and also look up the map values associated with any of the columns so that we know the correct values to use in our event session definition.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n-- Look up the lock_acquired event columns\r\nSELECT \r\n\tname,\r\n\tcolumn_id,\r\n\ttype_name\r\nFROM sys.dm_xe_object_columns\r\nWHERE object_name = N'lock_acquired' AND\r\n\tcolumn_type = N'data';\r\n\r\n-- Look up the values for the Lock Resource Type and the Lock Owner Type\r\nSELECT \r\n\tname,\r\n\tmap_key,\r\n\tmap_value\r\nFROM sys.dm_xe_map_values\r\nWHERE name IN (N'lock_resource_type',\r\nN'lock_owner_type');\r\n<\/pre>\n<p>From this, we can get the DATABASE lock_resource_type map_key=2 and the SharedXactWorkspace lock_owner_type map_key=4.  With these values, we can define our event session to track how frequently this lock occurs by database_id, and leverage the bucketizer\/histogram target to bucket the data automatically.  Since the target name and output changed slightly in SQL Server 2012, two different version specific examples of the event session and event parsing code are presented below:<\/p>\n<h2>SQL Server 2008 Event Session<\/h2>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n-- If the Event Session Exists, drop it first\r\nIF EXISTS (SELECT 1 \r\n\t\t\tFROM sys.server_event_sessions \r\n\t\t\tWHERE name = 'SQLskills_DatabaseUsage')\r\n\tDROP EVENT SESSION &#x5B;SQLskills_DatabaseUsage] \r\n\tON SERVER;\r\n\r\n-- Create the Event Session\r\nCREATE EVENT SESSION &#x5B;SQLskills_DatabaseUsage] \r\nON SERVER \r\nADD EVENT sqlserver.lock_acquired( \r\n\tWHERE owner_type = 4 -- SharedXactWorkspace\r\n\t  AND resource_type = 2 -- Database level lock\r\n\t  AND database_id &gt; 4 -- non system database\r\n\t  AND sqlserver.is_system = 0 -- must be a user process\r\n) \r\nADD TARGET package0.asynchronous_bucketizer\r\n( SET slots = 32, -- Adjust based on number of databases in instance\r\n\t  filtering_event_name='sqlserver.lock_acquired', -- aggregate on the lock_acquired event\r\n\t  source_type=0, -- event data and not action data\r\n\t  source='database_id' -- aggregate by the database_id\r\n)\r\nWITH(MAX_DISPATCH_LATENCY =1SECONDS); -- dispatch immediately and don't wait for full buffers\r\nGO\r\n\r\n-- Start the Event Session\r\nALTER EVENT SESSION &#x5B;SQLskills_DatabaseUsage] \r\nON SERVER \r\nSTATE = START;\r\nGO\r\n\r\n-- Parse the session data to determine the databases being used.\r\nSELECT  slot.value('.\/@count', 'int') AS &#x5B;Count] ,\r\n        DB_NAME(slot.query('.\/value').value('.', 'int')) AS &#x5B;Database]\r\nFROM\r\n(\r\n\tSELECT CAST(target_data AS XML) AS target_data\r\n\tFROM sys.dm_xe_session_targets AS t\r\n    INNER JOIN sys.dm_xe_sessions AS s \r\n\t\tON t.event_session_address = s.address\r\n\tWHERE   s.name = 'SQLskills_DatabaseUsage'\r\n\t  AND t.target_name = 'asynchronous_bucketizer') AS tgt(target_data)\r\nCROSS APPLY target_data.nodes('\/BucketizerTarget\/Slot') AS bucket(slot)\r\nORDER BY slot.value('.\/@count', 'int') DESC\r\n\r\nGO\r\n<\/pre>\n<h2>SQL Server 2012 Event Session<\/h2>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n-- If the Event Session Exists, drop it first\r\nIF EXISTS (SELECT 1 \r\n\t\t\tFROM sys.server_event_sessions \r\n\t\t\tWHERE name = 'SQLskills_DatabaseUsage')\r\n\tDROP EVENT SESSION &#x5B;SQLskills_DatabaseUsage] \r\n\tON SERVER;\r\n\r\n-- Create the Event Session\r\nCREATE EVENT SESSION &#x5B;SQLskills_DatabaseUsage] \r\nON SERVER \r\nADD EVENT sqlserver.lock_acquired( \r\n\tWHERE owner_type = 4 -- SharedXactWorkspace\r\n\t  AND resource_type = 2 -- Database level lock\r\n\t  AND database_id &gt; 4 -- non system database\r\n\t  AND sqlserver.is_system = 0 -- must be a user process\r\n) \r\nADD TARGET package0.histogram\r\n( SET slots = 32, -- Adjust based on number of databases in instance\r\n\t  filtering_event_name='sqlserver.lock_acquired', -- aggregate on the lock_acquired event\r\n\t  source_type=0, -- event data and not action data\r\n\t  source='database_id' -- aggregate by the database_id\r\n); -- dispatch immediately and don't wait for full buffers\r\nGO\r\n\r\n-- Start the Event Session\r\nALTER EVENT SESSION &#x5B;SQLskills_DatabaseUsage] \r\nON SERVER \r\nSTATE = START;\r\nGO\r\n\r\n-- Parse the session data to determine the databases being used.\r\nSELECT  slot.value('.\/@count', 'int') AS &#x5B;Count] ,\r\n        DB_NAME(slot.query('.\/value').value('.', 'int')) AS &#x5B;Database]\r\nFROM\r\n(\r\n\tSELECT CAST(target_data AS XML) AS target_data\r\n\tFROM sys.dm_xe_session_targets AS t\r\n    INNER JOIN sys.dm_xe_sessions AS s \r\n\t\tON t.event_session_address = s.address\r\n\tWHERE   s.name = 'SQLskills_DatabaseUsage'\r\n\t  AND t.target_name = 'histogram') AS tgt(target_data)\r\nCROSS APPLY target_data.nodes('\/HistogramTarget\/Slot') AS bucket(slot)\r\nORDER BY slot.value('.\/@count', 'int') DESC\r\n\r\nGO\r\n<\/pre>\n<p>One thing to keep in mind with this event session is that while a end user might not actually use a database, other tasks like maintenance, backups, CHECKDB, or even using intellisense in SQL Server Management Studio will.  It is therefore, expected that databases not being used by end users would still show up inside of the histogram target, but the frequency of usage would be significantly lower than the databases that are actively being used by end users or applications.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the challenges with inheriting an existing set of SQL Servers and databases when you start a new job can be determining which of the databases are actively being used or not, especially on development and testing systems where there may be multiple copies of the same database that were used by different projects [&hellip;]<\/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-1728","post","type-post","status-publish","format-standard","hentry","category-database-administration","category-extended-events","category-sql-server-2008","category-sql-server-2012"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Tracking SQL Server Database Usage - Jonathan Kehayias<\/title>\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\/tracking-sql-server-database-usage\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Tracking SQL Server Database Usage - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"One of the challenges with inheriting an existing set of SQL Servers and databases when you start a new job can be determining which of the databases are actively being used or not, especially on development and testing systems where there may be multiple copies of the same database that were used by different projects [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2013-03-18T15:41:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-03-18T17:13:04+00:00\" \/>\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=\"5 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\\\/tracking-sql-server-database-usage\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/tracking-sql-server-database-usage\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Tracking SQL Server Database Usage\",\"datePublished\":\"2013-03-18T15:41:11+00:00\",\"dateModified\":\"2013-03-18T17:13:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/tracking-sql-server-database-usage\\\/\"},\"wordCount\":903,\"commentCount\":7,\"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\\\/tracking-sql-server-database-usage\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/tracking-sql-server-database-usage\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/tracking-sql-server-database-usage\\\/\",\"name\":\"Tracking SQL Server Database Usage - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2013-03-18T15:41:11+00:00\",\"dateModified\":\"2013-03-18T17:13:04+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/tracking-sql-server-database-usage\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/tracking-sql-server-database-usage\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/tracking-sql-server-database-usage\\\/#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\":\"Tracking SQL Server Database Usage\"}]},{\"@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":"Tracking SQL Server Database Usage - Jonathan Kehayias","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\/tracking-sql-server-database-usage\/","og_locale":"en_US","og_type":"article","og_title":"Tracking SQL Server Database Usage - Jonathan Kehayias","og_description":"One of the challenges with inheriting an existing set of SQL Servers and databases when you start a new job can be determining which of the databases are actively being used or not, especially on development and testing systems where there may be multiple copies of the same database that were used by different projects [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/","og_site_name":"Jonathan Kehayias","article_published_time":"2013-03-18T15:41:11+00:00","article_modified_time":"2013-03-18T17:13:04+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Tracking SQL Server Database Usage","datePublished":"2013-03-18T15:41:11+00:00","dateModified":"2013-03-18T17:13:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/"},"wordCount":903,"commentCount":7,"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\/tracking-sql-server-database-usage\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/","name":"Tracking SQL Server Database Usage - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2013-03-18T15:41:11+00:00","dateModified":"2013-03-18T17:13:04+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-sql-server-database-usage\/#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":"Tracking SQL Server Database Usage"}]},{"@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\/1728","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=1728"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/1728\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=1728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=1728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=1728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}