1450 Errors in Sybase Errorlog--second opinion

[Former Member]

01:00000:00000:2016/02/08 16:07:09.91 kernel  sddone: read error on virtual disk 52 block 13933312:
01:00000:00000:2016/02/08 16:07:09.91 kernel  sddone: Windows NT system error 1450: Unforseen error.
01:00000:01245:2016/02/08 16:07:09.96 server  Error: 823, Severity: 24, State: 1
01:00000:01245:2016/02/08 16:07:09.96 server  I/O error detected during read for BUF=0x7F2E7080, MASS=0x7F2E7080, Buf#=0

We have these errors in our ASE 12.5.4 ESD#7 Windows 2003 x86 environment running on VMWare ESX 5

This is a production environment and we are doing POC for upgrade to ASE 16, and from Windows 2003,

so let's leave the upgrade suggestions for this question.

The problem is, that it leads to zombie processes, meaning processes not running but still holding locks. That requires a reboot.

SAP says that 1450 are Windows NT error and wants to raises their hands, call it quits.

The windows eventlog does not have any event, so basically the Microsoft team raises their hands too.

is there any way we can try to pinpoint and fix with evidence?

estimate rogue queries going zombie?

like file descriptors?

paged pool errors? (although there are no events related to paged pool in the eventlog)

your replies would be greatly appreciated.

[jayrijnbergen · Accepted solution]

Zombies are tricky, reboot is usually best way to get rid of them

if these zombies still hold locks, you might be able to forcefully release the locks. Note that forcefully releasing locks can cause issues.... but releasing shared locks should be ok.

No guarantees that you can avoid the reboot

dbcc lock_release( spid, { "table", "page", "row" }, locktype =

{ "ex_tab", "sh_page", "up_row", etc. }, dbid, objid [, pageno [, rowno ] ] [, "force" ] )

Did you check for file fragmentation (in Windows) for device 52?

heavily fragmented file can lead to error 1450

[Former Member · Accepted solution]

The file is a tempdb device with 3 fragments. I was wondering if dysnc on windows or async i/o setting could also be a cause of concern.

[jayrijnbergen · Accepted solution]

If it's a tempdb device, regardless of the issues, turn off dsync I/O

no need to recover tempdb, every restart it's rebuilt from model

That said, not even sure if dsync is actually doing anything on Windows.

What async i/o setting?

[ChrisBaker · Accepted solution]

You are on 12.5.4, so Direct I/O is not available. However, you may want to confirm that the O/S is not attempting to cache the device, when dsync is turned on or off. As you are on 32-bit Windows, the device might be too large to cache in a 32-bit environment And this could be contributing to the problem.

We noticed in 15.7 at least, with Direct I/O off on temp devices, NTFS tried to cache the temp devices (actually ANY device with Direct I/O off).  Under ECC these were very large and started to push the ASE out of memory. Windows obviously recognizes MSSQL devices and does not cache them. But with ASE, you must be explicit. The workaround was to turn Direct I/O on, even for temp devices, to prevent caching by the O/S.

Although you you are still on 12.5.4, I wonder if the 32-bit O/S is trying to cache the device. You may want to try running the O/S with /3GT flag (if my memory is correct) to give more data space to processes (gives only 1GB to the O/S instead of the default 2+2 split - assuming you have 4GB of memory on the box) and check if setting Dsync on or off helps.

The other solution is to get to 64-bit, supported ASE and O/S ASAP.

Chris

[Former Member · Accepted solution]

Hi Chris, we tried dsync on and off, both do not work in eliminating this issue. As dysnc is not read for windows environment.

I wonder if configuration option "allow sql server async i/o" (default on) can make the i/o's synchronous like not getting it to cache by OS (and would they be slow now?)

do you know how can we check in windows/VM level if a device is being cached and how to not let it cache at all?

[ChrisBaker · Accepted solution]

O/S Caching may or may not be the problem here.  It may still be a device error related to running on a VM and the application you are running.

Are you properly allocating enough physical memory, CPU, etc. to the ASE guest instance?

Could there be any issues related to the ASE guest being swapped out of host memory when connections are dropped?

Did this happen when the ASE was run on a physical box?  What is the setting of 'runnable process search count'?  I assume at some point the ASE ran on physical hardware and has been virtualized, in preparation for the move.  What needs to be understood is that older ASEs were not good candidates for virtualization without a lot of tuning.  You need to still map physical resources 1:1 (e.g. memory and cpu).

Your original question related to zombie processes holding locks.  I hit a situation at a customer many years ago that might relate here.  This is my thinking:

• ASE 12.5.4 still had APL locking on system pages
• Many stored procedures and applications use temporary tables but do not clean up.
• Clean-up is left to the SPID when the connection is dropped.
• In a high-concurrency environment, there is then contention on the APL system tables of the tempdb.  The removal of the session temp tables is not completed due to the contention on the tempdb system catalogs  - I believe this is the root of your zombies.
• Another side effect seen at the time was the reduction of free space in tempdb over time, requiring a reboot of the ASE eventually.

How this relates to the 1450 error, I am not sure, but the second error - 823, might be related.  I wonder if the virtualization is part of the issue here (perhaps the VM is not persisting changes to the tempdb device before swapping the ASE out of memory - hence my question on resources).

Regardless, if the root cause is the concurrency into tempdb and need to clean up, you can fix this simply by dropping #temp tables in the same sp that creates them, thereby reducing the need to clean up tables at the end of the connection.  It is always a best practice anyway.

If you don't want to change code, start the move to a more recent ASE anyway.  To be honest you might be wasting your time and newer ASE has DRL catalogs.  You need the time to look at the optimizer differences instead.  (HINT: do NOT rely on 'compatibility mode').

Chris

[former_member188958]

A quick search on Google found this Microsoft writeup for MS SQL Server: https://support.microsoft.com/en-us/kb/2002606https://support.microsoft.com/en-us/kb/2002606

Now, ASE isn't MS SQL Server, but they do share common roots.
The MS SQL Server message suggests it will keep retrying.
I suspect ASE doesn't know how to handle this error and isn't handling not knowing how to handle it well.  Assuming that is the case, there isn't anything that can be done about that because 12.5.4 is end-of-life for engineering support and has been for many years.

However, the MS note does suggest the problem has its roots in file fragmentation.

It might help avoid the issue to do something like this :

Truncate tempdb down to just it's initial allocation on the master device

Reboot ASE.

Drop the tempdb device and delete it from the file system

Use disk init to create a new device

Create a user database on the new device for the full size of the device, this will ensure all the space for the device is actually fully allocated all at the same time.

Drop the user database

Alter tempdb onto the new device (and any other devices it was originally on)

-bret