Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have had three incidents in recent months where records in a table that have either been deleted or values updated to zero across a whole table. We have a team of four people who have the permission and who are responsible for updating the database who could have done this. Disappointingly no one has admitted to carrying out the changes.

Going forward I would like to be able to have a record of these transactions. I was wondering what do other people use to track these changes? Do they use software that tracks changes or do you create stored procedures or trace files? If anyone has this set up at their facility I would like to know what they use. The trace files do have the information I am after such as login name machine number and the sql statement so it will give me the information if I set them up in advance.

I have copies of the database and the transaction logs when these changes took place. Is there anything I can do with these old files to help track down the culprit? Thanks in advance to anyone who replies. We are using SQL server 2005.

share|improve this question
1  
Please only use the meta site (meta.dba.stackexchange.com) for discussions about the site itself. Any technical database question needs to come to the main site. –  JNK Nov 7 '12 at 19:46
    
do you have any idea as to when the change took place? –  swasheck Nov 7 '12 at 19:52
2  
Paul Randal helped write fn_dblog which might help you. sqlskills.com/blogs/paul/post/… –  swasheck Nov 7 '12 at 20:00
    
Per chance do you know when it happened? That would help a lot. We could do point in time restores to get the moment it happened, then looking through the transaction log would be a lot easier, but I don't think it records the host name. You could look at your default trace if it still has the info as well to find the host name that carried out the operation. Default trace will tell you the type of operation if they had sysadmin rights. –  Ali Razeghi Nov 7 '12 at 23:34
1  
I have a script posted on my blog called Dump Transaction Log Backup that uses fn_dump_dblog -- I had to use it today at work, in fact. The output of my script is more refined than the queries in Paul Randal's post, which basically just demonstrates what the function does. Edit: you'll need to modify it a bit to work with 2005 syntax, though. Sorry. –  Jon Seigel Nov 8 '12 at 3:21
add comment

migrated from meta.dba.stackexchange.com Nov 7 '12 at 19:45

This question came from our discussion, support, and feature requests site for database professionals who wish to improve their database skills and learn from others in the community.

2 Answers

up vote 3 down vote accepted

fn_dblog is the way to look backwards as the other commentators have said.

Only allowing the users to modify the data through stored procedures is a great way to prevent this happening in the first place, as you can add logic to prevent users from mass modifying records they shouldn't, or ensuring that they have to provide correct values for updates. This may mean that you'd need to make application changes depending on how they're currently accessing the data. Which may or may not be possible for you.

If you can't do that, then a quick and simple way with SQL 2005 is going to be using a trigger to do some DML auditing at the table level. (SQL Server 2008 onwards have auditing tools built in).

A simple solution for your issue might be:


drop table audit_test
go
create table audit
(
uname varchar(50),
[date] datetime,
what nvarchar(4000),
host varchar(50),
)
go 

create trigger ddlcheck on tbl_example 
for update, delete
as
declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp

This will fire any time a query attempts to update the table, or delete from the table. It uses DBCC inputbuffer ( http://msdn.microsoft.com/en-us/library/ms187730(v=sql.90).aspx ) to get the issued command. This gives you a table populated with all the update and delete statements issued against a particular table. Plus it records who issued statement, and where and when it was.

Now this could be a lot of logging data on a busy table, so this could be restricted to just catch 'bad' queries (say those that update/delete 1000+ rows) by altering the trigger to:


create trigger ddlcheck on tbl_example 
for update, delete
as
declare @cnt integer
select @cnt=count(1) from deleted
if @cnt>1000
begin
  declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
  insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
  insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp
end

This logs less data, but the trigger still needs to 'evaluate' for every operation, so may have a performance impact which will need to be measured and tested. This will also miss the actions if the user submits lots of individual statements, ie;

won't catch:


delete from tbl_example where id=1
delete from tbl_example where id=2
.....
delete from tbl_exampe where id=1000

will catch


delete from tbl_example where id>0 and id<1001

Hope this is of some help for the future.

share|improve this answer
    
Thanks for all the comments guys! Yes I do know roughly when the changes was carried out. I have narrowed it down to a time frame of about half an hour. –  kdis Nov 12 '12 at 12:55
add comment

We use ApexSQL Log for auditing our production databases. It can show you who and when has deleted or updated the records. It also tracks inserts and Create/alter/drop statements for data objects.

It's good that you have copies of the database and the transaction logs when these changes took place, as ApexSQL Log can read these transactions from the transaction logs. If you plan to use it for tracking changes in future, your databases have to be in the full recovery model, as only then you can be sure that the online transaction log or a transaction log backup contains all the transactions you want to read. We compress our transaction log backups to save space

We have a nightly job scheduled to read the transaction log backups for the last 24 hours and export the transactions into a HTML file. If we need any info, I check the file and don't worry if the transaction log backups are deleted (we delete them after 30 days)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.