nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

DavidMcDermott1

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

Find more posts tagged with

Accepted answers

DavidMcDermott1

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.

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

<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

AdminNCCMA

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.

DavidMcDermott1

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

<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

JoeBorgione

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

SCVTAGIS

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…

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.