Isolation level snapshot

[Breck_Carter]

Hi.

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?

Regards,

Bjarne

[Breck_Carter]

What does SELECT @@VERSION return?

[Former Member]

SELECT @@VERSION => 12.0.1.3726

[Former Member]

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.

[johnsmirnios]

-g enables "-a" but also shows audit records and trigger-generated actions. Since -g changes your SQL output to 7GB compared with 179MB with "-a" alone, you either have auditing enabled or some very busy triggers.

[Breck_Carter]

I'm voting for Characteristic Error Number 24, perhaps applied to an UPDATE: "Omitting a PRIMARY KEY column from the WHERE clause, thus turning a singleton SELECT (or DELETE!) into something rather more enthusiastic than expected"

[VolkerBarth]

But a multi-row UPDATE or DELETE would be logged as lots of single-row operations, wouldn't it? (If so, that would not explain the log growth IMHO).

At least that's what I've learned from translating logs for SQL Remote affairs...