application connections MSSQL

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!
LVL 3
pma111Asked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
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] ,
        S.hostname;

Open in new window


But ProgramName is an optional information given be the connecting program.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
IMHO, the best thing is to go application by application and check their configuration settings to find out the connection string.
0
 
Anthony PerkinsCommented:
Or you could always do the "Scream" test.  Take the database offline and wait ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.