Is DesignDB In Use?

When Visual Studio Team System Database Edition opens a design-time validation database, it acquires an exclusive application lock on the DesignDB database using the following code.

    1:  EXEC @rc = sp_getapplock N'TSDATA', N'Exclusive', N'Session', 0, N'dbo';

A side effect of acquiring the application lock is that only a single Visual Studio instance can open the project using the same DesignDB instance, this is why the DesignDB name is stored in the <database project>.dbproj.user file which therefore should not be shared between. If you do share the <database project>.dbproj.user file and a second user will try to open the project it will get the following error dialog, indicating that the DesignDB instance associated with the project is already open.

You can use this knowledge to find out which DesignDB instances are actively being used by a Visual Studio instance by running the following query:

    1: SELECT  db_name([rsc_dbid]) AS [DesignDB]

    2: FROM    [master].[dbo].[syslockinfo] 

    3: WHERE   [rsc_type] = 10 

    4: AND     [req_ownertype] = 3 

    5: AND     [req_refcnt] = 1 

    6: AND     [rsc_text] like N'%[TSDATA]%'

If you have multiple instances of Visual Studio running and want to find out which process is holding an DesignDB instance open you can correlate it back to the process ID (PID) by using the following query:

    1: SELECT  DISTINCT [host_process_id] AS [PID]

    2: FROM    [master].[sys].[dm_exec_sessions] AS [es]

    3: JOIN    [master].[dbo].[syslockinfo] AS [li] ON [li].[req_spid] = [es].[session_id] 

    4: WHERE   [li].[rsc_type] = 10 

    5: AND     [li].[req_ownertype] = 3 

    6: AND     [li].[req_refcnt] = 1 

    7: AND     [li].[rsc_text] like N'%[TSDATA]%'

-GertD