April 12, 2005 at 6:36 am
another newbie question, using pubs:
let's suppose I have maintenance plan working, which does full b/up once a day and txnlog b/up every 30 minutes or so. let's say a user enters data which corrupts a database which then needs to be recovered, but I want to rollback the portion of the txnlog that contains the corruption.
I would need to use STOPAT in my recovery step, but need the timestamp from the txnlog to know where to stop. by using DBCC Log (pubs, X) where x= -1,0,1,2,3,4, I get all sorts of stuff from the txnlog EXCEPT a timestamp. not being familiar enough to use LSN record, I need a simple timestamp, but do not know how to get that using DBCC Log.
Help!
April 13, 2005 at 4:20 am
Hi,
you might try fn_dblog, but it won't give you SQL-Statements...
fn_dblog returns a table of records from the transaction log.
The syntax of the call is:
select * from ::fn_dblog(@StartingLSN, @EndingLSN)
@StartingLSN and @EndingLSN are the start and ending
Log Sequence Numbers, also known as an LSN. A NULL argument for
the Starting LSN requests log records from the beginning of the
transaction log. A NULL value for the ending LSN requests
information to the end of the transaction log.
If you want to see the SQL-Stataments, you will have to use a 3rd party tool like LogExplorer...
karl
Best regards
karl
April 13, 2005 at 7:47 am
Karl:
using select * from ::fn_dblog(null, null), from Northwind, this gives timestamp for checkpoint end. is there a way to see the time stamps for all actions in the txnlog?
I'm not so much worried about viewing the actual TSQL statements (I know I'll need a utility for that), but would like to timestamp each event in the tnxlog, not just the checkpoints.
so far so good, thanx for the help!
April 13, 2005 at 10:38 am
select operation,[checkpoint end],[Begin time],[end time] from ::fn_dblog(null,null)
gives you the start and end times of every transaction that is in the active part of the ta-log
Best regards
karl
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply