April 22, 2009 at 1:02 am
I need to know which queries are being executed and what modification each performed on tables. Is there any tool to handle this?
April 22, 2009 at 1:25 am
hi
try using the stored proc 'exec sp_trace_setstatus
manz
April 22, 2009 at 1:32 am
so far as I know it sets status of a server side trace.
maybe I've not asked my question clearly; I need history of data changes and the queries that performed those changes
April 22, 2009 at 4:01 am
You can have following options:
1. DML Trigger
2. C2 Auditing
3. Profiler to capture the desire DML transactions.
Before implementing the above options you need to understand the objective and find out why and what you want? As C2 auditing and profiler is highly resource consuming process.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 22, 2009 at 4:21 am
In addition to the suggestions above, it's worth noting that SQL Server 2008 offers Change Data Capture, another way to monitor what was changed in the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2009 at 5:59 am
Just run a trace
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
April 22, 2009 at 6:08 am
peace2007 here is what i use.
it is a stored procedure i wrote, which creates a trace.
It creates a trace and a view in the MASTER database; it includes a lot of information, so you can see the DML statements, as well as performance indictors to see if any given statement was slow or fast.
it also creates a VIEW, so i can see the results of the trace on demand.
the viewname starts with sp_ so I can call it from anywhere:
select * from sp_DMLTrace
and I can review the trace at my leisure.
see if this might help you in the future:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_AddMyTrace]
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
declare @sql varchar(1000)
declare @path nvarchar(256)
declare @traceidout int
declare @maxfilesize bigint
declare @maxRolloverFiles int
declare @on bit
set @on = 1
set @maxRolloverFiles = 2
set @maxfilesize = 50
--we want the current trace folder
--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1
SET @path = @path + N'MyDMLtrace' --system appends .trc automatically for the filename
--create the trace
exec sp_trace_create @traceidout output, @maxRolloverFiles, @path, @maxfilesize, NULL
--for the Event Every SQL statement completed, capture all 64 columns of accessible data
exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData
exec sp_trace_setevent @traceidout, 12, 2, @on --SQL:BatchCompleted,BinaryData
exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID
exec sp_trace_setevent @traceidout, 12, 4, @on --SQL:BatchCompleted,TransactionID
exec sp_trace_setevent @traceidout, 12, 5, @on --SQL:BatchCompleted,LineNumber
exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName
exec sp_trace_setevent @traceidout, 12, 7, @on --SQL:BatchCompleted,NTDomainName
exec sp_trace_setevent @traceidout, 12, 8, @on --SQL:BatchCompleted,HostName
exec sp_trace_setevent @traceidout, 12, 9, @on --SQL:BatchCompleted, ClientProcessID
exec sp_trace_setevent @traceidout, 12,10, @on --SQL:BatchCompleted,ApplicationName
exec sp_trace_setevent @traceidout, 12,11, @on --SQL:BatchCompleted,LoginName
exec sp_trace_setevent @traceidout, 12,12, @on --SQL:BatchCompleted,SPID
exec sp_trace_setevent @traceidout, 12,13, @on --SQL:BatchCompleted,Duration
exec sp_trace_setevent @traceidout, 12,14, @on --SQL:BatchCompleted,StartTime
exec sp_trace_setevent @traceidout, 12,15, @on --SQL:BatchCompleted,EndTime
exec sp_trace_setevent @traceidout, 12,16, @on --SQL:BatchCompleted,Reads
exec sp_trace_setevent @traceidout, 12,17, @on --SQL:BatchCompleted,Writes
exec sp_trace_setevent @traceidout, 12,18, @on --SQL:BatchCompleted,CPU
exec sp_trace_setevent @traceidout, 12,19, @on --SQL:BatchCompleted,Permissions
exec sp_trace_setevent @traceidout, 12,20, @on --SQL:BatchCompleted,Severity
exec sp_trace_setevent @traceidout, 12,21, @on --SQL:BatchCompleted,EventSubClass
exec sp_trace_setevent @traceidout, 12,22, @on --SQL:BatchCompleted,ObjectID
exec sp_trace_setevent @traceidout, 12,23, @on --SQL:BatchCompleted,Success
exec sp_trace_setevent @traceidout, 12,24, @on --SQL:BatchCompleted,IndexID
exec sp_trace_setevent @traceidout, 12,25, @on --SQL:BatchCompleted,IntegerData
exec sp_trace_setevent @traceidout, 12,26, @on --SQL:BatchCompleted,ServerName
exec sp_trace_setevent @traceidout, 12,27, @on --SQL:BatchCompleted,EventClass
exec sp_trace_setevent @traceidout, 12,28, @on --SQL:BatchCompleted,ObjectType
exec sp_trace_setevent @traceidout, 12,29, @on --SQL:BatchCompleted,NestLevel
exec sp_trace_setevent @traceidout, 12,30, @on --SQL:BatchCompleted,State
exec sp_trace_setevent @traceidout, 12,31, @on --SQL:BatchCompleted,Error
exec sp_trace_setevent @traceidout, 12,32, @on --SQL:BatchCompleted,Mode
exec sp_trace_setevent @traceidout, 12,33, @on --SQL:BatchCompleted,Handle
exec sp_trace_setevent @traceidout, 12,34, @on --SQL:BatchCompleted,ObjectName
exec sp_trace_setevent @traceidout, 12,35, @on --SQL:BatchCompleted,DatabaseName
exec sp_trace_setevent @traceidout, 12,36, @on --SQL:BatchCompleted,FileName
exec sp_trace_setevent @traceidout, 12,37, @on --SQL:BatchCompleted,OwnerName
exec sp_trace_setevent @traceidout, 12,38, @on --SQL:BatchCompleted,RoleName
exec sp_trace_setevent @traceidout, 12,39, @on --SQL:BatchCompleted,TargetUserName
exec sp_trace_setevent @traceidout, 12,40, @on --SQL:BatchCompleted,DBUserName
exec sp_trace_setevent @traceidout, 12,41, @on --SQL:BatchCompleted,LoginSid
exec sp_trace_setevent @traceidout, 12,42, @on --SQL:BatchCompleted,TargetLoginName
exec sp_trace_setevent @traceidout, 12,43, @on --SQL:BatchCompleted,TargetLoginSid
exec sp_trace_setevent @traceidout, 12,44, @on --SQL:BatchCompleted,ColumnPermissions
exec sp_trace_setevent @traceidout, 12,45, @on --SQL:BatchCompleted,LinkedServerName
exec sp_trace_setevent @traceidout, 12,46, @on --SQL:BatchCompleted,ProviderName
exec sp_trace_setevent @traceidout, 12,47, @on --SQL:BatchCompleted,MethodName
exec sp_trace_setevent @traceidout, 12,48, @on --SQL:BatchCompleted,RowCounts
exec sp_trace_setevent @traceidout, 12,49, @on --SQL:BatchCompleted,RequestID
exec sp_trace_setevent @traceidout, 12,50, @on --SQL:BatchCompleted,XactSequence
exec sp_trace_setevent @traceidout, 12,51, @on --SQL:BatchCompleted,EventSequence
exec sp_trace_setevent @traceidout, 12,52, @on --SQL:BatchCompleted,BigintData1
exec sp_trace_setevent @traceidout, 12,53, @on --SQL:BatchCompleted,BigintData2
exec sp_trace_setevent @traceidout, 12,54, @on --SQL:BatchCompleted,GUID
exec sp_trace_setevent @traceidout, 12,55, @on --SQL:BatchCompleted,IntegerData2
exec sp_trace_setevent @traceidout, 12,56, @on --SQL:BatchCompleted,ObjectID2
exec sp_trace_setevent @traceidout, 12,57, @on --SQL:BatchCompleted,Type
exec sp_trace_setevent @traceidout, 12,58, @on --SQL:BatchCompleted,OwnerID
exec sp_trace_setevent @traceidout, 12,59, @on --SQL:BatchCompleted,ParentName
exec sp_trace_setevent @traceidout, 12,60, @on --SQL:BatchCompleted,IsSystem
exec sp_trace_setevent @traceidout, 12,61, @on --SQL:BatchCompleted,Offset
exec sp_trace_setevent @traceidout, 12,62, @on --SQL:BatchCompleted,SourceDatabaseID
exec sp_trace_setevent @traceidout, 12,63, @on --SQL:BatchCompleted,SqlHandle
exec sp_trace_setevent @traceidout, 12,64, @on --SQL:BatchCompleted,SessionLoginName
--turn on the trace
exec sp_trace_setstatus @traceidout, 1 ---start trace
--exec sp_trace_setstatus 2, 0 ---stop trace, you must know the traceid to stop it
--exec sp_trace_setstatus 2, 2 ---close trace you must know the traceid to delete it
IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name='sp_DMLTrace')
BEGIN
SET @sql = 'ALTER VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path +'.trc'', default)'
exec(@sql)
END
ELSE
BEGIN
SET @sql = 'CREATE VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'
exec(@sql)
END
COMMIT TRAN
END
Lowell
April 22, 2009 at 6:08 am
Thank You 🙂
April 22, 2009 at 6:17 am
It sounds to me like you're still trying to answer the main question for yourself, especially since in a lot of ways you're asking two different questions: what queries are being executed, and what data modifications are taking place.
How often do you need to know what queries are being executed- daily, only in specific circumstances, is there a specific triggering event? Depending on the load on your databases, any kind of logging may quickly overload you with too much information.
When data modifications are taking place, how often do you need to see what actually happened? Is there a legal requirement that must be satisfied? How long will you be required to keep any documentation of this kind?
There are lots of really good articles that talk about both of these topics; if I were you, I would first start by narrowing down what I was trying to accomplish by setting some very specific targets.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply