none
Which application using Database RRS feed

  • Question

  • Hello,

    We have Sql 2008R2 and planning to move Sql 2012, we would like to know which application or any other connecting to the database/instance for last few days so we can have idea for migration.

    Thanks,


    Saturday, January 25, 2020 4:51 PM

Answers

  • Hi pdsqsql,

    If you did not create anything to monitor the SQL server connection, you can only get the information for current connections. I suggest you try to create a simple logon trigger that records an entry every time a user logins.  Please check the third method from this link.  You can also try to use SQL server audit. Choose the option ‘Successful_Login_Group’ as below screenshot. Please refer this blog to get more information.

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 27, 2020 6:36 AM
  • I tried sys.dm_exec_sessions and SP_WHO2 but i wanted to see for longer days v/s current sessions/applications/host.

    The sys.dm_exec_sessions DMV only includes current sessions. You could merge the results into a history table to capture sessions over time. For example, the script below could be scheduled as a SQL Server Agent T-SQL job step to run every minute to store the information in a user database table.

    IF OBJECT_ID(N'dbo.dm_exec_sessions_history') IS NULL
    BEGIN
    	SELECT TOP(0) host_name, program_name, original_login_name, CAST(NULL AS datetime2(0)) AS capture_timestamp
    	INTO dbo.dm_exec_sessions_history
    	FROM sys.dm_exec_sessions;
    END;
    
    MERGE dbo.dm_exec_sessions_history AS target
    USING (
    	SELECT DISTINCT
    		  COALESCE(host_name, N'') AS host_name
    		, COALESCE(program_name, N'') AS program_name
    		, COALESCE(original_login_name, N'') AS original_login_name
    	FROM sys.dm_exec_sessions AS s
    	WHERE s.is_user_process = 1
    	) AS source ON
    		source.host_name = target.host_name
    		AND source.program_name = target.program_name
    		AND source.original_login_name = target.original_login_name
    WHEN NOT MATCHED BY TARGET THEN
    	INSERT (host_name, program_name, original_login_name, capture_timestamp)
    	VALUES (source.host_name, source.program_name, source.original_login_name, SYSDATETIME());
    GO


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, January 27, 2020 11:02 AM
    Moderator

All replies

  • Please see if this blog helps: https://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Saturday, January 25, 2020 5:08 PM
  • You could create a server trace of login events (including the host_name) or periodically capture results of the sys.dm_exec_sessions DMV with a SQL Agent job. The results can be aggregated by host name to identify the hosts running applications that connect to the instance.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, January 25, 2020 5:12 PM
    Moderator
  • Thanks Dan.

    I tried sys.dm_exec_sessions and SP_WHO2 but i wanted to see for longer days v/s current sessions/applications/host.

    Saturday, January 25, 2020 5:43 PM
  • Thanks Vaibhav.

    link has very good and couple good script.

    I need to check for last few days instead of current transactions so i am sure that i can get more details about application if they are not connected recently.

    Saturday, January 25, 2020 5:48 PM
  • Hi pdsqsql,

    If you did not create anything to monitor the SQL server connection, you can only get the information for current connections. I suggest you try to create a simple logon trigger that records an entry every time a user logins.  Please check the third method from this link.  You can also try to use SQL server audit. Choose the option ‘Successful_Login_Group’ as below screenshot. Please refer this blog to get more information.

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 27, 2020 6:36 AM
  • I tried sys.dm_exec_sessions and SP_WHO2 but i wanted to see for longer days v/s current sessions/applications/host.

    The sys.dm_exec_sessions DMV only includes current sessions. You could merge the results into a history table to capture sessions over time. For example, the script below could be scheduled as a SQL Server Agent T-SQL job step to run every minute to store the information in a user database table.

    IF OBJECT_ID(N'dbo.dm_exec_sessions_history') IS NULL
    BEGIN
    	SELECT TOP(0) host_name, program_name, original_login_name, CAST(NULL AS datetime2(0)) AS capture_timestamp
    	INTO dbo.dm_exec_sessions_history
    	FROM sys.dm_exec_sessions;
    END;
    
    MERGE dbo.dm_exec_sessions_history AS target
    USING (
    	SELECT DISTINCT
    		  COALESCE(host_name, N'') AS host_name
    		, COALESCE(program_name, N'') AS program_name
    		, COALESCE(original_login_name, N'') AS original_login_name
    	FROM sys.dm_exec_sessions AS s
    	WHERE s.is_user_process = 1
    	) AS source ON
    		source.host_name = target.host_name
    		AND source.program_name = target.program_name
    		AND source.original_login_name = target.original_login_name
    WHEN NOT MATCHED BY TARGET THEN
    	INSERT (host_name, program_name, original_login_name, capture_timestamp)
    	VALUES (source.host_name, source.program_name, source.original_login_name, SYSDATETIME());
    GO


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, January 27, 2020 11:02 AM
    Moderator
  • Thanks Cathy,  I will try it out.
    Wednesday, January 29, 2020 4:50 PM
  • Thanks Dan.

    I will implement and see the results and let you know.

    Wednesday, January 29, 2020 4:51 PM