Re: Tracing Used Tables
- From: Linchi Shea <LinchiShea@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 28 Jul 2009 05:54:01 -0700
Why not set up a SQL trace to capture all the TSQL and RPC compltetd events
in the database, and then analyze the trace files to get the info you are
looking for?
This will still give you a lot of data on a business database, but should be
much less than LockAcquired. And hopefully on a soon-to-be-retired database,
the activities level has taped off.
Linchi
"Shiju Samuel" wrote:
Erland Sommarskog wrote:.
Shiju Samuel (Shiju.Samuel@xxxxxxxxx) writes:
I need to trace all the tables which are used in the database. I
found the objects:object:Opened is not getting any trace hit. Now
I am tracing with Locks->LockAquired with all the possible filter
on server side trace is there any other way to do this ? This is
generating a lot of trace data even for ever key locks aquired.
Tracing LockAcquired on a production server is definitely not to
recommend. That will cause an enormous extra load on the box.
Overall, this is no simple task, and even less so on SQL 2000. If your
application does not use stored procedures, but uses ad hoc SQL, you
collect data from master.syscacheobjects.sql and then parse that. If
your application is based to stored procedures, collect which
procedures that are run, and then build a shadow copy of the database
where you load all proceduers after table tables, so that sp_depends
becomes decently correct. (But tables which appears in queries with
temp tables and not elsewhere in the procedures will not be listed in
sysdepends.)
And once you have this data, you may still have tables that are only
used for end-of-the-year reporting and the like, and thus are not used
this time of year...
Thanks Erland - Looks to be an Herculean task considering 22 databases
which I need to retire on the server.
-- Thanks - Shiju
- References:
- Tracing Used Tables
- From: Shiju Samuel
- Re: Tracing Used Tables
- From: Erland Sommarskog
- Re: Tracing Used Tables
- From: Shiju Samuel
- Tracing Used Tables
- Prev by Date: RE: backup file names
- Next by Date: Re: Stored procedure is not returning data because of DateTime value
- Previous by thread: Re: Tracing Used Tables
- Next by thread: Tricky sql formulation
- Index(es):
Relevant Pages
|
