2

I inherited a large, pretty-much undocumented SQL Server 2008 R2 general purpose database, used by dozens of different applications.

We know many of the tables are no longer in use.

Any suggestions on how to determine what tables are still being routinely accessed over a weeks time? Or how to best configure Profiler to gather that info?

2

One possible way is to look into index usage statistics, such as sys.dm_db_index_usage_stats() (and several others with similar names).

Two possible catches here are:

  1. Their data isn't persisted between server restarts, so make sure that uptime is sufficiently long.
  2. If you have scheduled maintenance jobs, for example, regular index rebuilds, they will spoil the statistics. But at least you will be able to separate your objects into 2 categories: one with similar access counts across all of them and the rest is well above that.

Overall it's not very reliable, but at least will give you some quick initial insight. The second step could be, for example, denying all permissions on suspected tables and watch for your work phone / email box :).

1

Run a job nightly which counts the rows in each table. Store the output and see how it trends.

Obvious problems -

  • will not show read-only loads, such as reference tables.
  • will miss infrequent activities such as financial year end.
  • will miss corner cases.

It will, however, show the tables that definitely are used.

I'm afraid the only reliable solution is to examine each application's code.

0

You can use audit

CREATE SERVER AUDIT AuditDataAccess
TO FILE ( FILEPATH ='C:\SQLAudit\' );

ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON);

CREATE DATABASE AUDIT SPECIFICATION DatabaseAudit
FOR SERVER AUDIT [AuditDataAccess] 
ADD (SELECT ON SCHEMA::[DataSchema] BY [public])
WITH (STATE = ON);

And then you can query that audit file by simple select

SELECT * FROM fn_get_audit_file('C:\SQLAudit\AuditDataAccess_*.sqlaudit',default,default);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.