topic Last used/accessed datetime for feature class/table in Geodatabase in Data Management Questions

Last used/accessed datetime for feature class/table in Geodatabase

DavidMcDermott1 — Tue, 02 Apr 2019 13:51:26 GMT

Hi GeoNet'ers,

I am performing an audit of our enterprise database as part of a wider GIS Data Strategy. I am trying to ascertain a last accessed/used date for all the feature classes stored within the Geodatabase. The plan is to archive or delete feature classes that aren't been used then create or update metadata on the rest. If there are no options in the geodatabase, it is built on SQL Server 2012 which may have some facility.

Thanks,

David

Re: Last used/accessed datetime for feature class/table in Geodatabase

SCVTAGIS — Tue, 11 Aug 2020 20:01:17 GMT

Did you ever arrive at a solution? Querying the OS didn't seem promising because a flag to keep track of Date Accessed is off by default, so in the file system I was looking at the Date Accessed is always the same as the Date Created.

The only other option I have encountered so far is described here (although I haven't tried it yet) arcgis 10.2 - How to get the "modified" date for a FeatureClass in a file geodatabase via ArcPy? - Geographic Informatio…

Re: Last used/accessed datetime for feature class/table in Geodatabase

JoeBorgione — Tue, 11 Aug 2020 21:08:05 GMT

I'm pretty sure you can do this with ArcGIS Monitor

Re: Last used/accessed datetime for feature class/table in Geodatabase

DavidMcDermott1 — Fri, 10 Dec 2021 22:25:43 GMT

Thank you for your responses. The best solution I found was to query the index usage stats. I have pasted the SQL below. The main downside with this method is the value are reset if you reboot the server. Hope this helps.

select [schema_name], 
       table_name, 
	   PS.row_count AS TotalRowCount,
       max(last_access) as last_access 
from(
    select schema_name(schema_id) as schema_name,
           name as table_name,
           (select max(last_access) 
            from (values(last_user_seek),
                        (last_user_scan),
                        (last_user_lookup), 
                        (last_user_update)) as tmp(last_access))
                as last_access
from sys.dm_db_index_usage_stats sta
join sys.objects obj
     on obj.object_id = sta.object_id
     and obj.type = 'U'
     and sta.database_id = DB_ID()
) usage
JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id) = table_name
where schema_name NOT LIKE 'sde'
group by schema_name, 
         table_name,
		 PS.row_count

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Last used/accessed datetime for feature class/table in Geodatabase

AdminNCCMA — Tue, 18 Feb 2025 03:02:11 GMT

Hi David,

I am currently undertaking a similar audit on one of our SDE databases. I need to know when each feature class/table was last viewed/accessed to determine if I can delete it or not. Would you please mind explaining to me what each field means when I run your sql query as I don't understand, particularly the last_access field, I'm not sure what it's telling me, I was expecting to see lots of dates potentially dating back months/years (if the server had not been rebooted as you mentioned) but I'm only seeing the last couple of days dates. I'm unsure what I can delete and what I can't. Sorry if this is a silly question.