\nMsg 3702, Level 16, State 3, Line 1
\n
\nCannot drop database \"AdventureWorks2008R2\" because it is currently in use.\n<\/div>\n<\/blockquote>\n
\nThis error is informing you that the database can not be dropped right now, because someone is still using it. An error like this will show up for any attempt to execute an action that requires exclusive access to the database.\n<\/p>\n
To proceed with the requested action you have several options. You could for example force the database offline:<\/p>\n\n[sql]\nALTER DATABASE AdventureWorks2008R2 SET OFFLINE WITH ROLLBACK IMMEDIATE;
\n[\/sql]\n
\nThis solution has however several implications that make it unattractive to use. It also does not help you if you really just want to know who is connected to the database right now.\n<\/p>\n
\nTo just get to the information you could run this statement:\n<\/p>\n[sql]\nSELECT * FROM master..sysprocesses WHERE dbid = DB_ID('AdventureWorks2008R2');
\n[\/sql]\n
\nThis was the common recommendation to go with in SQL Server 2000. However, while it still works (even in SQL Server Denali CTP3) the sysprocesses<\/code> system table has been deprecated in newer versions of SQL Server.\n<\/p>\n\nInstead you could go with the system procedure sp_who<\/code>. It is not deprecated (as of Denali CTP 3), but it does not allow you to filter on a specific database, so if you have a lot of concurrent connections it gets quite cumbersome to use. There is also sp_who2<\/code>, but it has the same shortcoming and it is also undocumented.\n<\/p>\n\nSo lets look at the DMVs:\n<\/p>\n[sql]\nSELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID('AdventureWorks2008R2')
\n[\/sql]\n
\nThis query gives you very similar information to the first one that queried from sysprocesses<\/code>. It's biggest problem is that the database_id<\/code> column was introduced in Denali, so you most likely will not be able to use it for a while.\n<\/p>\n\nThere is a database_id<\/code> column in the sys.dm_exec_requests<\/code> DMV, but it returns rows only for processes that currently have an active request (statement) running. That means idle connections like an open SSMS query window are not included.\n<\/p>\n\nAll these methods, besides of their individual problems, have one big shortcoming in common: The information they provide is incomplete.\n<\/p>\n
\nTo drop a database, the executing connection needs to acquire an exclusive lock on the database. This request, like any other lock request will be blocked if there is an incompatible lock held by another connection.\n<\/p>\n
\nEvery connection that does anything in or with a database first requests a lock on that database. Connections that retrieve or alter data in the database will acquire a shared lock on the database. Connections that, like a database drop, require exclusive access to the database require an exclusive lock on it. An exclusive lock — as the name suggests — is not compatible with any other lock, so the requesting process is blocked if any other lock on that database is currently held. SQL Server automatically sets the lock timeout for these statements to a fairly short value, so you will not be blocked forever like you would be in a normal blocking situation with the default lock timeout (infinite).\n<\/p>\n
\nThis means that a single connection, for example by doing a cross database query, can hold a lock on more than one database:\n<\/p>\n[sql]\nUSE tempdb;
\nBEGIN TRAN
\nSELECT TOP(1)* FROM AdventureWorks2008R2.Person.Person;
\n[\/sql]\n
\nThis connection will hold a lock on AdventureWorks2008R2<\/code> while the dbid<\/code> column in sysprocesses<\/code> will point to tempdb<\/code>.
\nSo, the dbid<\/code> column in sysprocesses<\/code> as well as the soon to come database_id<\/code> column in sys.dm_exec_sessions<\/code> provide the information in which database the next statement will be executed in. The information we are really looking for, we need to get from somewhere else.\n<\/p>\n\nThis information is provided by the sys.dm_tran_locks<\/code> DMV:\n<\/p>\n[sql]\nSELECT request_session_id
\nFROM sys.dm_tran_locks
\nWHERE resource_type = 'DATABASE'
\n AND resource_database_id = DB_ID('AdventureWorks2008R2') ;
\n[\/sql]\n\nThis query returns the session id for each session that is currently holding a lock on the AdventureWorks2008R2<\/code> database which is exactly the answer to our quest.\n<\/p>\n\nIf you would like to see a little more information about the established connections, you can use the dbo.DatabaseConnections<\/code> function:\n<\/p>\n[sql]\nIF OBJECT_ID('dbo.DatabaseConnections') IS NOT NULL
\n DROP FUNCTION dbo.DatabaseConnections ;
\nGO
\nCREATE FUNCTION dbo.DatabaseConnections (@DbId INT = NULL)
\nRETURNS TABLE
\n AS
\nRETURN
\n SELECT d.database_id,
\n d.name AS database_name,
\n c.session_id,
\n l.request_mode + '(' + l.request_status + ')' AS 'DbLock',
\n s.status,
\n c.auth_scheme,
\n s.login_name,
\n c.client_net_address,
\n s.host_name,
\n s.program_name,
\n s.host_process_id,
\n s.client_interface_name,
\n c.connect_time,
\n s.login_time,
\n s.last_request_start_time,
\n CASE WHEN r.session_id IS NULL THEN s.last_request_end_time
\n END AS last_request_end_time,
\n c.last_read,
\n c.last_write,
\n at.transaction_begin_time,
\n st.is_local AS is_local_transaction,
\n r.percent_complete,
\n CAST(t.text AS NVARCHAR(MAX)) text,
\n s.is_user_process,
\n s.transaction_isolation_level,
\n s.lock_timeout,
\n r.statement_start_offset,
\n r.statement_end_offset,
\n r.blocking_session_id,
\n r.wait_type,
\n r.wait_time,
\n r.wait_resource,
\n c.net_transport,
\n c.net_packet_size,
\n s.total_elapsed_time,
\n s.total_scheduled_time,
\n s.cpu_time,
\n s.reads,
\n s.writes,
\n s.logical_reads,
\n c.num_reads,
\n c.num_writes,
\n s.memory_usage
\n FROM sys.dm_exec_connections c
\n JOIN sys.dm_exec_sessions s
\n ON c.session_id = s.session_id
\n LEFT JOIN sys.dm_exec_requests r
\n ON c.session_id = r.session_id
\n OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle,
\n c.most_recent_sql_handle)) t
\n JOIN sys.dm_tran_locks l
\n ON l.request_session_id = c.session_id
\n AND l.resource_type = 'DATABASE'
\n AND l.resource_subtype = ''
\n JOIN sys.databases d
\n ON l.resource_database_id = d.database_id
\n LEFT JOIN sys.dm_tran_session_transactions st
\n JOIN sys.dm_tran_active_transactions at
\n ON at.transaction_id = st.transaction_id
\n ON st.session_id = c.session_id
\n WHERE d.database_id = COALESCE(@DbId, d.database_id) ;
\nGO
\n[\/sql]\n\ndbo.DatabaseConnections<\/code> is a table valued function that requires the database id of the database of interest to be passed in. If you pass in a NULL<\/code> it will return information about all databases.
\nFor each connection, it provides information about who is connected from where using which software and what type of lock is held on the given database. It also gives information about when the connection was established and the last time for a handful of actions like sending a request or executing a data change. It shows if a transaction is open on the connection and if so, when it was started. It also returns the statement that is (or was) executing together with a little bit of statistical information.\n<\/p>\n\nOne final note: You cannot use this function to see who is using master<\/code> or tempdb<\/code>. Because both databases are essential to SQL Servers functioning, SQL Server restricts what you can do to them. Particularly, you cannot drop or restore them. Therefore it is not necessary for SQL Server to track if there is a connection the still needs these databases around. To save the overhead of locking, connections do not need to acquire a database lock on either of the two databases. That in turn means, that the above function will not return any information about connections that are using only one of these two databases.<\/p>\n","protected":false},"excerpt":{"rendered":"Sometimes you need to know who is currently connected to specific database. While certainly not the only reason, the need for this information arises most often after receiving an error message like this one: Msg 3702, Level 16, State 3, [more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,14],"tags":[],"class_list":["post-380","post","type-post","status-publish","format-standard","hentry","category-general","category-sql-server-internals"],"acf":[],"yoast_head":"\nConnections to a Database<\/title>\n<meta name=\"description\" content=\"This article explains how to find out how many connections are established to a given database. It provides information on how to get to that information and how to not do it.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Connections to a Database\" \/>\n<meta property=\"og:description\" content=\"This article explains how to find out how many connections are established to a given database. It provides information on how to get to that information and how to not do it.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2011-09-06T00:00:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T19:02:59+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/380\\\/connections-to-a-database\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/380\\\/connections-to-a-database\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Connections to a Database\",\"datePublished\":\"2011-09-06T00:00:21+00:00\",\"dateModified\":\"2014-11-13T19:02:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/380\\\/connections-to-a-database\\\/\"},\"wordCount\":1259,\"commentCount\":0,\"articleSection\":[\"General\",\"SQL Server Internals\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/380\\\/connections-to-a-database\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/380\\\/connections-to-a-database\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/380\\\/connections-to-a-database\\\/\",\"name\":\"Connections to a Database\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2011-09-06T00:00:21+00:00\",\"dateModified\":\"2014-11-13T19:02:59+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"This article explains how to find out how many connections are established to a given database. It provides information on how to get to that information and how to not do it.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/380\\\/connections-to-a-database\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/380\\\/connections-to-a-database\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/380\\\/connections-to-a-database\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Connections to a Database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Connections to a Database","description":"This article explains how to find out how many connections are established to a given database. It provides information on how to get to that information and how to not do it.","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:\/\/sqlity.net\/en\/380\/connections-to-a-database\/","og_locale":"en_US","og_type":"article","og_title":"Connections to a Database","og_description":"This article explains how to find out how many connections are established to a given database. It provides information on how to get to that information and how to not do it.","og_url":"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2011-09-06T00:00:21+00:00","article_modified_time":"2014-11-13T19:02:59+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Connections to a Database","datePublished":"2011-09-06T00:00:21+00:00","dateModified":"2014-11-13T19:02:59+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/"},"wordCount":1259,"commentCount":0,"articleSection":["General","SQL Server Internals"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/","url":"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/","name":"Connections to a Database","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2011-09-06T00:00:21+00:00","dateModified":"2014-11-13T19:02:59+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"This article explains how to find out how many connections are established to a given database. It provides information on how to get to that information and how to not do it.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/380\/connections-to-a-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Connections to a Database"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-68","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/380","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=380"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/380\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=380"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=380"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=380"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}