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

595
3
Jump to solution
04-02-2019 06:51 AM
DavidMcDermott1
New Contributor III

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

0 Kudos
1 Solution

Accepted Solutions
DavidMcDermott1
New Contributor III

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

View solution in original post

0 Kudos
3 Replies
SCVTAGIS
New Contributor II

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? - G... 

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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

That should just about do it....
0 Kudos
DavidMcDermott1
New Contributor III

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

0 Kudos