[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server: How to know when a table has been queried

Posted on 2011-10-04
3
Medium Priority
?
381 Views
Last Modified: 2012-05-12
I am working with a database which exists off a commercial product.  It has "Group" tables that are populated when users add groups via the front end, and they may use their own groups or groups that other users have made.  I have a large client who would like to remove unused groups.  Nothing changes in the tables when the groups are being used.

Triggers don't work on the "select" command.  Is there a way that I may add something (back-end only, sadly) that would keep track of when a group was used?

Thanks
jr
0
Comment
Question by:jruhe
3 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 1000 total points
ID: 36911821
This probably isn't going to be easy to do.  You can setup a profiler trace, but as far as I know you can't specify a specific table that you are looking for.  A table could be referenced by a stored procedure or function.  You would need a list of every object that references the table and then compare that to the profiler trace and see who executed it.  That would be a monumental task.  

But I have an easier solution.  Disable, not delete, the accounts in question and see who screams.  Crude, but probably more effective.

Greg

0
 
LVL 3

Assisted Solution

by:pg_vinod
pg_vinod earned 1000 total points
ID: 36914424
You Can use SQL Profiler for tracing tables activity such as selects, inserts, deletes and updates.
-- remember to Apply filters like
    1) on Group table names
    2) dbid -- target database

Review the trc output by querying through TSQL function:
SELECT *
FROM fn_trace_gettable(‘D:\SQLTrace\TraceGrouptableAccessFile.trc’, default) Trace

HTH,
Vinod Pottekkatt
0
 
LVL 4

Author Comment

by:jruhe
ID: 36920062
Thanks guys.  Both have value.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question