Phil Hi - I'm using SQL 2000 & executed some DML (create Tables)/DDL (insert
Records) statements on Database & noticed the very strange behavior of Transaction log.
Lastly it observed the number of rows in Transaction log is 2958 And now the total number of rows in Transaction log is 458 It seems that transaction log is truncated.
I did following steps >>
1) Created DB from the Enterprise Manager & here is the default options
details:
Recovery:
Model - Full
Setting:
Auto update statistics - True
Auto Create Statics - True
Torn page detection - True
Other options are false like Auto shrink...auto close....
2) Created some Tables in DB
3) Inserted records into these tables.
4) Checked number of records in a Transaction log by using DBCC Log ('DB_Name', 4) - previously it was 2958
5) Inserted more records into tables.
6) Checked number of records in a Transaction log & it is 458
I know SQL Server truncates the inactive part of the Transaction log & saved it backup somewhere on disk.
can you please suggest me how can I stop this truncation or capture this event or read Inactive Transaction Log data or Backup Transaction log Data.
can we know what triggers the transaction file truncation and control when it happens?
Are we reading logical Transaction log or Primary, When we are reading data from Transaction log file using DBCC log('DB_Name',4)
During truncation logical log file is truncated or physically file?
is there anyway to read the inactive part of transaction file?
is there anyway to read truncated log file?
I observed the same behavior in SQL 2005 too.
Thanks,
Baddy