Trace table inserts/updates
-
Hi,
I am planning to write a trace to capture the trace for inserts/updates happening in sql-server tables across a server. Which event i should use to capture that? I tried "Object:Opened" but it is not working as it is not capturing table inserts. Appreciate your help.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font] -
Try RPC Completed.
It will Give You sp_executesql 'INSERT ...'
And sp_executesql 'INSERT ...'
-
Baskar B.V (5/26/2010)
Hi,I am planning to write a trace to capture the trace for inserts/updates happening in sql-server tables across a server. Which event i should use to capture that? I tried "Object:Opened" but it is not working as it is not capturing table inserts. Appreciate your help.
Baskar take a look at this trace example for capturing all DML activity; i use it a lot, creating a scheduled job to create this trace on SQL Server startup (thanks to homebrew's idea).
you could add some filters to it in order to limit it's scope if need be, but it sure is handy to have a trace in place on everything for those inevitable "who deleted everything from my table" issues, as well as the "did Bob do any work after business hours" kinds of traces as well.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi,
I developed similar to one below.... but it not writing anything.. please let me know what is wrong in here?
--------------------
/****************************************************/
/* Created by: SQL Server Profiler */
/* Date: 05/26/2010 04:43:28 PM */
/****************************************************/
-- 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, 0, N'\\server\folder\Trace', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
-- SQL Server Yukon specific events will not be scripted
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 48, 12, @on
exec sp_trace_setevent @TraceID, 48, 28, @on
exec sp_trace_setevent @TraceID, 48, 9, @on
exec sp_trace_setevent @TraceID, 48, 6, @on
exec sp_trace_setevent @TraceID, 48, 10, @on
exec sp_trace_setevent @TraceID, 48, 14, @on
exec sp_trace_setevent @TraceID, 48, 22, @on
exec sp_trace_setevent @TraceID, 48, 34, @on
exec sp_trace_setevent @TraceID, 48, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%Insert%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 8a29674b-20d0-46d6-b110-fdc293ab916a'
-- 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
-------------------
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font] -
what value did you put in for the path to the file, N'\\server\folder\Trace',
if it is not a local directory, but a UNC path, you might have problems depending on what account is being used for the startup or proxy accounts.
when you access any resource OUTSIDE of SQL server, like network shares, local hard drives,xp_cmdshell,sp_OA type functions etc, it doesn't matter what YOUR credentials are, like Domain Admin,Local Admin etc, because SQL will not carry those credentials to the "outside of SQL" security context.
SQL Server uses the account it starts with to try and access the resource:
That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.
As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.
Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.
you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the linked server works when SQL is run your credentials, so you'd know you need a domain account to access the resource.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The trace file got created successfully but it showing only "0" bytes. I am assuming the process has access since it has created the trace file in the network share which is UNC path.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font] -
Baskar B.V (5/27/2010)
The trace file got created successfully but it showing only "0" bytes. I am assuming the process has access since it has created the trace file in the network share which is UNC path.That's fairly normal, I think--it won't show its true size until you stop the trace, because it's caching in memory.
-
Yes. Thanks Paul.
I would like the automated script to run 24/7 and hope it would not block the memory if it going to keep it in cache.
How to program such that to write the output trace into the file every 1 hour or so?
Appreciate your help.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply