November 11, 2010 at 6:48 am
nealhudson77 (11/11/2010)
I wrongly assumed that turning on transactino logging would be enough. ....
As Gail mentioned earlier, Transaction log is not an optional component and you cannot turn it on or off. It is present for all databases. Behaviour inside transaction log may differ based on recovery model.
November 11, 2010 at 6:50 am
nealhudson77 (11/11/2010)
I wrongly assumed that turning on transactino logging would be enough.
What do you mean by 'turning on transaction logging'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2010 at 6:54 am
Ok I'm no SQL Server expert - I'm the developer.
The last time the issue happened, I asked the Sys Admin / DBA person for the relevant SQL log files so I look into what was happening. I was told that the transaction logging wasn't turned on, so we didn't have any.
The upshot was, I was told, that the transaction logging would be turned on so we would have the relevant data next time the issue happened. Turns out now that is not the case.
November 11, 2010 at 7:15 am
Do you reckon using that Apex tool I'd be able to narrow it down as to when the records got deleted?
November 11, 2010 at 7:16 am
It's not transaction logging that you want.
SQL logs all transactions, for database integrity and recoverability. Depending on the recovery model those stay in the transaction log for different amounts of time. On full recovery you need to be taking log backups to allow the log to be reused. The reason for transaction log backups is so that you can recover a server to point-in-time in case of a failure.
If you want auditing, you need some form of audit log. SQL 2005 is limited in what it offers here. You can run a server-side trace, but that's going to be a lot of space if it runs for long periods (that's what the C2 trace is essentially). You can put triggers onto the specific tables and write your own auditing. That's pretty much that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2010 at 7:18 am
That Apex tool only works on SQL 2000 🙁
November 11, 2010 at 7:19 am
GilaMonster (11/11/2010)
It's not transaction logging that you want.SQL logs all transactions, for database integrity and recoverability. Depending on the recovery model those stay in the transaction log for different amounts of time. On full recovery you need to be taking log backups to allow the log to be reused. The reason for transaction log backups is so that you can recover a server to point-in-time in case of a failure.
If you want auditing, you need some form of audit log. SQL 2005 is limited in what it offers here. You can run a server-side trace, but that's going to be a lot of space if it runs for long periods (that's what the C2 trace is essentially). You can put triggers onto the specific tables and write your own auditing. That's pretty much that.
Would 2008 offer more?
November 11, 2010 at 7:22 am
nealhudson77 (11/11/2010)
That Apex tool only works on SQL 2000 🙁
No it doesn't.
http://www.apexsql.com/sql_tools_log.aspx
Support for SQL Server versions 2000, 2005, 2008, 2008 R2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2010 at 7:23 am
nealhudson77 (11/11/2010)
Would 2008 offer more?
Yes, it has more options available. You would still have to set them up though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2010 at 7:29 am
Would the transactions in question still be in the log after a transaction log backup?
November 11, 2010 at 7:32 am
jpertell (11/11/2010)
Would the transactions in question still be in the log after a transaction log backup?
Maybe.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2010 at 7:37 am
GilaMonster (11/11/2010)
nealhudson77 (11/11/2010)
That Apex tool only works on SQL 2000 🙁No it doesn't.
http://www.apexsql.com/sql_tools_log.aspx
Support for SQL Server versions 2000, 2005, 2008, 2008 R2
Apologies, my bad
November 11, 2010 at 8:09 am
The Apex tool isn't giving me much...
Ok what about this:
I restore the first backup (.bak @ 2am)
I turn on audit tracing
I restore the 4 .trn files
What would that give me?
November 11, 2010 at 8:22 am
An trace file with 4 RESTORE LOG events in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2010 at 8:36 am
nealhudson77 (11/11/2010)
The Apex tool isn't giving me much...Ok what about this:
I restore the first backup (.bak @ 2am)
I turn on audit tracing
I restore the 4 .trn files
What would that give me?
You wont be able to find information who deleted records by restoring the backup files. It doesnt replay each transaction on behalf of the user who performed the transaction. As Gail said, the trace will have log restores because that was the action performed by the user (you).
If you want to track this in future, you need to have some sort of auditing tool running which captures these information or as Gail said, you can create a trigger on those important tables which will log information in another table whenever user performs DML operation.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply