Q10.3.1
SYBASE Technical News, Volume 5, Number 1
February, 1996
This issue of SYBASE Technical News contains new information about your Sybase software. If needed, duplicate this
newsletter and distribute it to others in your organization. All issues of SYBASE Technical News and the troubleshooting
guides are included on the AnswerBase CD. Send comments to technews@sybase.com.
To receive this document by regular email, send name, full internet
address and customer ID to technews@sybase.com.
In this Issue
Tech Support News/Features
SQL Server 11.0
SQL Server General
Connectivity / Tools / PC
Certification and Bug Reports
Sybase Technical Support is open on all holidays and provides full
service on many. During the limited-service holidays shown below,
Technical Support will provide the following coverage:
- SupportPlus Preferred and Advantage customers may log all
cases; we will work on priority 1 and 2 cases over the holiday.
- 24x7 and 24x5 Support customers may log priority 1 cases; we
will work on these over the holiday.
- SupportPlus Standard, Desk Top, and Regular Support
customers may purchase Extended-hour Technical Support for
coverage over the holiday.
Table 1: Sybase Technical Support limited service holidays -
U.S. customers
| Holiday | Date |
|---|
| New Year's Day | January 1 |
| President's Day | February 19 |
| Memorial Day | May 27 |
| Independance Day | July 4 |
| Labor Day | September 2 |
| Thanksgiving | November 28 |
| Christmas | December 25 |
Table 2: Sybase Technical Support limited service holidays -
Canadian customers
| Holiday | Date |
|---|
| New Year's Day | January 1 |
| Good Friday | April 5 |
| Victoria Day | May 20 |
| Canada Day | July 1 |
| Labour Day | September 2 |
| Canadian Thanksgiving | October 11 |
| Christmas Day | December 25 |
| Boxing Day | December 26 |
If you have questions, please contact Technical Support.
Sybase SQL Server release 11.0 includes a new online database
command. This article contains a few commonly asked questions
about the "online" state of a database and the use of the online database
command. For more information, please see What's New in Sybase
SQL Server Release 11.0?, the System 11 SQL Server Migration Checklist
supplement to this Sybase Technical News, and the SQL Server
Reference Manual.
Question
Executing a load database leaves the database offline; does load
transaction leave a database online or not?
Answer
load transaction leaves the database the way it found it: if it was offline,
it remains offline; if it was online, it comes online again. A customer
doing a sequence of load database, load tran, load tran ... will have to use
the online database command at the end of the load sequence. A
customer who has already brought the database online and who
then loads another transaction dump will not have to repeat
online database.
Question
If a database becomes corrupt during boot time recovery and I am
able to fix whatever caused the problem, can I just bring the database
online with the online command, or will I have to reboot SQL Server?
Answer
Using the online database command in this context will bring the
database online; you won't have to reboot. Note, however, that if the
database has been marked suspect, online database will have no effect.
Question
Can I run dbcc checkalloc or tablealloc with the fix option when a
database is offline, instead of having to put the database in single
user mode?
Answer
Yes. You can also do any other dbcc commands in an offline database.
Question
What causes a database to go offline?
Answer
There are three things that take a database offline:
- load database. This causes a "persistent" offline state, that is, the
database stays offline until you issue an online database command.
- load transaction. As mentioned above, if the database was online
before the load transaction, it comes back online automatically; if it
was offline, it stays offline.
- Database recovery. This causes a "temporary" offline state, that
is, the database comes back online automatically when recovery
is finished, unless an error occurs during recovery.
Note
The "persistent" offline state overrides a "temporary" offline state. Thus, if
you do a load database followed by a load transaction, the offline state set up
by the load database persists after the load transaction. That's the mechanism
by which load transaction "leaves the database the way it found it" as in the
first question above. That's also the way SQL Server detects that a
database should remain offline even if the server should crash while the
database is in a load sequence.
Question
What dumps are compatible between SQL Server 11.0 and earlier
releases?
Answer
The following table shows what SQL Server releases can read dumps
from other SQL Server releases.
Destination -->
Source
v | SQL Server 4.9.x |
SQL Server 10.0.x | SQL Server 10.1 | SQL Server
11.x |
|---|
| Dump Level 4.9.x | Yes | No | No | No |
|---|
| Dump Level 10.0.x | No | Yes | Yes | Yes |
|---|
| Dump Level 10.1 | No | No | Yes | Yes |
|---|
| Dump Level 11.x | No | No | No | Yes |
|---|
Note
This table applies to the compatibility of physical dumps themselves, rather
than Backup Server compatibility. Backup Server releases are only
compatible with the SQL Server of the same or previous release level.
Here are a few important issues regarding dump compatibility
between SQL Server releases.:
- In general, you should never assume backward compatibility: a
lower-numbered version of a product probably can't read a
higher-numbered version's files. However, higher-numbered
versions should be able to read files from lower-numbered
versions. You can dump from 10.0.2 and load to 10.1.
- 10.1 dump headers have a field in them that can't be read by
10.0.x servers. The new "log version" field was added so that
System 11 could distinguish a 10.1 release database by reading
the dump header. 10.0.x doesn't recognize this, so 10.0.x can't
read 10.1 dumps.
- While SQL Server can read several flavors of log record, it can
only write log records at its own release level; and if it writes an
11.0 log record into a section of log that currently contains 10.x
records, the new records will not be readable during a
subsequent load or boot-time recovery because the log reader
expects all log records to be in a single format until SQL Server
tells it to switch formats.
Consequently, when you start up SQL Server 11, one of two
things will happen:
- If boot-time recovery succeeds, your databases will be online,
but SQL Server will refuse to do a logged dump transaction until
you do a dump database. This means that until you dump database,
you can only do dump transaction with no_log or with truncate_only,
because other kinds of dump transaction actually write some log
records. truncate_only is allowed because the database is online
and therefore writable, and since you aren't trying to dump log
records to be read later it's okay to write an 11.x log record.
- If boot-time recovery fails for some reason, or if the database is
replicated (in which case it will be offline only until replication
is finished), you can only do dump transaction with no_log or with
no_truncate. The database is offline and unavailable for writing,
but no_truncate is allowed because it is not a logged operation
and you or Sybase Technical Support (in the case of recovery
failure) may find a copy of the log useful. If you are able to
bring the database online by executing online database, you will
still have to dump database before you can do a logged operation.
In both of these cases, you will see Error 4225:
This database has not been dumped since it was created or
upgraded. You must perform a dump database before you can dump its
transaction log.
You may also see Error 4226:
Logged DUMP TRANSACTION cannot run in database %.*s, because that
database's log version (%d) disagrees with the SQL Server's log
version (%d); use DUMP TRANSACTION WITH NO_LOG. Versions will agree
once ONLINE DATABASE has run.
To clear this condition, again, just do dump database.
Question
Is lock promotion calculated on a per statement basis, or on a per
transaction basis? I have a single process executing several thousand
update statements to the same table in a transaction, and the
EX_PAGE locks are not being promoted to a table-level lock. Is this
the behavior I should expect?
Answer
Lock promotion is performed on a per-statement basis.
Additionally, there may be some complex statements for which lock
promotion will not be performed at all. We do not take the
transaction into account.
In System 11, however, you can configure the lock promotion
threshold for tables, databases, and servers. This will give you the
ability to guarantee that updates use table level locks. For more
information about setting the lock promotion threshold, please see
Chapter 11, "Locking on SQL Server," in the SQL Server Performance
and Tuning Guide.
In SQL Server 11.0, there are some important changes in the way that
thresholds behave on the log segment.
Thresholds and the syslogshold Table
In the situation where you or a threshold process cannot truncate the
log because of an open transaction, SQL Server 11.0 provides a new
table, syslogshold, which includes information on what process has
the open transaction. You can use information in this table to set up
a threshold process to deal with the open transaction.
For more information on the syslogshold table, please see the SQL
Server Reference Supplement. For information about using it in
conjunction with threshold procedures, see the chapter "Managing
Free Space with Thresholds" in the System Administration Guide.
Private Log Cache Feature
As a performance enhancement, 11.0 introduces the Private Log
Cache (PLC). This feature maintains log records in memory, rather
than writing them directly to the log. However, SQL Server must
guarantee that space is available in the log to flush these records
when the time comes. Consequently, the PLC "reserves" log pages¯
that is, it marks space as used before actually writing to it. Presently,
this reservation equals three pages per open transaction per
database.
This reserved space counts as "used pages" toward triggering the
threshold procedure, even though under some circumstances that
space won't actually be written. The effect for you is that in the log
segment, thresholds will trigger sooner than it looks like they
should: there may appear to be more empty space in the log segment
than the threshold claims there is. This occurs because SQL Server
reports pages actually written, while it acts on pages reserved.
This will be particularly noticeable if your site has many users with
open transactions in a single database.
Note
Remember that the rule is three pages reserved per transaction per
database: a thousand users with a transaction open in one database will
have reserved 3000 pages in the log for that database.
Question
Sometimes a threshold process that dumps the transaction log when
the threshold is overrun fails to actually do the dump tran. Why did it
fail?
Answer
There are many reasons the threshold procedure can fail to start.
SQL Server checks all of the following, in order:
- That it can allocate memory for the information the threshold
procedure will need.
- That it can create a task to execute the procedure.
- That the threshold procedure can use the database.
- That the systhresholds table exists.
- That there is an entry in systhresholds for the threshold in
question.
- That the threshold owner is a valid user in the database.
- If the database is usable by the database owner only, that the
user who bound the threshold is the database owner.
- That the threshold owner is a valid server login.
- That the procedure named in the threshold is valid.
The step that concerns us here is step 3. If your procedure failed,
check to be sure that the database is not in single-user mode. If a
database is in single-user mode, and the user overruns a threshold,
the threshold process won't succeed in doing any work in that
database, because the maximum number of users allowed in the
database is one and the user who overran the threshold is that one.
The threshold procedure must be in the database before it starts
work; if it cannot use the database, SQL Server prints Error 7403 and
stops:
Threshold task could not use database %d, and so
cannot execute the threshold procedure for segment
%d, free space %ld.
This behavior affects all threshold procedures, regardless of whether
they do any work that is not directly involved with the database.
Question
Is there a maximum value for the timestamp in the log? If there is,
what happens when that number is reached?
Answer
Yes, there is a maximum timestamp value. If we ever reach that
value, the next assigned timestamp value would be 0 (not 1). If the
timestamp does roll over, corruption might result; however, before
this happens, you will be warned.
SQL Server attempts to warn you that the database is approaching
the maximum timestamp value by checking that the current
database timestamp value is less than 0xffff 0xfefffffff. This
validation check occurs each time the dbtable is created for the
database. If the timestamp is greater than 0xffff 0xfefffffff, SQL
Server raises Error 935:
WARNING - the timestamp in database `%.*s' is approaching the maximum allowed.
When you receive Error 935, you should do one of the following as
soon as possible:
- Create a new database the same size as the old, execute
sp_dboption "select into" for that database, then use select into to
recreate the tables.
- Bulk copy your data out, drop and re-create the database, then
bulk copy the data back in.
Explanation
Sybase timestamps are an unsigned 48-bit number; that is, they can
hold integer values from 0 through 248 -1 or 281,474,976,710,655. In
recovery, SQL Server decides what has and hasn't been done by
comparing timestamps; if the timestamp on the page is smaller than
the one in the log record, then this change hasn't been made, so SQL
Server makes it.
If the timestamp were ever to roll over, the timestamp in a log record
might well be something like 0xffff.ffffffac, and the timestamp on the
page something like 0x0000.00000013. Logically, the timestamp on
the page should be later than the one in the log, because the
timestamps have wrapped around, but SQL Server won't detect that.
All SQL Server knows is that the page timestamp is smaller than the
log record timestamp, so it will make the change specified by the log
record, thus corrupting the data page.
Bear in mind that timestamp can take quite a long time to roll over.
For instance, suppose that your server makes a change in the
database every millisecond (1000 changes per second), all day every
day. At that rate, it will take more than 8,900 years for the timestamp
to roll over. (248 / (1000 * 60 * 60 * 24 * 365.25) = 8919.4) However,
improper use of dbcc rebuild_log or dbcc save_rebuild_log could result in
the timestamp reaching the maximum value. When this occurs, SQL
Server will generate Error 6901:
Overflow on High component of timestamp occurred in database %d. Database table possibly corrupt.
Again, if you receive this error, use one of the methods described
above to re-create your database.
Note
This information applies to SQL Server 11.0 as well as to previous
releases.
Question
I want to back up all my databases, both Sybase and Oracle, onto the
same tape each night in a batch. How do I do this?
Answer
You can't. Backup Server expects a tape to conform to a format
similar to ANSI standard, where the first record on the tape is a
"volume header", followed by some "file header" records. Because
other vendors do not try to use ANSI standard format,
Backup Server cannot use tapes that contain other vendors' data.
Sybase has no plans to change this in the foreseeable future.
Question
When I try to dump transaction in my database, I get error 4207, which
says that dump transaction is not allowed while the select into/bulk copy
option is enabled. The problem is, that option isn't actually enabled
in that database. What happened?
Answer
What you're seeing is the old text of error 4207. It caused a lot of
confusion, so Sybase changed it. As of SQL Server 11.x, it says:
Dump transaction is not allowed because a non-logged operation was performed on the database.
Dump your database or use dump transaction with truncate_only until you can dump your database.
What happened to you is one of several things:
- You performed a fast (unlogged) bulk copy into your database.
- You performed a select into a table in your database.
- You used dump transaction with truncate_only or no_log in your
database.
All of these operations make changes that are not recorded in the
transaction log. Because the log doesn't have a record of those
changes, it isn't possible to rebuild your database from only the log.
Thus, as soon as one of those things happen in a database, SQL
Server disallows dump transaction until the next dump database has been
performed. The database dump gives the server the necessary
information to re-create those changes, so it is then possible to use
transaction log dumps to re-create the rest of the changes.
Question
I have two 4.9.2 SQL Servers running at different Rollup levels.
Certain queries containing subqueries run much more slowly on the
newer Rollup than on the older one. The showplan output reveals that
the newer Rollup uses a four-step plan where the older one uses only
two steps. I have heard that two SQL Server trace flags, 322 and 323,
might help me. What do these trace flags do?
Answer
A fix for an optimizer bug, 13495 (also listed as 17230), changed the behavior of the
optimizer. 13495 ensures that subqueries under ors are not unnested,
so that correct results are returned in all cases. The following table
lists the platforms and Rollup numbers in which this change first
appeared:
| Platform | 4.9.2. Rollup Number |
|---|
| SunOS Release 4.x (BSD) | 4152 |
| HP 9000 Series 800 HP-UX | 4153 |
| IBM RISC System/6000 AIX | 4154 |
| AT&T System 3000 UNIX SVR4 MPRAS | 4155 |
| Digital OpenVMS VAX | 4156 |
| Sun Solaris 2.x | 4157 |
| Digital OpenVMS Alpha 1.5 | 4158 |
| Digital UNIX | n/a |
If you are running one of these Rollups, or any Rollup released later,
you may find that you now get poor performance with certain
queries containing subqueries. To fix this, you can try using trace
flags 322 or 323. Trace flag 322 is to be used at SQL Server startup;
trace flag 323 is the interactive version, invoked with the command
dbcc traceon(323). Both trace flags disable the fix for bug 13495.
WARNING!
Disabling this fix may return performance for these queries to your
previous levels, but may also cause SQL Server to return two sorts of
incorrect results:
- Because subqueries are processed as joins, existence checks may
return duplicates where they should not.
- The construction or x in this statement:
select a from b where...
will not work when b is empty.
If you are running SQL Server release 10.x and encountering this
particular performance problem, you may use EBFs 4428 and higher,
which include these trace flags.
These trace flags are not available in SQL Server 11.0 because
subquery processing has been substantially rewritten.
Release 10.0 of HP-UX allows for the installation of the
asynchronous driver using SAM (System Administration Manager).
This alleviates the need to run the installasync script which is currently
shipped with Sybase SQL Server Releases 10.0 and below for HP-
UX. To install async with SAM, follow these steps:
- Invoke SAM and select Kernel Configuration.
- Select Drivers within the Kernel Configuration menu.
- Change the Pending State for asyncdsk to In.
- Rebuild the Kernel and Reboot the system (using the Actions
menu option).
- Execute the following statements as "root":
/etc/mknod /dev/async c 101 5
chmod 0660 /dev/async
chown sybase /dev/async
chgrp sybase /dev/async
Note
Step 5 may also be performed prior to step 1.
In fact, as of System 11, Sybase will no longer ship an installasync
script. Instead, use the steps described above.
If you have questions abut SAM, please call HP Technical Support.
Sybase has determined that Digital Ultrix 4.4 is not binary
compatible with Ultrix 4.3a, and we do not recommend that
customers run the 4.2 SQL Server under Ultrix 4.4. Sybase has no
plans to recompile the SQL Server on Ultrix 4.4; any further issues
relating to the compatibility of Ultrix 4.4 should be addressed
directly to Digital Equipment Corp.
The following table lists the latest Rollups for SQL Server on all
platforms.
| Platform | Release Number | Rollup Number |
|---|
| Sun OS Release 4.x (BSD) | 10.0.2 | 5539 |
| Sun OS Release 4.x (BSD) | 4.9.2 | 5631 |
| HP 9000 Series 800 HP-UX | 10.0.2 | 5540 |
| HP 9000 Series 800 HP-UX | 4.9.2 | 5632 |
| IBM RISC System/6000 AIX | 10.0.2 | 5541 |
| IBM RISC System/6000 AIX | 4.9.2 | 5633 |
| AT&T (NCR) System 3000 UNIX SVR4 MPRAS | 10.0.2 | 5542 |
| AT&T (NCR) System 3000 UNIX SVR4 MPRAS | 4.9.2 | 5634 |
| Digital VAX OpenVMS | 10.0.2 | 5543 |
| Digital VAX OpenVMS | 4.9.2 | 5635 |
| Sun Solaris 2.x | 10.0.2 | 5544 |
| Sun Solaris 2.x | 4.9.2 | 5636 |
| Digital OpenVMS Alpha 1.5 | 10.0.2 | 5545 |
| Digital OpenVMS Alpha 1.5 | 4.9.2 | 5637 |
| Digital OSF/1 | 10.0.2 | 5549 |
| Digital OpenVMS Alpha 1.0 | 4.9.2 | 5637 |
| Novell Netware | 10.0.2 | 5547 |
| OS/2 | 10.0.2 | 4726 |
| Windows NT | 10.0.2 | 5546 |
In the case of 4.9.2 Rollups, there are subsequent one-off SWRs
(formerly EBFs) that you may order for specific bug fixes, but Sybase
recommends you upgrade rather than ordering a one-off SWR at this
point.
The following tables list the latest rollups of Sybase products other
than SQL Server for PC platforms.
Table 3: EBFs for DOS Platforms
| Product Group | Release Number | SWR Number |
|---|
| Open Client/C Developers Kit | 10.0.2 | 4907 |
| DB-Library | 4.2.5 | 4987 |
| DB-Library | 4.2.6 | 4987 |
| Net-Library FTP PC/TCP | 1.0.3 | 3666 |
| Net-Library Microsoft TCP | 1.0.3 | 5408 |
| Net-Library Named Pipes | 1.0.2 | 2387 |
| Net-Library Novell IPX/SPX | 1.0.2 | 3661 |
| Net-Library Novell LAN Workplace | 1.0.3 | 3665 |
| SQR Workbench | 2.5 | 2489 |
| SQR Execute | 2.5 | 2490 |
Table 4: EBFs for Netware Platforms
| Product Group | Release Number | SWR Number |
|---|
| Open Client/C Developer's Kit | 10.0.3 | 5509 |
| DB-Library | 4.6 | 2849 |
| Replication Server | 10.1 | 4922 |
Table 5: EBFs for OS2 Platforms
| Product Group | Release Number | SWR Number |
|---|
| Open Client/C Developer's Kit | 10.0.3 | 5677 |
| DB-Library | 10.0.2 | 4146 |
| DB-Library | 4.2 | 4721 |
| Net-Library Named Pipes | 1.0.2 | 3904 |
| Net-Library Named Pipes | 2.0 | 2698 |
| Net-Library Novell SPX/IPX | 1.0.2 | 3982 |
| Net-Library Novell IPX/SPX | 2.0 | 1612 |
| Net-Library Novell LAN WorkPlace | 1.0.2 | 2534 |
| Net-Library IBM TCP | 10.0.1 | 3184 |
| SQR | 2.4 | 1822 |
| Open Server | 10.0.2 | 3905 |
| Open Server | 2.0 | 3436 |
Table 6: EBFs for PC Windows Platforms
| Product Group | Release Number | SWR Number |
|---|
| Open Client/C | 10.0.3 | 5735 |
| DB-Library | 4.2.5 | 5185 |
| Net-Library FTP PC/TCP | 10.0.1 | 3777 |
| Net-Library Named Pipes | 10.0.1 | 5303 |
| Net-Library NEWT | 1.0.3 | 3158 |
| Net-Library Novell LAN WorkPlace | 1.0.2 | 2472 |
| Net-Library WinSock | 1.0.3 | 5146 |
| ODBC | 10.0.1 | 5736 |
| Embedded SQL/C Precompiler | 10.0.2 | 4653 |
| Embedded SQL/Cobol Precompiler | 10.0.2 | 4269 |
| Embedded SQL/C Precompiler | 4.0.4 | 3607 |
| SQL Monitor Client | 10.1.2 | 4723 |
| SQR Workbench | 2.5 | 2492 |
| SQL Server Manager | 10.3 | 5099 |
Table 7: EBFs for Windows NT Platforms
| Product Group | Release Number | SWR Number |
|---|
| Open Client/C Developer's Kit | 10.0.3 | 5655 |
| Replication Server | 10.1 | 5112 |
| Open Server | 10.0.3 | 5513 |
| Manager Server | 10.1 | 4117 |
Disclaimer: No express or implied warranty is made by Sybase or its
subsidiaries with regard to any recommendations or information
presented in SYBASE Technical News. Sybase and its
subsidiaries hereby disclaim any and all such warranties, including
without limitation any implied warranty of merchantability of fitness
for a particular purpose. In no event will Sybase or its subsidiaries
be liable for damages of any kind resulting from use of any
recommendations or information provided herein, including without
limitation loss of profits, loss or inaccuracy of data, or indirect
special incidental or consequential damages. Each user assumes the
entire risk of acting on or utilizing any item herein including the
entire cost of all necessary remedies.
Staff
Principal Editor: Leigh Ann Hussey
Contributing Writers:
Lance Andersen, Peter Dorfman, Sekhar Prabhakar, Loretta Vibberts,
Elton Wildermuth
Send comments and suggestions to:
SYBASE Technical News
6475 Christie Avenue
Emeryville, CA 94608
or send mail to technews@sybase.com
Copyright 1996 © Sybase, Inc. All Rights Reserved.