CODEWITHSUNDEEP
sqlsql-server-2005
MadMax
MadMax

Reputation: 23

Find details of a query or statement that caused an unexpected table update

We have been having problems with ghost updates in our DB (SQL Server 2005). Fields are changing, and we cannot find the routine that is performing the update.

Is there any way, (perhaps using an update trigger ?) to determine what caused the update? The SQL statement, process, username/login,etc?

Upvotes: 1

Views: 77

Answers (4)

Cade Roux
Cade Roux

Reputation: 89671

I would probably use AutoAudit to generate triggers on the table first.

It's somewhat limited in terms of knowing exactly what is changing your data, but it's a start.

You could always look at the triggers and modify them to only log certain columns you are interested in and perhaps get more information which it doesn't currently log.

Upvotes: 0

cdonner
cdonner

Reputation: 37668

All the information that you are asking for is available at the time the update is performed. The SQL Profiler will certainly work, but it is a bit of work to craft a filter that does not overwhelm you with data, particularly if you need to run it for days or weeks at a time. An update trigger is easy enough the create, and you can log the information that you need in a new table.

Upvotes: 1

Don
Don

Reputation: 9661

Use SQL Server Profiler

You'll probably want to filter away the things you don't need so it might take a while to get it setup.

At least it'll get you to the procedure / query that is responsible as well as user / computer for the alterations, which leaves finding that in your code.

Upvotes: 3

Russ Clarke
Russ Clarke

Reputation: 17909

I found and article that might help you out over here:

http://aspadvice.com/blogs/andrewmooney/archive/2007/08/20/SQL-Server-2005-Audit-Log-Using-Triggers.aspx

Upvotes: 1

Related Questions