locked
Find all (current + dormant) applications thats calling our DB sql server RRS feed

  • Question

  • HI,

    I need to get the application lists calling our DB. I need both dormant and current. I thought profiler can give only active ones. Please correct me if I am wrong.

    Thanks in advance,

    Venkat

    Wednesday, June 10, 2015 6:53 AM

Answers

  • What i was thinking is may be there a application thats connecting every month once or so. Or say 2 months once. Those will not appear in sql profiling, right?

    No sql server profiler will only show you current sessions. You have some DMV's to help you with

    select * from sys.dm_exec_requests
    go
    select * from sys.dm_exec_connections
    go
    select * from sys.dm_exec_sessions


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by VenBin Wednesday, June 10, 2015 8:46 AM
    Wednesday, June 10, 2015 7:10 AM
  • Hi,

    I don't see 100% foolproof method to achieve this, but you can try couple of below mentioned methods.

    1. SQL Server dynamic management view

    SELECT db.[name] AS DatabaseName
    , (SELECT TOP 1 last_user_seek FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_seek DESC) AS last_user_seek
    , (SELECT TOP 1 last_user_scan FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_scan DESC) AS last_user_scan
    , (SELECT TOP 1 last_user_lookup FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_lookup DESC) AS last_user_lookup
    , (SELECT TOP 1 last_user_update FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_update DESC) AS last_user_update
    FROM sys.databases db Where database_id > 5
    ORDER BY db.[name]

    This query will give you the most recently used index in each database. If the indexes aren't being used, then the data probably isn't being used either. Also,  if you have maintenance plans set up to automatically reindex your databases regularly, then these dates may have recent values.

    2. Run a very lightweight profiler trace on your server and log just "Audit Login" events. Leave this running for a days or weeks.

    3. Worst solution, keep all database which you feel or received inputs from above queries  in single user mode and wait for the application users feedback, if no shout for a month then you can consider it is inactive.

    There is a connect to Microsoft development team to include a column in sys.databases that says last date it was accessed, but development team has closed this with Won't fix

    Database last accessed time 


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    • Marked as answer by VenBin Wednesday, June 10, 2015 8:46 AM
    Wednesday, June 10, 2015 7:33 AM

All replies

  • Hello

    From SQL Server we will get to know only active Applications which is connecting to your database.

    You will not be able to get to know from SQL if it is not connecting.

    Thanks,

    --------------------------------------------------------------------------------

    Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Wednesday, June 10, 2015 6:56 AM
  • Start with

    SELECT host_name,
        program_name,
    session_id,
    login_name,
    last_request_end_time,
    db_name(database_id) dbname
       
    FROM
    sys.dm_exec_sessions


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, June 10, 2015 6:59 AM
    Answerer
  • What i was thinking is may be there a application thats connecting every month once or so. Or say 2 months once. Those will not appear in sql profiling, right?
    Wednesday, June 10, 2015 7:00 AM
  • What i was thinking is may be there a application thats connecting every month once or so. Or say 2 months once. Those will not appear in sql profiling, right?

    No sql server profiler will only show you current sessions. You have some DMV's to help you with

    select * from sys.dm_exec_requests
    go
    select * from sys.dm_exec_connections
    go
    select * from sys.dm_exec_sessions


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by VenBin Wednesday, June 10, 2015 8:46 AM
    Wednesday, June 10, 2015 7:10 AM
  • Hi Venkat,

    If you were running the profiler by when the application is connecting then you will get to know. Or else you you will get to know.

    Thanks,

    -----------------------------------------------------------------------

    Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Wednesday, June 10, 2015 7:11 AM
  • Hi,

    I don't see 100% foolproof method to achieve this, but you can try couple of below mentioned methods.

    1. SQL Server dynamic management view

    SELECT db.[name] AS DatabaseName
    , (SELECT TOP 1 last_user_seek FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_seek DESC) AS last_user_seek
    , (SELECT TOP 1 last_user_scan FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_scan DESC) AS last_user_scan
    , (SELECT TOP 1 last_user_lookup FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_lookup DESC) AS last_user_lookup
    , (SELECT TOP 1 last_user_update FROM sys.dm_db_index_usage_stats ix1 WHERE ix1.database_id = db.database_id ORDER BY last_user_update DESC) AS last_user_update
    FROM sys.databases db Where database_id > 5
    ORDER BY db.[name]

    This query will give you the most recently used index in each database. If the indexes aren't being used, then the data probably isn't being used either. Also,  if you have maintenance plans set up to automatically reindex your databases regularly, then these dates may have recent values.

    2. Run a very lightweight profiler trace on your server and log just "Audit Login" events. Leave this running for a days or weeks.

    3. Worst solution, keep all database which you feel or received inputs from above queries  in single user mode and wait for the application users feedback, if no shout for a month then you can consider it is inactive.

    There is a connect to Microsoft development team to include a column in sys.databases that says last date it was accessed, but development team has closed this with Won't fix

    Database last accessed time 


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    • Marked as answer by VenBin Wednesday, June 10, 2015 8:46 AM
    Wednesday, June 10, 2015 7:33 AM