CODEWITHSUNDEEP
sqlsql-server-2008
Vivian River
Vivian River

Reputation: 32430

How to figure out why rows are disappearing from my SQL server?

I'm working with a SQL Server 2008 installation that was maintained for years by another team of programmers.

I'm having a problem that rows of data seem to be mysteriously disappearing from a specific table in my server.

I would like to be able to set up some sort of monitoring system that would tell me when the table is modified, and a summary of the modification.

I think that "triggers" might be what I'm looking for, but I've never used them before. Are triggers what I want to use, and if so, what is a good resource for learning to use them? Is there a better solution?

I think that I should mention that the table I'm referring to is not that frequently updated, so I don't think that adding a little bit of overhead should be a big deal, but I would prefer a solution that I can brush away once the problem is resolved.

Upvotes: 0

Views: 2721

Answers (4)

DeadZone
DeadZone

Reputation: 1690

The following sample should be a basic idea of what you're looking for.

CREATE TABLE MyTestTable(col1 int, col2 varchar(10));
GO

CREATE TABLE MyLogTable(col1 int, col2 varchar(10), ModDate datetime, ModBy varchar(50));
GO

CREATE TRIGGER tr_MyTestTable_IO_UD ON MyTestTable AFTER UPDATE, DELETE
AS
    INSERT MyLogTable
    SELECT col1, col2, GETDATE(), SUSER_SNAME()
    FROM   deleted;
GO

Insert MyTestTable Values (1, 'aaaaa');
Insert MyTestTable Values (2, 'bbbbb');
UPDATE MyTestTable Set col2 = 'bbbcc' WHERE col1 = 2;
DELETE MyTestTable;
GO

SELECT * FROM MyLogTable;
GO

However, keep in mind that there are still ways of deleting records that won't be caught by a trigger. (TRUNCATE TABLE and various bulk update commands.)

Upvotes: 2

BlakeH
BlakeH

Reputation: 3494

A FOR DELETE trigger could help you capture the rows that are being deleted. You could create an audit table (copy of the table that you'd like to monitor) and then add this code to your trigger:

INSERT INTO [Your Audit Table]
SELECT * FROM deleted

I've also seen some "more advanced" scenarios involving FOR XML.

I don't know that the trigger would help determine who is deleting the records, but you might be able to PROVE that the records are being deleted, and perhaps what time, etc. That could help you troubleshoot further.

Upvotes: 2

Marc
Marc

Reputation: 16522

Another solution would be to attach Sql Profiler to the database with specific conditions. This will log every query run for your inspection.

I like to stay away from triggers but they could help for your problem like Draghon said

Upvotes: 1

Draghon
Draghon

Reputation: 367

I think you have it figured out. A trigger is likely your best bet as it's as close to the data as you can get. Inspecting the code (programming or even a stored procedure) would not give you as much an assurance as a trigger would; a Delete trigger in this case.

Check out this article: http://www.go4expert.com/forums/showthread.php?t=15510

Upvotes: 0

Related Questions