I got an Assertion failed: 201200 "Did not expect to continue row segment" on a production database.

I can't find any information about it. Can you help me?

Now my db is ok, and I'm running a validate on it.

I'm running SA 11.0.1 2808 on windows 2003 R2 x64.

asked 20 Jun '12, 16:13

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%

edited 21 Jun '12, 02:47

Volker%20Barth's gravatar image

Volker Barth
30.0k294447654


Depending on your situation, you may want to consider turning on checksums in order to flag disk page corruption more quickly, since the server will assert as soon as it reads a page from disk where the page checksum doesn't match the page contents.

It is impossible to know what the initial cause of the problem is. I can tell you that we frequently see corrupt databases from customers where the databases are kept on commodity hardware. The first thing I would do is not only validate your current database copy, but in additional shutdown the server and copy fhe database files to another location and attempt an unload to verify that you can recover all your data.

permanent link

answered 20 Jun '12, 19:12

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

Mr Glenn, I did unload of struture and data with success and after that full valitate with no errors. Can I trust this database or I need to create a new one? It's about 150gb...

(21 Jun '12, 08:45) Zote
Replies hidden

If VALIDATE ran successfully then the database is very likely just fine. I would consider using checksums to try to catch a media failure as quickly as possible so that, hopefully, any corruption that may occur will affect as few pages as possible.

I am aware of an outstanding issue that can raise assertion 201200 - the issue pertains (only) to RECURSIVE UNION queries where during the recursion strings are continuously concatenated, eventually exceeding the query's memory quota. If your application issues RECURSIVE UNION queries then this might be the cause of the problem, but otherwise I would assume the cause is transient disk or memory failure.

(21 Jun '12, 09:19) Glenn Paulley

Should I activate checksum using alter database or dbeng switch ?

(21 Jun '12, 15:32) Zote
2

I would use the ALTER DATABASE statement. Make sure you have a backup of your files first, and that you can re-create a database from that backup, before you proceed any further.

(21 Jun '12, 15:47) Glenn Paulley

I got this error again! Same database, but now running on SA12.0.1.3750 under Windows 2008.

This night I'll do copy and validation.

(10 Dec '12, 07:22) Zote

Sometimes RAM errors cause symptoms that look like disk errors. Remember, EVERYTHING goes through RAM... SQL Anywhere doesn't actually see data on the disk, it only sees data after it's been copied to RAM by the O/S, BIOS, device drivers and disk adapters.

RAM errors are particularly evil since continued use of bad RAM can CAUSE true data errors, and can in some cases cause complete destruction of data even though the physical disk drive is flawless.

If you suspect a RAM error, stop using the database immediately and run a full diagnosis of the RAM. With commodity hardware, it's often easier/cheaper just to swap out the RAM, or swap computers.

permanent link

answered 21 Jun '12, 09:02

Breck%20Carter's gravatar image

Breck Carter
27.4k424585836
accept rate: 21%

Hi Breck, our server uses ECC, is that possible to get memory errors without ECC detect it? Anyway, at this time I can't run diagnosis on RAM, since it's 16gb and I need to shutdown my server to do it, and I have several db that I can't stop. Any tips?

(26 Jun '12, 10:21) Zote
Replies hidden

Most ECC memory will detect 1 and 2 bit errors and correct 1 bit errors. Multi bit errors may occur undetected, als long as the redundant bits stay the same. But practically speaking the probabilty for those errors should be rather low.

(26 Jun '12, 11:22) Reimer Pods

FWIW, the following FAQ deals with RAM checks, too - though they surely will require a server shutdown...

(26 Jun '12, 15:58) Volker Barth

We are able to reproduce an issue that results in the assertion error 201200 "Did not expect to continue row segment". Its reference number is QTS 674549. In our case, that recursive clause of a recursive select statement first cause the non-fatal asssertion error 111706 or 111707 and then the fatal assertion error 201200 when trying to write a subsequent row segment.

For example: The below recursive select does a string concatenation z||c and exceeds the describe data type "char(10)" so it should return a SQL error but not an assertion error.

create table T1 ( a int, b int, c char(10) )

with recursive V0 ( x, y, z ) as (
    select a, b, c from T1 where  a = 1 and b = 2
    union all
    select a, b, z||c f from  T1,V0 where   T1.a = V0.y )
select *
from V0

You may check for recursive selects and/or assertion errors 111706 or 111707. To work around the above issue the corresponding result set column in the first subselect needs to be casted to a larger type, e.g.:

with recursive V0 ( x, y, z ) as (
    select a, b, cast(c as varchar(32767) from T1 where  a = 1 and b = 2
    union all
    select a, b, z||c f from  T1,V0 where   T1.a = V0.y )
select *
from V0

We are currently looking for a proper fix for this issue.

permanent link

answered 28 Jun '12, 07:46

Hartmut%20Branz's gravatar image

Hartmut Branz
37629
accept rate: 0%

edited 28 Jun '12, 09:24

Volker%20Barth's gravatar image

Volker Barth
30.0k294447654

I'm not getting any assertion failed before 201200. Here is my output log:

I. 06/29 08:54:46. Now accepting requests
I. 06/29 09:00:03. Performance warning: No unique index or primary key for table "tbIteAr" in database "credinet9"
I. 06/29 09:02:47. Starting checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:02
I. 06/29 09:02:47. Finished checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:02
I. 06/29 09:02:48. Starting checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:02
I. 06/29 09:02:48. Finished checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:02
I. 06/29 09:06:22. Cache size adjusted to 9944816K
I. 06/29 09:07:22. Cache size adjusted to 11902584K
I. 06/29 09:08:22. Cache size adjusted to 12390992K
I. 06/29 09:09:22. Cache size adjusted to 12512832K
I. 06/29 09:15:06. Cache size adjusted to 12543224K
I. 06/29 09:16:06. Cache size adjusted to 12550808K
I. 06/29 09:17:50. Starting checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:17
I. 06/29 09:18:01. Finished checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:18
I. 06/29 09:18:07. Cache size adjusted to 12552704K
E. 06/29 09:18:13. *** ERROR *** Assertion failed: 201200[credinet9] (11.0.1.2808)
Did not expect to continue row segment
I. 06/29 09:18:13. Database server shutdown requested via server console

Today I get it 3 times (until now). This error is driving me crazy!

(29 Jun '12, 08:25) Zote

The assertion means that a database page was corrupted. The cause can be due to a software fault, or a transient or permanent corruption of the media.

Do you have checksums enabled for your database?

Is there anything in the Windows event log that would pertain to the failure?

permanent link

answered 20 Jun '12, 16:34

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

edited 20 Jun '12, 16:34

How do I check if checksums is enabled? I'm looking windows event log.

(20 Jun '12, 16:43) Zote
Replies hidden

Glenn, nothing on event log. Just 2 assertion failed.

(20 Jun '12, 16:44) Zote

SELECT DB_PROPERTY ( 'Checksum' );

(20 Jun '12, 16:47) Glenn Paulley

It's Off...

(20 Jun '12, 16:55) Zote

Turn on request level debugging (server options -zr sql -zo requestlog.out) and look for the last requests. This should point you to the causing sql statement.

permanent link

answered 29 Jun '12, 08:33

Hartmut%20Branz's gravatar image

Hartmut Branz
37629
accept rate: 0%

I did it and I'm looking file right now.

(29 Jun '12, 09:11) Zote

The last sql just before the assertion failed was what caused the error may have been some before it?

(29 Jun '12, 09:15) Zote

One of the last uncompleted requests. Requests start with ,<, and end with ,>, in the request log. If you cannot find a request you likely need to run with -zrall to also print all fetch requests.

(29 Jun '12, 09:22) Hartmut Branz
Replies hidden

I'm runnig -zr all...

(29 Jun '12, 09:27) Zote
1

I was able to reproduce the problem! It occurs when I update a column with accents. Very probably have some error in my application, but this should not cause the shutdown of the database. This application is PHP and does this by calling a webservice update the database.

(29 Jun '12, 10:28) Zote

Got same error on recursive select. Looks like BUG in SA.

permanent link

answered 18 Oct '12, 16:58

Andrei's gravatar image

Andrei
1
accept rate: 0%

1

What SA version & build are you using?

(18 Oct '12, 17:24) Mark Culp

12.0.1.3769

(19 Oct '12, 02:14) Andrei
Replies hidden

In case your situation fits Hartmut's explanation, does his workaround resolves the problem?

FWIW, the according fix for this CR is documented to be in build 12.0.1.3788, so not yet contained in your version (and not yet available on Windows/Linux, AFAIK).

(19 Oct '12, 03:26) Volker Barth

I use long nvarchar (not a varchar or nvarchar). So it doesn't look as my case. I used recursive select in a View ( automaticaly generated column already was varchar(32767) ).

What I did to workaround? 1) rebuild DB (dbunload -c "{skiped}" -ar ) 2) tried to use my View, but got same assertion 3) so I had to rewrite my view to do not use recursive select

(20 Oct '12, 16:03) Andrei
Replies hidden
1

If you have a recursive query with your problem as a reproducible, feel free to show it here...

(22 Oct '12, 03:21) 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:

×137
×31

question asked: 20 Jun '12, 16:13

question was seen: 2,287 times

last updated: 10 Dec '12, 07:22