application connections MSSQL

pma111
pma111 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
IMHO, the best thing is to go application by application and check their configuration settings to find out the connection string.
Top Expert 2012
Commented:
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