Hi -
does any one have idea that how to stop Transaction log gets Truncated.
Thanks,
Baddy

---

Thanks,
Baddy

Baddy,
Can you explain a bit more what the problem is. What sort of recovery model are you using? What sort of backup regime have you got in place? What is the setting of TRUNCATE LOG ON CHECKPOINT?

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

Any suggestion?

---

Thanks,
Baddy

Baddy,

I'll try to tackle your questions.

1) Is there anyway to read the inactive part of transaction file? Is there anyway to read truncated log file? We are reading data from Transaction log file using DBCC log('DB_Name',4)

Both DBCC Log and fn_dblog() only show the "active" tail portion of the Transaction Log. As far as I know there isn't a command to view the "inactive" potion; you'd have to have a 3rd party tool for that. The data isn't really truncated it's marked inactive and another section of the Tlog is presented for use, which explains why the log continues to grow, but you only see a portion of the records.

2) Can you please suggest me how can I stop this truncation?

You can delay the "active" portion from moving to inactive by having an uncommitted transaction in the database. While this isn't recommended in a production environment for your test database it should be fine. To achieve this create a “dummy” table and do an update on a field in it, then open another connection and do the same update. This will create a blocking scenario on this table only, the rest of the tables, transactions, etc. will work just fine. However the TLOG will wait until all transactions are committed and delay moving to a new section. You should be able to verify this with the test that you’ve outlined.

Cheers,

Kraig Kerr