July 27, 2004 at 4:04 am
For archiving purposes I'm trying to find redundant tables in a database. Is there a programatic way to find the when table data was
1. last read
2. last inserted/updated/deleted
Thanks
July 27, 2004 at 6:55 am
I run a server side trace to get this information (it's the only way I've found to capture reads against tables).
Run the below script, changing the filename, path and filters as appropriate. Run the trace for as long as needs be. You'll then want to bring the data from the files into a table, join against the sysobjects table to add the table names, remove those that you aren't concerned with. You'll then be left with a list of tables, who accessed them, and when.
/****************************************************/
/* Created by: SQL Profiler */
/* Date: 05/26/2004 08:08:09 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 2, N'I:\Trace_Files\Metrica_Access_Audit', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 48, 1, @on
exec sp_trace_setevent @TraceID, 48, 6, @on
exec sp_trace_setevent @TraceID, 48, 11, @on
exec sp_trace_setevent @TraceID, 48, 12, @on
exec sp_trace_setevent @TraceID, 48, 14, @on
exec sp_trace_setevent @TraceID, 48, 22, @on
exec sp_trace_setevent @TraceID, 49, 1, @on
exec sp_trace_setevent @TraceID, 49, 6, @on
exec sp_trace_setevent @TraceID, 49, 11, @on
exec sp_trace_setevent @TraceID, 49, 12, @on
exec sp_trace_setevent @TraceID, 49, 14, @on
exec sp_trace_setevent @TraceID, 49, 22, @on
exec sp_trace_setevent @TraceID, 51, 1, @on
exec sp_trace_setevent @TraceID, 51, 6, @on
exec sp_trace_setevent @TraceID, 51, 11, @on
exec sp_trace_setevent @TraceID, 51, 12, @on
exec sp_trace_setevent @TraceID, 51, 14, @on
exec sp_trace_setevent @TraceID, 51, 22, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 7
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
July 27, 2004 at 7:41 am
wow.
Thanks Nicholas. Looks good to me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply