Trying to determine active SQL database?

Other SQL Server Topics
#1

Does anyone know if there is a sql query ( and what that might be ) that will let someone know what an active database would be ... for example

I have a sql server running and it has several databases

chad

mike

joe

I am trying to figure out which database a specific program is using ( as the current active database ) without opening the program to get that information

Anyone have any idea on if this can be done or how ?

Thank you so much

#2

Would select the latest record of the biggest table help you out?

#3

Possibly not sure let me see what you are thinking ... and thank you for offering your help

#4

Maybe running sql profiler for an extended period?

Maybe also check to see if there are any audit columns such as CreatedDate and/or UpdatedDate

#5

Or you can use extended events:

http://sqlnotesfromtheunderground.wordpress.com/2015/12/02/is-the-database-being-used-answered-with-extended-events/)

1 Like
#6

To determine the active database on a SQL server, there are a couple of options. One approach is to query the sys.sysprocesses system view, which holds details about all the running processes on the SQL server. By checking the database_id column of this view, you can identify the ID of the database that each process is connected to.
Another method is to use the sys.dm_exec_sessions dynamic management view, which holds information about all the currently active sessions on the SQL server. The database_id column of this view also indicates the ID of the database that each session is connected to.
You can use the following query to find the active database for a specific program:

SELECT DB_NAME(database_id)
FROM sys.sysprocesses
WHERE program_name = 'your_program_name'

Or:

SELECT DB_NAME(database_id)
FROM sys.dm_exec_sessions
WHERE program_name = 'your_program_name'
2 Likes