Find the liar in the SQL Server 2005

[eggy168]

Hi,

I don't know is it possible or not. My users always lie to me about the database is not working and bla bla. They always claim the database didn't save their works, so they lost data. I am speechless because I can't find the evident.

I would like to know is there method(s)in the SQL Server 2005 to find who is doing what on a table with a time frame? I know I can use sp_who to find who is in the database instant. However, I think it is somewhat useless because the sp_who is dealing with a database level. If I want to know who is in what table, the sp_who can't help.

Anyhow, my questions are can anyone helps me or guide me to find the following if it is possible?

1) Each user in the database is doing what in what table? For instance, User A was updating a record in Table A on what time and what date
2) Can I find which user in in what table at the moment?

Thank you very much.

 

[dgillz]

This is all in the transaction log file. How to read it, I have no idea.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[macleod1021]

There might be a better way, but what I always did was added a LastModified column to the table. Then I would write a trigger that would update that column to current date/time whenever there was an update done. You could extend that a bit further and add a user update.

The downside to that is if you have someone update...then someone else update, you lost your info.

IF I needed to keep a running log, then I set up an Activity Table with the information that I wanted, and modified the trigger to update that table.

im in ur stakz, overflowin ur heapz!

 

[eggy168]

I tried to look at the log and as dgillz said, I have no idea how to read the log as well.

macledod1021, I setted up the update date/time column in each table, but as you said there are too many users updated the same records, and I lost track. However, I didn't set up a column for an updated user. I am going to do it today and hopefully I will have some evidents later on.

thank you for all your helps.

 

[ptheriault]

You can also read the Tlog with the following command.

DBCC LOG(db_name, 3)

Also,
There is a great tool by lumigent called log explorer. It can read the log and recreate transactions. So if some idiot does delete data you can undo the transaction.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[eggy168]

The dbcc log(db_name, 3) has so many information that I don't know how to make those useful. However, I will keep searching on it.

I am going to check the tool, log explorer and I hope it will help to solve the problem.

Thank you very much.

 

[SQLDenis]

use a product like Lumigent Log Explorer

http://www.ssw.com.au/SSW/LogExplorer/

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions