Re: Tracing Used Tables

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

.

Relevant Pages

  • Re: SQL 2000 with SP3: Profiler tracing does not capture any data
    ... Are you sure you are tracing the correct server with the correct database ... Mark Allison, SQL Server MVP ... there were no data in the profiler trace window at all. ...
    (microsoft.public.sqlserver.server)
  • Re: Tracing Used Tables
    ... the objects:object:Opened is not getting any trace hit. ... Tracing LockAcquired on a production server is definitely not to recommend. ... Overall, this is no simple task, and even less so on SQL 2000. ...
    (microsoft.public.sqlserver.programming)
  • Re: [PATCH 1/3] relay: Fix 4 off-by-one errors occuring when writing to a CPU buffer.
    ... blktrace userspace code into a generic tracing library called libutt, ... controlling the trace: ... The other change to the kernel code is that it allows the buffers (and ... server: waiting for connections... ...
    (Linux-Kernel)
  • Re: Application level trace stops tracing
    ... Specifies the number of trace requests to store on the server. ... > No the tracing disappears on subsequent pages. ...
    (microsoft.public.dotnet.framework.aspnet)
  • [GIT PULL] tracing/core for v2.6.32
    ... tracing: Remove mentioning of legacy latency_trace file from documentation ... Move sched event insertion helpers in the sched switch tracer file ... Undef TRACE_EVENT_FN between trace events headers inclusion ... tracing/events: add missing type info of dynamic arrays ...
    (Linux-Kernel)