Suggestion Needed to audit Table

Question

Suggestion Needed to audit Table

CDBA

Hall of Fame

Points: 3266
More actions
July 6, 2007 at 5:04 pm

#173435

Hi
I need to resolve the below requirement without using trigger:
1. We have a database [SQL2000 EE] which contains 1000+ tables
2. Many tables in that database are not being used actively. [No one/application is quering them]
But there is no manual way/doc. to identify those tables. Could any one give some idea how to identify those tables? [For eg. To identify tables of a database which are not used in last 30 days].
Please let me know if you need more info.. Thanks in advance.
Regards
Utsab Chattopadhyay

CDBA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The only way you could do this is run a continual trace to see all executed statements. Then after the predetermined period of x (30) days, you try to find the table name in the trace file (loaded in a table at that point). This can be somewhat tricky and only semi-automated because a tablename may not be unique... for example : Orders and OrdersItems are 2 different tables, but if you search for the keywork Orders, you will get a hit on both table calls. The same could be said about all other database objects calls and also alias in the queries (vwOrders, USP_OrderIns...). The good thing about it is that once you've identified a positive hit on a table, you can discard it for good in your delete list and move to the next table.

I'm curious here, why can't you use a trigger to do this? It would be an easy way to track all the tables with DML on them (excluding selects of course). This could discard a lot of manual work. Also those triggers could certainly be written in a way that doesn't impact performance at all. Here I'm thinking of something like this (never implemented this in production, or ever) :

CREATE TRIGGER dbo.TR_IsTableUsed_TableName_A_IUD ON dbo.TableName

AFTER INSERT, UPDATE, DELETE

As

SET NOCOUNT ON

INSERT INTO DBA.dbo.UsedTables (DbName, TableName, LogDate) VALUES (DBName(), 'dbo.TableName', GETDATE())

DROP TRIGGER dbo.TR_IsTableUsed_TableName_A_IUD

GO

In this scenario, the trigger would only be executed once before self-destruction, so it would never fire more than once. Also this could be up and running in a matter of minutes. Another version of this would be to have a job scan the UsedTables every x minutes and drop all triggers from tables with activity. Or to make things very obscure, you could add a trigger on the UsedTables table that would drop the calling trigger after the insert . Different flavors for different scenarios (in the event you don't want to have a public user with DDL ADMIN permissions for the month(s) this will go on).

Using the trigger solution combined with the trace would certainly lessen the manual workload on your part. I know you said no trigger, but I'd be really curious to give this one a try in real life .

Or a compromise between all ideas. Run the trace for 30 days on the production system. Restore a backup of the DB on a Test server as it was 30 days ago (just as it was when you started the trace). Then use my triggers idea. Replay the trace. Then list the tables that still have the trigger alive after the trace is replayed. Then manually scan for those table names in the trace table and eliminate the valid tables from that list one by one. This may be the safest / fastest way to run this procedure on production environement.

Sachin Samuel-286731 Right there with Babe Points: 778 More actions · Answer 2

You can also explore few tools available to read sql server log files (.ldf). One of them is log explorer.

http://www.lumigent.com/products/le_sql.html

Thanks

Sachin

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

Use profiler to trace the acivities and then from there you can get those list manipulating.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

CDBA Hall of Fame Points: 3266 More actions · Answer 4

Hi

Thanks all for your response. I forwarded the trigger based suggestion [Highlighting that the trigger will be dropped only after 1 execution] and waiting for manager's approval....

Will update this thread with final outcome and if I need more help

Thanks again....

Regards

Utsab Chattopadhyay

CDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

The only PitFall I see in my idea is the fact that the users need to have DDL_ADMIN rights.

That's why I was using a second process to delete the triggers in my final "attempt" at this.

Here's a working sample of my first idea :

USE master

GO

CREATE TABLE dbo.a (Col INT NOT NULL)

GO

CREATE TRIGGER dbo.TRa ON dbo.A

FOR INSERT, UPDATE, DELETE

AS

PRINT 'INSERT into...'

DROP TRIGGER dbo.TRa

GO

SELECT * FROM dbo.SysObjects WHERE Name = 'TRa' AND XType = 'TR'

GO

INSERT INTO dbo.a (Col) VALUES (1)

GO

SELECT * FROM dbo.SysObjects WHERE Name = 'TRa' AND XType = 'TR'

GO

DROP TABLE dbo.a