Question Re: Isolation level snapshot in Technology Q&A

Isolation level snapshot

Former Member — Fri, 07 Jun 2013 03:44:37 GMT

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

Re: Isolation level snapshot

VolkerBarth — Fri, 07 Jun 2013 04:06:00 GMT

What does

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?

Re: Isolation level snapshot

Former Member — Fri, 07 Jun 2013 04:24:42 GMT

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.

Re: Isolation level snapshot

Breck_Carter — Fri, 07 Jun 2013 05:00:02 GMT

What does SELECT @@VERSION return?

Re: Isolation level snapshot

Former Member — Fri, 07 Jun 2013 05:06:45 GMT

SELECT @@VERSION => 12.0.1.3726

Re: Isolation level snapshot

Breck_Carter — Fri, 07 Jun 2013 05:10:38 GMT

"row versions are not stored in the TL" ... even from first principles, that statement rings true. During normal operations the transaction log is a sequential write-only file, it would kill disk performance if the server forced the drive to do random I/O. Recovery mode, and the MobiLink client and SQL Remote processes, that's when the log is read, but even those are pretty much sequential AFAIK.

Re: Isolation level snapshot

VolkerBarth — Fri, 07 Jun 2013 05:19:28 GMT

Well, it's also officially documented:

Row versions

When snapshot isolation is enabled for a database, each time a row is updated, the database server adds a copy of the original row to the version stored in the temporary file.

Re: Isolation level snapshot

johnsmirnios — Fri, 07 Jun 2013 06:33:03 GMT

-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.

Re: Isolation level snapshot

Breck_Carter — Fri, 07 Jun 2013 08:26:58 GMT

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"

Re: Isolation level snapshot

VolkerBarth — Fri, 07 Jun 2013 09:06:52 GMT

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...