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

asked 07 Jun '13, 03:44

Bjarne%20Anker's gravatar image

Bjarne Anker
440141729
accept rate: 0%

What does SELECT @@VERSION return?

(07 Jun '13, 05:00) Breck Carter

SELECT @@VERSION => 12.0.1.3726

(07 Jun '13, 05:06) Bjarne Anker

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.

permanent link

answered 07 Jun '13, 04:24

Bjarne%20Anker's gravatar image

Bjarne Anker
440141729
accept rate: 0%

edited 07 Jun '13, 05:13

Breck%20Carter's gravatar image

Breck Carter
25.7k427586845

1

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

(07 Jun '13, 06:33) John Smirnios
Replies hidden

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"

(07 Jun '13, 08:26) Breck Carter

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

(07 Jun '13, 09:06) Volker Barth

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?

permanent link

answered 07 Jun '13, 04:06

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 07 Jun '13, 04:09

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

(07 Jun '13, 05:10) Breck Carter
Replies hidden

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.

(07 Jun '13, 05:19) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409
×31
×10
×5

question asked: 07 Jun '13, 03:44

question was seen: 983 times

last updated: 07 Jun '13, 09:06