We had some issues with locking on some tables and materialized view, so we enabled snapshot using this command: SET OPTION PUBLIC.allow_snapshot_isolation = 'On'; We're aware that this would effect the logfile.
Snapshot had no effect on our software, so we turned it off again and tuned our code instead.
But now the logfile keeps growing at an alarming pace: 6GB in the last 5 days, for a database at 10GB. We have to trunc it every 3 days to make sure we don't run out of diskspace. We also tried to run "Translate log file" to see what's going on, but on a 2GB logfile dbtran.exe returns a 179MB sql-file. So where's the rest of the log? All the checkpoints are there, and it looks very normal. The size of the sql-file makes me think that the logfile should be approx 200Mb, and not at all 2GB.
Before our test with snapshot, the logfile acted all normal an noone ever thought about it.
Could it be that snapshot is still active in some way? The option is off - checked it just now.
Do we have to restart the server?
Any other tips?
asked 07 Jun '13, 03:44
SELECT DB_PROPERTY ( 'SnapshotIsolationState' ) => 'Off'
SELECT DB_PROPERTY ( 'VersionStorePages' ) => 0
I used DBTRAN from Sybase Central with "Include uncommiited transactions" and "Include trigger-generated transactions", all checkpoints and all users. It results in this statement: dbtran -a "D:\Data\Leroy\maritech.log" "c:\temp\test.sql"
A colleague of mine tried the -g switch and got a 7GB sql-file from the 2GB logfile. Which sounds more normal.
SELECT DB_PROPERTY ( 'SnapshotIsolationState' );
reveal? It should return 'OFF' if all transactions that were active during the phase when snapshot isolation was set have finished in the meantime.
However, AFAIK, the row versions are not stored in the TL but in the temporary file, so snapshot isolation should not explain the TL growth at all.
You can use
SELECT DB_PROPERTY ( 'VersionStorePages' );
to check whether the temporary file does contain pages for row versions at all.
FWIW, what DBTRAN options did you use? There are a bunch of them to show otherwise "hidden" contents, say for auditing, replication, triggers, uncommited ops and the like. - Possibly the usage of them would reveal (much) more log contents?