December 10, 2008 at 8:34 pm
I have a small query .
Is it possible to read the transaction log file or is there any specific way to read the transaction log.
Suppose i want to know the exact transactions along with the exact users who performed the operations that happened during a particulartime .Is there any way to look at the transaction log and know the transactions.
Thanks
Sandhya
December 10, 2008 at 9:13 pm
ApexSQL has a log tool that will read the 2005 transaction log. You can purchase this, but I'm not sure it shows who performed the transaction. The transaction log isn't an audit tool, it's a record of data changes.
There is a default trace in 2005 and if it hasn't been too long, you might be able to use it.
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
December 11, 2008 at 1:00 am
sandhyarao49 (12/10/2008)
Is it possible to read the transaction log file or is there any specific way to read the transaction log.
Sure. SELECT * from fn_dblog(null, null)
Don't expect it to be easy to read or interpret. The tran log's not mean to be readable
Suppose i want to know the exact transactions along with the exact users who performed the operations that happened during a particulartime
Use audit triggers or run profiler. The transaction log is not an audit log. It's there for database integrity and for recoverability.
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
December 11, 2008 at 4:37 am
Hi,
You can get transaction id and operation type only (no data) through a select * from syslogs.
Use the following code to make it a bit more readable.
SELECT
xactid AS TRAN_ID,
CASE op
WHEN 0 THEN 'BEGINXACT Start Transaction'
WHEN 1 THEN 'Sysindexes Change'
WHEN 2 THEN 'Not Used'
WHEN 3 THEN 'Not Used'
WHEN 4 THEN 'INSERT Insert Row'
WHEN 5 THEN 'DELETE Delete Row'
WHEN 6 THEN 'INSIND Deferred Update step 2 insert record'
WHEN 7 THEN 'IINSERT NC Index Insert'
WHEN 8 THEN 'IDELETE NC Index Delete'
WHEN 9 THEN 'MODIFY Modify Row'
WHEN 10 THEN 'NOOP'
WHEN 11 THEN 'INOOP Deferred Update step 1 insert record'
WHEN 12 THEN 'DNOOP Deferred Update step 1 delete record'
WHEN 13 THEN 'ALLOC Allocate Page'
WHEN 14 THEN 'DBNEXTID Allocate Next Object ID'
WHEN 15 THEN 'EXTENT Allocate Empty Extent'
WHEN 16 THEN 'SPLIT Page split'
WHEN 17 THEN 'CHECKPOINT'
WHEN 18 THEN 'SAVEXACT Savepoint'
WHEN 19 THEN 'CMD'
WHEN 20 THEN 'DEXTENT Deallocate extent'
WHEN 21 THEN 'DEALLOC Deallocate page'
WHEN 22 THEN 'DROPEXTS Delete all extents on alloc pg'
WHEN 23 THEN 'AEXTENT Alloc extent - mark all pgs used'
WHEN 24 THEN 'SALLOC Alloc new page for split'
WHEN 25 THEN 'Change to Sysindexes'
WHEN 26 THEN 'Not Used'
WHEN 27 THEN 'SORT Sort allocations'
WHEN 28 THEN 'SODEALLOC Related to sort allocations'
WHEN 29 THEN 'ALTDB Alter database record'
WHEN 30 THEN 'ENDXACT End Transaction'
WHEN 31 THEN 'SORTTS Related to sort allocations'
WHEN 32 THEN 'TEXT Log record of direct TEXT insert'
WHEN 33 THEN 'INOOPTEXT Log record for deferred TEXT insert'
WHEN 34 THEN 'DNOOPTEXT Log record for deferred TEXT delete'
WHEN 35 THEN 'INSINDTEXT Indirrect insert log record'
WHEN 36 THEN 'TEXTDELETE Delete text log record'
WHEN 37 THEN 'SORTEDSPLIT Used for sorted splits'
WHEN 38 THEN 'CHGINDSTAT Incremental sysindexes stat changes'
WHEN 39 THEN 'CHGINDPG Direct change to sysindexes'
WHEN 40 THEN 'TXTPTR Info log row WHEN retrieving TEXTPTR'
WHEN 41 THEN 'TEXTINFO Info log for WRITETEXT/UPDATETEXT'
WHEN 42 THEN 'RESETIDENT Used WHEN a truncate table resets an identity value'
WHEN 43 THEN 'UNDO Compensating log record for Insert Only Row Locking (IORL)'
WHEN 44 THEN 'INSERT_IORL Insert with Row Locking record'
WHEN 45 THEN 'INSIND_IORL INSIND with IORL'
WHEN 46 THEN 'IINSERT_IORL IINDEX with IORL'
WHEN 47 THEN 'SPLIT_IORL Page split with IORL'
WHEN 48 THEN 'SALLOC_IORL Alloc new page for split with IORL'
WHEN 49 THEN 'ALLOC_IORL Allocation with IORL'
WHEN 50 THEN 'PREALLOCLOG Pre-allocate log space for CLRs'
ELSE 'Unknown Type' END AS LOG_RECORD
FROM syslogs
2. dbcc log command. Not well documented, but some details below for SQL. Note that as with most undocumented dbcc commands you need to do a dbcc traceon(3604) first to see the output.
3. 3rd party. Logview from http://www.dbsg.com.
4. 3rd party. Image Analyzer from http://www.platinum.com. This product also allows extracting data and SQL statements from the log.
---------------------------------------
dbcc log [ (@dbid, @objid, @pagenum, @rownum, @records, @type [, @printopt]) ]
dbcc log (5, 0, 0, 0, -1, 0, 1) // Show the last begin transaction record in the log
December 14, 2008 at 10:08 pm
[font="Verdana"]Wasn't syslogs removed in SQL Server 2000?[/font]
December 15, 2008 at 8:50 am
Not sure when it was removed, but it's not in 2005 for sure.
December 15, 2008 at 8:58 am
I do remember that syslog existed as a table in SQL Server 6.5. If I remember correctly it was removed with SQL Server 7.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 15, 2008 at 10:04 am
True as it was removed from SQL Server 7.0 onwards. The query to syslog was changed with DBCC command. Sorry about my previous post on this subject.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply