Determine last time a table was used

  • I was just trying to do some cleanup on our DB and was wondering if there was a way to dermine that last time a table was accessed?

    we have something like 3000 tables and i'm sure many are old tables used by previous programmers that aren't being used anymore (temp tables for testing, etc). I'd like to archive and toss them but it's hard to track down every line of code in all our software to see if anything references it.

    Any ideas or suggestions on how to query the DB to see if anything is actually touching a table?

    thanks!

  • :), note the access rights and then remove them. It's the easiest way to see if someone's using the table.

    Or you can set up a trace for access to certain tables and let it run. I'd go at least 30 days (lots of monthly periodic processes). If nothings changing in terms or add/updates/deletes, then you can archive the table. be sure you save it for a year. Never know if it's used for some yearly close out.

  • Try using sys.dm_db_index_usage_stats DMV? I'm not sure how long info stays here, but certainly is retained through service stops & starts (caveat: I haven't actually used this in prod, might be making a logical error somewhere, but this seems to work in simple tests):

    --find unused tables

    select t.*

    from sys.tables t

    left outer join sys.dm_db_index_usage_stats ddius

    on ddius.object_id = t.object_id

    where ddius.object_id is null

    order by t.name

    Or you could use trace--this is not trivial, but works (in SQL2000 too). Start a trace capturing only "lock:aquired" events, filtered down to the dbid that you're interested in and excluding system spids. Only event column you need to trace is ObjectID, but other ones of interest might be application name, hostname, sessionloginname, starttime. If you have narrowed list down to just a few suspect tables, you can add their object IDs to the ObjectID trace filter to make this more efficient. Leave this running for as long as you think is necessary...maybe a day, or a week, or a month? Import the resulting trace files in to SQL tables, & then analyze by (maybe aggregate on) objectid, do similar left outer join from sysobjects to determine which tables have not been used.

Viewing 3 posts - 1 through 2 (of 2 total)

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