{"id":8631,"date":"2016-12-30T06:24:08","date_gmt":"2016-12-30T06:24:08","guid":{"rendered":"http:\/\/www.top-password.com\/blog\/?p=8631"},"modified":"2016-12-30T06:27:01","modified_gmt":"2016-12-30T06:27:01","slug":"view-or-restrict-the-number-of-connections-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.top-password.com\/blog\/view-or-restrict-the-number-of-connections-in-sql-server\/","title":{"rendered":"How to View or Restrict the Number of Connections in SQL Server"},"content":{"rendered":"<p>SQL Server allows a maximum of 32,767 user connections. In this tutorial we&#8217;ll show you how to find out the number of connections to a database in SQL Server, and restrict the number of connections to a SQL Server instance.<\/p>\n<p><strong>Part 1: View Total Number of Connections in SQL Server<\/strong><\/p>\n<p>Need to check the number of active connections for each database? This can be done easily using the following script. The script displays the databaseName, the number of connections and the login name:<\/p>\n<p><code>SELECT DB_NAME(dbid) as DBName,<br \/>\nloginame,<br \/>\nCOUNT(dbid) as NumberOfConnections<br \/>\nFROM sys.sysprocesses<br \/>\nWHERE dbid &gt; 0<br \/>\nGROUP BY dbid, loginame<\/code><\/p>\n<p>If you&#8217;re running SQL Server 2012, 2014 or 2016, run this script instead:<\/p>\n<p><code>SELECT DB_NAME(database_id) as DBName,<br \/>\nlogin_name,<br \/>\nCOUNT(session_id) as NumberOfConnections<br \/>\nFROM sys.dm_exec_sessions<br \/>\nGROUP BY database_id, login_name<\/code><\/p>\n<p><strong>Part 2: Restrict Concurrent Database Connections in SQL Server<\/strong><\/p>\n<p>To configure the maximum user connections, open SQL Server Management Studio and connect to your database, then follow these steps:<\/p>\n<ol>\n<li>In Object Explorer, right-click a server and click <strong>Properties<\/strong>.\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.top-password.com\/blog\/wp-content\/uploads\/2016\/12\/sql-server-properties.png\" alt=\"sql-server-properties\" width=\"506\" height=\"517\" class=\"alignnone size-full wp-image-8633\" \/><\/p>\n<\/li>\n<li>Click the <strong>Connections<\/strong> node on the left page.<\/li>\n<li>Under Connections, in the <strong>Maximum number of concurrent connections<\/strong> box, type or select a value from 0 through 32767 to set the maximum number of users that are allowed to connect simultaneously to the instance of SQL Server.\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.top-password.com\/blog\/wp-content\/uploads\/2016\/12\/max-connections-in-sql-server.png\" alt=\"max-connections-in-sql-server\" width=\"600\" height=\"539\" class=\"alignnone size-full wp-image-8634\" \/><\/p>\n<\/li>\n<li>Click <strong>OK<\/strong>. Restart SQL Server to apply your changes.<\/li>\n<\/ol>\n<!-- AddThis Advanced Settings generic via filter on the_content --><!-- AddThis Share Buttons generic via filter on the_content -->","protected":false},"excerpt":{"rendered":"<p>SQL Server allows a maximum of 32,767 user connections. In this tutorial we&#8217;ll show you how to find out the number of connections to a database in SQL Server, and restrict the number of connections to a SQL Server instance. Part 1: View Total Number of Connections in SQL Server Need to check the number [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,7,10],"tags":[3202,3201],"class_list":["post-8631","post","type-post","status-publish","format-standard","hentry","category-others","category-sql-server","category-tips-tricks","tag-sql-server-maximum-number-of-connections","tag-sql-server-number-of-connections"],"_links":{"self":[{"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/posts\/8631","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/comments?post=8631"}],"version-history":[{"count":8,"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/posts\/8631\/revisions"}],"predecessor-version":[{"id":8641,"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/posts\/8631\/revisions\/8641"}],"wp:attachment":[{"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/media?parent=8631"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/categories?post=8631"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.top-password.com\/blog\/wp-json\/wp\/v2\/tags?post=8631"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}