If you’ve worked with Microsoft SQL Server for any length of time, you’re probably already aware that SQL Server uses locks to control access to database objects such as rows, pages, partitions, and tables.

But did you know that SQL Server also creates a shared lock for every connection to the database? It does.

Viewing shared database locks

To see these, you can use the sys.dm_tran_locks dynamic management view as shown in the following query.

SELECT
     l.resource_type
    ,l.request_mode
    ,l.request_status
    ,l.request_session_id
FROM  
    sys.dm_tran_locks AS l
WHERE 
    resource_database_id = DB_ID();

When running this query against the AdventureWorks database, you can see the following results.

There are two rows in the result set since I have two open connections to the database. If I close one of the connections or use another database, the result set will change as shown below.

Counting connections

So, why is this useful? Sometimes you’d like to know just how many connections are currently associated with a database, perhaps before you attempt to detach it. You can certainly use SQL Profiler to get this information, but running a quick query in Query Editor can be much quicker.

More information on the sys.dm_tran_locks dynamic management view can be found in Books Online.

Do you have other uses for this? Or do you have another way of getting the number of current connections to the database? If so, I’d love to hear about them.

Joe