Post reply

last_user_SCAN, last_user_Seek, last_user_Lookup, and last_user_Update

  • April 14, 2016 at 7:22 am

    #309912

    I run job every 10 minute to identify unused databases.

    Job runs SP which inserts into my table data from select below

    SelectDistinct

    @@ServerName As ServerName

    ,Name As DBName

    ,Max(Login_Time) As LoginTime

    ,Max(Last_Batch) As LastBatch

    ,Coalesce([Status], '') As [Status]

    ,Coalesce(HostName, '') As HostName

    ,Coalesce(Program_Name, '') As ProgramName

    ,Coalesce(NT_UserName,'') As NTUserName

    ,Coalesce(LogiName,'') As LogiName

    ,Max(last_user_SCAN) As last_user_Scan

    ,Max(last_user_Seek) As last_user_Seek

    ,Max(last_user_Lookup) As last_user_Lookup

    ,Max(last_user_Update) As last_user_Update

    From

    sys.databases d

    Left Join

    master.dbo.sysprocesses sp On (d.database_id = sp.dbid)

    Left Join

    sys.dm_db_index_usage_stats i on (d.database_id=i.database_id)

    Where

    d.database_id Not Between 1 and 4 /* Exclude system databases */

    Group By

    d.database_id

    ,Name

    ,Coalesce([Status],'')

    ,Coalesce(HostName,'')

    ,Coalesce(Program_Name,'')

    ,Coalesce(NT_UserName,'')

    ,Coalesce(LogiName,'')

    I added last_user_SCAN, last_user_Seek, last_user_Lookup, and last_user_Update

    later when I noticed that if stored procedure in one database calls stored procedure in different database, login information is not capturing.

    But what is difference between those four (last_user_SCAN, last_user_Seek, last_user_Lookup, and last_user_Update) ? If I have a row in my table where all columns are nulls except of last_user_SCAN, last_user_Seek - does this mean the database is in-use?

    Thank you

  • April 14, 2016 at 8:50 am

    #1872015

    Have you seen this information on sys.dm_db_index_usage_stats?

    https://msdn.microsoft.com/en-GB/library/ms188755.aspx

  • April 14, 2016 at 9:21 am

    #1872025

    Thank you, this is nice link. If a maintenance job rebuilds indexes, will it record as an user action?

    But database might not being used by any application.

  • April 14, 2016 at 9:37 am

    #1872038

    If you rebuild the index then the row will be deleted from dm_db_index_usage_stats until it is used again by a query. If you use re-organize then the last_user_ dates are left as they were before the reorganize.

  • April 14, 2016 at 9:42 am

    #1872043

    You might want to test this out for yourself to see how it works. The behaviour I've seen is on SQL 2012. It looks like it is different on SQL 2008.

    http://thomaslarock.com/2013/01/rebuilding-indexes-will-reset-index-usage-statistics-in-sql-server-2012/

  • April 14, 2016 at 3:14 pm

    #1872182

    Thank you!

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

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