asked on

SQL Server logs - how to find out what happened and when

Hi,

One of our production databases has been altered over the weekend. In particular a table containing user data has been updated across the board, altering usernames. This should not have happened and we are trying to find out when it happened and who did it.

Our regular DBA is not here and although I do SQL Server DB development I'm not sure what can be done in this situation. It seems like the database and transaction logs have only been retained for the past two days so the oldest available backup includes the unwanted changes committed.

Is there any way to find out when these updates occurred and under what credentials this happened?

Thanks.

gplana

Do you mean error logs ?

Please see this link: http://msdn.microsoft.com/en-us/library/ms187885.aspx

irb56

ASKER

Thanks for the link but these are not errors we're trying to identify, they are committed update transactions on a particular table. I've done a bit of searching, which so far has lead me to download a trial of a log reader named ApexSQL Log. I believe this is supposed to allow you to audit database changes, including seeing details of the transaction(s) that updated the table in question and the associated login info, etc.

However, I am unsure what the limitations of such a tool will be. How far back can you go in the auditing?

ASKER CERTIFIED SOLUTION

gplana

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

irb56

ASKER

It looks like the ApexSQL Log tool is excellent. As well as identifying the details of every change, it provides a facility for automatically generating scripts to reverse the change.

I assume there are other SQL Server log reader tools out there. Does anyone have any recommendations, particularly if there are any useful products that don't cost as much as ApexSQL?

Also I am curious regarding the limits of a log reader tool. How is it affected by the database recovery model and the frequency and types of backup? For example, if a database has the full recovery model and both database and log file gets backed up, doesn't this truncate the log file and would this mean that those transactions truncated from the log file would then become unavailable to the SQL log reader tool?

Ramesh Babu Vavilla

use xp_readerrorlog command in the management studio, you will get the error log

Anthony Perkins

>>use xp_readerrorlog command in the management studio, you will get the error log<<
Pray tell what in the world has that got to do with the Transaction Log?

irb56

ASKER

Thanks for your help. :-)