Post reply

How to find last accessed date time of a SQL Server 2008 database?

  • February 7, 2012 at 3:08 am

    #252495

    How we can find last accessed date time of a SQL Server 2008 database apart from using the DMV sys.dm_db_index_usage_stats because it will show result only for database tables having indexes.

    Any audit or trace option...?

    Regards

    Vivek

  • February 7, 2012 at 6:21 pm

    #1444249

    My q2uestion would be.... why do you have tables that people access that have no indexes?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 7, 2012 at 7:56 pm

    #1444263

    Found this..

    http://www.sanssql.com/2008/10/when-was-my-database-last-used.html

  • February 7, 2012 at 8:00 pm

    #1444265

    Check this post.

    http://www.sqlservercentral.com/Forums/Topic517209-5-2.aspx#bm688879

    java

  • February 7, 2012 at 11:57 pm

    #1444299

    We are in the process of retiring unused DBs. These databases and tables were created few years back.

    Also, its not necessary that a table should have index.

  • August 29, 2012 at 9:38 am

    #1531477

    sp_MSforeachdb @command1='use [?];

    SELECT DB_NAME() as DatabaseName,

    --s1.sql_handle,

    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,

    ( (CASE WHEN statement_end_offset = -1

    THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)

    ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,

    last_execution_time,

    execution_count,

    plan_generation_num,

    total_worker_time,

    last_worker_time,

    min_worker_time,

    max_worker_time,

    total_physical_reads,

    last_physical_reads,

    min_physical_reads,

    max_physical_reads,

    total_logical_writes,

    last_logical_writes,

    min_logical_writes,

    max_logical_writes

    FROM sys.dm_exec_query_stats AS s1

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

    WHERE s2.objectid is null

    ORDER BY last_execution_time desc--,s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset

    '

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply