CODEWITHSUNDEEP
sql-server-2008transactionstrace
Azimuth
Azimuth

Reputation: 2698

SQL Server : track table inserts

I have a table which get new data inserted every minute or so. According to the source code I have here, it is only done in one class which is not used anymore.

Is there any way to trace the inserts? What I mean is to see which queries they were inserted by, who sent those queries etc. As much info as possible.

I have tried several ways myself (e.g.sp_who2 'Active' stored procedure) without any success. I also have access to the machine running the SQL server and to the transaction backup files (.trn files) but have no idea how to open those files.

Upvotes: 2

Views: 3434

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21766

Add trigger to the table which follows inserts and insert to other table these variables:

getdate(),
host_name(),
App_Name(),
suser_sname()

Seems to me that this is enough

The trigger looks like this:

CREATE TRIGGER YourTrigger On YourTable
AFTER INSERT
AS
SET NOCOUNT ON;

   INSERT logtable
   SELECT APP_NAME(), HOST_NAME(), SUSER_SNAME(), GETDATE(), * FROM INSERTED
GO

OR

you can use Sql Server Profiler for catching the queries - it may be more flexible

Upvotes: 3

aF.
aF.

Reputation: 66697

You may use sp_depends like this:

sp_depends tablename

This only states information in the same database but it might say what you need!

Upvotes: 0

Related Questions