Database list and the last access date

Question

Post reply

Database list and the last access date

Adelia

SSC-Addicted

Points: 422

March 17, 2021 at 6:18 am

Go to Answer

#3858800

I've SQL like this,

SELECT name as [Database Name], [Last Access Date] =(select MAX(temp.lastaccess)
from ( select lastaccess =
max(last_user_seek)
where max(last_user_seek)is not null
union all
select lastaccess = max(last_user_scan)
where max(last_user_scan)is not null
union all
select lastaccess = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select lastaccess =max(last_user_update)
where max(last_user_update) is not null) temp)
FROM master.dbo.sysdatabases sysdb
left outer join sys.dm_db_index_usage_stats Idxus
on sysdb.dbid= Idxus.database_id
group by sysdb.name

It return this, ( TODAY ACCESS )

sql-001

How SQL look like to return - List of database and EACH database last access date

Please help

ScottPletcher

SSC Guru

Points: 101120
More actions
March 22, 2021 at 4:42 pm
Answer
#3860892

SQL Server doesn't store, and thus doesn't know, the "last accessed date" for each db.
After SQL starts, when any index is accessed, an entry for that db and index is to added to the sys.dm_db_index_usage_stats view. When SQL shuts down, all that info is lost.
However, accesses that don't read data from that db are not recorded. For example, if you exec a proc that doesn't actually read data from that db, that db access is not recorded anywhere by SQL Server itself.
Thus, you'd need extended events or triggers or some other mechanism to determine if/when activity occurs on any given db.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

The SQL would look like the following... 😀

   WITH ctePreAgg AS
(--==== Pre-aggregate the relevant columns to make display easier.
 SELECT  DBName     = DB_NAME(database_id) --Easier than joining to sys.databases
        ,MaxSeek    = MAX(last_user_seek)
        ,MaxScan    = MAX(last_user_scan)
        ,MaxLookUP  = MAX(last_user_lookup)
        ,MaxUpdate  = MAX(last_user_update)
   FROM sys.dm_db_index_usage_stats
  GROUP BY database_id
)--==== Display only the latest user activity date for each database.
     -- The OUTER APPLY does a fast and easy "UNPIVOT" to make finding the MAX
     -- from the 4 pre-aggregated columns a simple task.
 SELECT  [Database Name]     = DBName
        ,[Last Access Date]  = CONVERT(DATE,MAX(oa.LastReadDT))
   FROM ctePreAgg pa
  OUTER APPLY (VALUES (MaxSeek),(MaxScan),(MaxLookUP),(MaxUpdate)) oa (LastReadDT) 
  GROUP BY DBName
  ORDER BY DBName
;

--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)

Adelia SSC-Addicted Points: 422 More actions · Answer 2

Jeff Moden wrote:

The SQL would look like the following... 😀

   WITH ctePreAgg AS
(--==== Pre-aggregate the relevant columns to make display easier.
 SELECT  DBName     = DB_NAME(database_id) --Easier than joining to sys.databases
        ,MaxSeek    = MAX(last_user_seek)
        ,MaxScan    = MAX(last_user_scan)
        ,MaxLookUP  = MAX(last_user_lookup)
        ,MaxUpdate  = MAX(last_user_update)
   FROM sys.dm_db_index_usage_stats
  GROUP BY database_id
)--==== Display only the latest user activity date for each database.
     -- The OUTER APPLY does a fast and easy "UNPIVOT" to make finding the MAX
     -- from the 4 pre-aggregated columns a simple task.
 SELECT  [Database Name]     = DBName
        ,[Last Access Date]  = CONVERT(DATE,MAX(oa.LastReadDT))
   FROM ctePreAgg pa
  OUTER APPLY (VALUES (MaxSeek),(MaxScan),(MaxLookUP),(MaxUpdate)) oa (LastReadDT) 
  GROUP BY DBName
  ORDER BY DBName
;

The script not returning as expected

I want the script

List all the databases
Another column named " Last accessed "

Last accessed is shown. Let say, CRM Database last accessed was 18-09-1995. It will shown

Please help

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

I just reran the code I posted on my production box and it's returning precisely (except I stripped the time off the "Last Accessed Date" output, which you can get back just by removing the CONVERT in the outer query) what you asked for in your original post and includes all databases. I'm starting to think that you may have only looked at the code instead of running it. You also need to have some pretty significant privs at the system level for it to be able to see all of the databases in a system.

But... if you did any SELECT (for example) in the CRM Database you speak of, then today will be the date returned. That's one of the problems with using sys.dm_db_index_usage_stats or just about anything else you might be able to gin up... and access is an access even if by accident.

--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)

Adelia SSC-Addicted Points: 422 More actions · Answer 4

ScottPletcher wrote:

SQL Server doesn't store, and thus doesn't know, the "last accessed date" for each db.
After SQL starts, when any index is accessed, an entry for that db and index is to added to the sys.dm_db_index_usage_stats view. When SQL shuts down, all that info is lost.
However, accesses that don't read data from that db are not recorded. For example, if you exec a proc that doesn't actually read data from that db, that db access is not recorded anywhere by SQL Server itself.
Thus, you'd need extended events or triggers or some other mechanism to determine if/when activity occurs on any given db.

thanks for info