August 9, 2004 at 11:31 am
I need to dig out the time of insertion of a particular row of a particular table. Anyone having any idea how to do that.. Can we dig up some info from the SQL Logs of that particular database?
Regards,
Pranav
August 9, 2004 at 4:02 pm
You can buy a product to read and "beautify" the log info. Some are Lumigent Log Explorer, LockwoodTech Log Navigator, Coherent Software Log PI.
You can use the undocumented DBCC LOG.
Syntax:
DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])
where:
dbid or dbname - Enter either the dbid or the name of the database
type - is the type of output, and includes these options:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name, page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump of the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID
by default, type = 0
Greg
Greg
August 18, 2004 at 3:28 pm
Did you ever figure out how to get the TIME out of the log? I don't think it is available via the DBCC LOG command.
Thanks,
Francis
Francis
-----------------
SQLRanger.com
August 19, 2004 at 7:11 am
You will need to use the -1 option.
Look at the operation, of course all you want to do is find the one with LOP_INSERT_ROW as the operation on the table name as the Object Name you want to narrow to. If not too many rows then should be easy but the data is in binary which looks like hex.
In many case if you do like so.
DECLARE @x as VARBINARY(8000)
SET @x = 0x(generally use only the last 10 to 40 double character (so 20 to 80 characters but can be relational to the length of your row) values or you will always get 0 back)
PRINT cast(@x as varchar(8000))
This can help narrow it without too much detail needed.
Once found then use the Transaction ID value and look for the first row with an Operation of LOP_BEGIN_XACT and that Transaction ID, this row will have a column Begin Time which is the time the entire transaction started.
Next look for a row with the same transaction id and an Operaion of LOP_COMMIT_XACT, this will have an End Time value. So this is the entire length of time of the batch.
You can also use UID to find the person or if you know the person might help find the row if not generic.
The uid is the same as found in the sysusers records for that database.
If you have a lot of data to sift thru you could use DBCC PAGE to find the record and get it's varbinary value then search for that in the DBCC LOG out, but that is a lot to walk you thru.
Another factor that may help is use the record as it is and get a single char or varchar fields text and use
SELECT cast('string' as varbinary(8000))
and use that out to search thru the log output to help you find or at least significantly reduce the numbers to look thru.
Also you should note that only char data is in correct order but int's are in inverted order.
So say you have abcd then 61626364 will be in the varbinary value.
But if you have 1234 which would translate properly to 04D2 it is instead D204
Hope this helps.
August 19, 2004 at 7:17 am
This is excellent information! Thanks so much!
Francis
-----------------
SQLRanger.com
August 19, 2004 at 8:08 am
Very Informative. Thanks to everyone.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply