Auditing table access

We regularly load data into hundreds of tables in a reporting database.
I know that many of the tables were needed years ago for short-term
projects, and are no longer used. I'm trying to find a way to identify
which tables are never accessed by users, so that we can stop those
loads and drop the tables. For this purpose, if nobody SELECTs from a
table in 30 days, I can consider that table dead. And, if anyone
SELECTs from the table even once, I need to keep it around.
This is a high-usage database, so performance is key. I'd rather not
run a Profiler session for 30 days straight, because of the potential
impact on performance. Ideally, I'd like to find something like a
one-time trigger: a table is accessed, a trigger fires and adds that
table to a "Keep Me" list somewhere, then the trigger is disabled. I
don't think SELECT triggers exist in SQL2000, though.
Anyone have creative ideas on how to generate a list of used or unused
tables? Or is there a way I could do this with Profiler without
creating too much overhead?
Cheers!Hi stavros
"stavros" wrote:
> We regularly load data into hundreds of tables in a reporting database.
> I know that many of the tables were needed years ago for short-term
> projects, and are no longer used. I'm trying to find a way to identify
> which tables are never accessed by users, so that we can stop those
> loads and drop the tables. For this purpose, if nobody SELECTs from a
> table in 30 days, I can consider that table dead. And, if anyone
> SELECTs from the table even once, I need to keep it around.
> This is a high-usage database, so performance is key. I'd rather not
> run a Profiler session for 30 days straight, because of the potential
> impact on performance. Ideally, I'd like to find something like a
> one-time trigger: a table is accessed, a trigger fires and adds that
> table to a "Keep Me" list somewhere, then the trigger is disabled. I
> don't think SELECT triggers exist in SQL2000, though.
AFAIK profiler is the only tool that is likely to do this. You could run
multiple profiles that covers the whole period and process each profile
offline rather than having one single set of outputs to process at the end of
the period.
> Anyone have creative ideas on how to generate a list of used or unused
> tables? Or is there a way I could do this with Profiler without
> creating too much overhead?
>
This is where source code control is very advantages, just doing textual
searches for your tables will eliminate alot of them. Alternatively if you
use stored procedures, then you could script the stored procedures on their
own and do a textual search. If want to know which procedures are called
without using profiler you could add code that audits the procedure being
run, although this will be an overhead whilst you are collecting the
information and a potential bottleneck. it will also mean a code change.
> Cheers!
>
John