application connections MSSQL

pma111 used Ask the Experts™
we need to do some documentation around which SQL databases are serving which applications for asset management and CMDB purposes. Some of which have been handed over to support with no real documentation on what databases/SQL instances serve which applications, and which servers the applications are installed upon. Would there be any clues within SQL Server on which apps the databases are connected to, and what servers those apps are hosted on? Any queries which may give some clues would be a huge help!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
You may start with

SELECT  DB_NAME(S.[dbid]) AS DatabaseName ,
        CAST(RTRIM(S.hostname) AS NVARCHAR(128)) AS HostName ,
        CAST(RTRIM(S.loginame) AS NVARCHAR(128)) AS LoginName ,
        CAST(RTRIM(S.[program_name]) AS NVARCHAR(128)) AS ProgramName ,
        CAST(RTRIM(S.[status]) AS NVARCHAR(128)) AS [Status] ,
        COUNT(S.[dbid]) AS Connections
FROM    sys.sysprocesses S
WHERE   S.[dbid] > 0
GROUP BY S.[dbid] ,
        S.loginame ,
        S.[program_name] ,
        S.[status] ,

Open in new window

But ProgramName is an optional information given be the connecting program.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
IMHO, the best thing is to go application by application and check their configuration settings to find out the connection string.
Top Expert 2012
Or you could always do the "Scream" test.  Take the database offline and wait ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial