locked
Last accessed Database Date RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Could anyone please let me know, how to check the last accessed date of the list of all databases in a particular server in MS SQL SERVER. 

    Regards,

    PURNA.

    Wednesday, July 22, 2015 11:35 AM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    If you haven't implemented an auditing, then there is no reliable way to get the information. You can use index stats like

    select max(last_user_seek), max(last_user_scan), max(last_user_update)
from sys.dm_db_index_usage_stats

    but it's not 100% sure

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 22, 2015 11:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    The Query doesn't helps us, also we didn't implemented auditing. Is there any other way/scenario.?.please reply.
    Wednesday, July 22, 2015 11:58 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    There is no fool-proof method to identify the same without auditing.

    You may take the database offline(risk attached) and then wait for someone raise a complaint.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]

    Wednesday, July 22, 2015 12:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    As of now there is no feature for this. I have seen Kimberly requested the same on MS Connect and I hope MS developers are working on it. They are planning to include a column in sys.databases. Anyway coming back to the workaround for you is to create an audit (SQL Server 2008) or DMV for Index (SQL Server 2005)

    There is a blog from Aaron on this.

    http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

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

    Wednesday, July 22, 2015 12:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In addition to Olaf's reply

    In SQL 2005 there is, sort of. This is query lists the last execution
    time for all SQL modules in a database:

       SELECT object_name(m.object_id), MAX(qs.last_execution_time)
       FROM   sys.sql_modules m
       LEFT   JOIN (sys.dm_exec_query_stats qs
                    CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st) 
              ON m.object_id = st.objectid
             AND st.dbid = db_id()
       GROUP  BY object_name(m.object_id)

    But there are tons of caveats. The starting point of this query is
    the dynamic management view dm_exec_query_stats, and the contents is
    per *query plan*. If a stored procedure contains several queries, 
    there are more than one entry for the procedure in dm_exec_query_stats.

    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, July 22, 2015 12:10 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote
    oh. ok{Thanks all}. In general decommission list of databases, by an SQL DBA in any project. What are the basic checks need to be researched if it was not used frequently(over 1 year). 
    Wednesday, July 22, 2015 12:15 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    https://gallery.technet.microsoft.com/Decommission-Database-c5fbc884

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

    Wednesday, July 22, 2015 1:17 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    There is not perfect way feedback has been given to Microsoft about the same and I have still not seen this feature in 2014 so no there is no perfect way. You can also refer to this Stackexchange thread

    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 Change123 Monday, August 10, 2015 10:35 AM
    • Edited by Shanky_621MVP Monday, August 10, 2015 10:55 AM
    Wednesday, July 22, 2015 2:37 PM