----------------------------------------------------------------------------
SYBASE TECHNICAL NEWS
Volume 7, Number 4 April 1998
----------------------------------------------------------------------------
Introduction
This issue of Sybase Technical News contains new information about your
Sybase software. This newsletter is intended for Sybase customers. All
issues of Sybase Technical News are included on the Technical Documents
web pages.
----------------------------------------------------------------------------
In This Issue
--------------------------------------
Tip of the Month
TechNotes:
* Frequently Asked Questions About Database Dumps and Loads in Adaptive
Server Enterprise and Sybase SQL Server
http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=920
* FAQs about xp_server
http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20200
* SQL Server and ASE Maximum Shared Memory
http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20203
Other Useful Information
Subscription
----------------------------------------------------------------------------
Tip of the Month: Restriction When Using Identity Columns and Trace Flag
299
to Improve Performance
--------------------------------------
If you use trace flag 299 for performance gains with stored procedures that
include temporary tables with identity columns, be aware that as of Release
11, the server imposes limitations on the temporary tables and identity
values.
Background
Trace flag 299 allows a stored procedure to open temporary tables by name
if
the server is unable to open temporary tables by the object ID. This skips
the recompilation step and can boost performance in stored procedure
execution time.
If you know that the table schemas will always match, you can use
traceflag299 to avoid re-compiling the stored procedure for which the
temporary table is being opened.
How Trace Flag 299 Functions with Identity Columns
Trace flag 299 prevents the stored procedure from re-compiling. During the
recompilation step the server generates an identity number for a column
declared as identity . If the stored procedure is not re-compiled, the
server inserts the identity number as 0. This can cause problems for
applications expecting the value to increment automatically.
Workaround for Identity Columns
As of Release 11, instead of creating a new identity value in the temporary
table for the rows inserted into the stored procedure, the server sets the
ID to 0. This behavior occurs if the temporary tables created and used in
stored procedures utilize identity.
To avoid this limitation, follow the steps below:
* Execute the stored procedure for the first time in the same isql
session in which you created the stored procedure.
* Execute the stored procedure prior to dropping the temporary table.
----------------------------------------------------------------------------
TechNotes
--------------------------------------
Frequently Asked Questions About Database Dumps and Loads in Adaptive
Server
Enterprise and Sybase SQL Server
This document answers frequently asked questions about database dump and
loads with Adaptive Server Enterprise and Sybase SQL Server 11.0.x. It
comprises the following sections:
Logs
Dumps and Loads
Backups and Maintenance
Logs
What information is tracked by the logptr column in master..sysdatabases?
The logptr column points to the first data page in the transaction log.
This
column is necessary for the dump tran with no_truncate command to function.
If the transaction log is kept on a separate physical device, you can use
the dump tran with no_truncate command to dump the current transaction log
even if the database itself has become inaccessible. This feature lets you
rebuild a database to its state at the moment it was lost due to media
failure.
WARNING! Do not use the dump tran with no_truncate command with a database
that is in use. For more information, see the Sybase Adaptive
Server™ Enterprise System Administration Guide.
Media recovery from the current log is possible because the logptr column
is
located outside the user database, in the master database.
Why does my log grow during dump, even with trunc log on chkpt option?
A database is configured to have trunc log on chkpt. During a dump of that
database, the syslogs table continues to grow until the dump is finished.
Why doesn't the log truncate?
The log cannot truncate because the checkpoint process occurs only at the
start and at the completion of the database dump.
Dumps and Loads
What effect does select into/bulkcopy have on dump transaction?
Why are dump transaction commands not allowed for a database when it has
the
select into/bulkcopy option set? Trying to do so generates Error 4207.
This happens only when a minimally logged operation occurred and the select
into/bulkcopy option is set. When the select into/bulkcopy option is set in
a database, rows added with fast bulkcopy are not logged. Only the space
allocated during the fast bulkcopy is logged. The transaction log is not
complete and could not be used for load transaction.
To perform a dump transaction for that database, the following must be
true:
* select into/bulkcopy option must be disabled with sp_dboption.
* The database must be dumped with dump database before you dump the
transaction log.
See the Sybase Adaptive Server™ Enterprise Reference Manual and the
Sybase Adaptive Server™ Enterprise System Administration Guide for
details.
What is the effect of a database's growth on dump database time?
As the number of pages used by a database increases, does the time for a
dump increase because the dump database command has to check all the pages
in that database or does it remain constant?
The time required for dumping a database increases as the number of pages
increases. The dump process dumps only the allocated pages being used in
that database. The dump does not need to check every page, only the
allocated pages.
What are the differences between dump transaction and dump transaction with
no_truncate?
When a dump transaction command is issued, the server makes a copy of all
the inactive transactions that occurred on a particular database up to the
point of the actual time of dump, and those transactions are removed from
the log. When thedump transaction with no_truncate command is issued, the
server makes a copy of the inactive transactions but does not remove those
transactions from the log.
Therefore, if successive transaction log dumps are performed using dump
transaction with no_truncate, the log will grow in size and will eventually
have to be purged either via dump transaction or dump transaction with
truncate_only.
Note: The dump transaction with truncate_only command does not make a copy
of the log, but purges the log of all inactive transactions.
What are the differences between dump transaction with truncate_only and
dump transaction with no_log?
Neither dump transaction with truncate_only nor dump transaction with
no_log
make a copy of the log records. Both reduce the space used in syslogs
whenever possible.
The dump transaction command adds checkpoint records and log-deallocation
records to the log even when just truncating the log, except when using the
with no_log option. Use dump transaction with no_log only when dump
transaction with truncate_only fails because syslogs lacks room for dump
transaction's checkpoint and deallocation records. See dump transaction in
the Sybase Adaptive Server™ Enterprise System Administration Guide.
WARNING! You must dump the database immediately after performing either of
these options. If corruption occurs while using dump transaction
with no_log or dump transaction with truncate_only, you will be
unable to recover because there will be no log information.
Will databases retain dbid value with dump and load?
A database is dumped from one server and loaded onto another. Will that
database have the same dbid after the load?
No, because the dbid of a database is determined at the time the database
is
created and a row is added to sysdatabases. The dbid of the database that
is
loaded is the dbid of the database into which it is loaded.
Why does suid change in a database being loaded to match sysdatabases?
During the load of a database, the following error messages may display:
Error 3115 Severity 10: User %.*s in database %.*s has suid %d, which is
the
same as the suid of the DBO (as defined in Sysdatabases). User %.*s will be
given suid %d.
Error 3116 Severity 10: When all load transactions have been completed for
database %.*s, user %.*s should be given a unique suid.
These errors occur due to a condition that causes inconsistency in the user
information between source and target databases. The load attempts to
update
sysusers..suid for the target database to match the source suid. However,
doing this would cause duplicate suids in sysusers, which is not allowed
because there is a uniqueness requirement for suid.
Example 1
This example shows sysusers before the load and after:
sysusers suid uid name
Before Load23 1 dbo sa_user
1 2
After Load 1 1 dbo sa_user
-99 2
In this case, another user owned the database that was dumped but had the
"sa" role as a user in the database. The destination database for the load
was created by the "sa". The sysusers row for "dbo" should be changed at
the
end of the load. There is a unique index on suid, so the row for sa_user
has
its suid changed to -99.
Why do I get Error 3114 after loading dump of master to another machine?
You may encounter this error:
Error 3114, Severity: 21, State: 1 Database does not have an entry in
Sysdatabases.
Cannot recover the master database, exiting.
The master database contains references to the devices and databases that
are loaded onto the first machine. The second machine contains the wrong
references, causing the error. Sybase recommends using the same machine for
dumps and loads of the master database.
Backups and Maintenance
Why should I run dbcc commands on a database before dumping?
It is often possible to dump a database that is not consistent. The dbcc
checkalloc, dbcc checkdb, dbcc checkcatalog, and dbcc checkstorage commands
perform different tests of the database's consistency; they are briefly
described in the chapter, "Checking Database Consistency" in the Sybase
Adaptive Server™ Enterprise System Administration Guide. When it is
not practical to run all these commands before each dump of a very large
database, they should all be run periodically as part of a preventive
maintenance program
Note: dbcc checkstorage, introduced in Adaptive Server 11.5, requires its
own database, dbccdb.
How often should I perform dbcc?
How often depends on how much data you are willing to lose in a worst-case
scenario. In general, the more important your data, the more often you
should run dbccs.
If yours is not a 24-hour organization, and dbccs will complete during your
down-time, perform them nightly with the output redirected to a file.
Perform a grep on the file for the keywords "Err," "err," and "Msg" to
catch
most errors that dbcc would report. Scan the output occasionally to catch
anything else that might be reported.
Mission-critical enterprises often use a second server as a "warm backup."
Instead of running dbcc commands on the production server, dumps are taken,
loaded on the warm backup, and dbccs are run there. If errors are
discovered, attempts are made to correct it on production server.
Sites with very large databases sometimes use a rotating schedule of dbcc
tablealloc and dbcc checktables, rather than the all-at-once dbcc
checkalloc
and dbcc checkdbs. Additionally, a new command, dbcc checkstorage, allows
you to check a database that is in use with little impact on performance.
See the Sybase Adaptive Server™ Enterprise System Administration
Guide.
How often should I dump and load the database?
In case of catastrophe (such as disk failure), how much processing are you
willing to lose? If the answer is 1 hour's worth, you probably should dump
the database at least daily, and dump the log at least hourly.
Generally, you load the database to recover from catastrophe. However,
Sybase recommends you occasionally perform a database dump and load in a
test environment to ensure that the dump process is working and you are
familiar with how to do it.
Note: Do not perform these tests on your production database. If something
is wrong with your dump, and you load it on top of your database,
then you have lost your data.
Which utility should I use for nightly tape backups?
In addition to the server commands dump database and dump tran, you can use
third-party tools such as SQL-Backtrack™.
Which media drivers are compatible with the server?
Because of the number of media drivers, Sybase does not test compatibility
for every driver. Contact your vendor for driver-compatibility information.
What if I don't have enough time for a full backup?
You can shorten the required backup-time by:
* Truncating the log just before performing dump database. A database
dump includes all log pages, regardless of whether they are committed
records. By truncating the log, only uncommitted records will be
included.
* Stripe the dump over as many dump devices as possible. Backup Server
can handle up to 32 dump devices.
If neither of these measures help, you can still perform full backups.
Backup Server is designed to minimize interference with server operation.
Therefore, you can perform dumps during periods of normal load, although
performance would be affected.
How do I back up to tape using isql?
Log in to isql and issue the following:
1> dump database to ""
2> go
You can save and execute this script through an automated scheduler such as
cron, if you know that your dump tape will be in that particular tape drive
when the scheduler job executes. Otherwise, you risk overwriting the wrong
tape.
For larger databases, you may have to stripe across several tape drives to
perform this automatically. For information on striping, see the Sybase
Adaptive Server™ Enterprise System Administration Guide
When should I perform checkpoints, or does the server automatically perform
checkpoint before a backup?
Adaptive Server checkpoints the databases automatically at the beginning of
dump command. At other times, checkpoint frequency is controlled by the
"recovery interval" parameter (sp_configure) and the amount of activity in
the database. Otherwise, perform a manual checkpoint after making direct
updates to system tables and as part of changing parameters with
sp_dboption.
Why did my backups stop working after upgrade?
Unless the Backup Server is correctly defined, it cannot run. This often
occurs when users rename the Backup Server from the default name. To
determine whether this is your problem, run the following isql statement:
1> select * from master..sysservers
2> go
If the srvnetname column does not list the name you use for your Backup
Server, change it by performing one of the following:
* Create an alias in your Backup Server RUN_SERVER file
-S
* Modify the network name in the interfaces file using the installation
utility for your platform.
* Use sp_addserver to update the network name for SYB_BACKUP.
1> sp_dropserver SYB_BACKUP
2> go
1> sp_addserver SYB_BACKUP, null,
2> go
----------------------------------------------------------------------------
----------------------------------------------------------------------------
FAQs about xp_server
Background
XP Server, introduced with Adaptive Server Enterprise 11.5, is an Open
Server application which runs on the same machine as Adaptive Server and
allows you to implement Extended Stored Procedures (ESPs). An extended
stored procedure contains procedural language code to carry out functions
and tasks external to the server in response to events occurring within the
server. For example, the system ESP xp_cmdshell lets you issue an operating
system command from within Adaptive Server.
This TechNote clarifies issues on the use and management of XP Server.
Frequently Asked Questions
What variables or path names do I need to define for XP Server?
You need to define the location where extended stored procedures reside. On
UNIX platforms, the $SYBASE/lib path must be defined. For additional
information on how to do this, refer to Installing Sybase Adaptive Server
Enterprise and OmniConnect on Unix Platforms .
On NT, include the DLL directory in the PATH.
Upon starting Adaptive Server, why don't I see XP Server running?
Starting Adaptive Server does not start XP Server, nor do you need to start
it yourself. Adaptive Server starts XP Server automatically when the first
extended stored procedure is run.
Note: XP Server does not have a separate RUN_SERVER file of its own.
However you can still start XP Server without a RUN_SERVER file, for
troubleshooting purposes, as described later in this TechNote.
Why do I get Error Message 11018 ("XP Server must be up for ESP to
execute"), and what does it mean?
Error 11018 is raised when Adaptive Server is unable to contact XPServer to
execute an ESP. Check that your interfaces file (UNIX) or sql.ini file (NT)
has the correct entry for XP Server; the XP Server name must be in all
upper
case letters and have the format SERVER_NAME_XP . Check also that the
server
name appears in sysservers, using the isql command:
1> select srvname from sysservers
2> go
Retry the ESP after making any necessary corrections.
Sometimes XP Server is not started the first time you issue an ESP. To
start
XP Server, shut down and restart Adaptive Server, and reissue the ESP. This
is only necessary after the initial installation of Adaptive Server.
Why do I get kernel error "XP Server failed to start. Try bringing up XP
Server manually. Check SQL Server documentation on how to bring XP Server
up"?
This kernel error means that XP Server is unable to start because the
XPServer port is in use by another process. To investigate the port
assignment:
1. Identify the port number assigned to XP Server by looking in the
interfaces file (UNIX) or sql.ini file (NT).
2. Check if another XP Server process (or any other process) is using the
same port number by entering:
netstat -a | grep (UNIX)
netstat -a | more (NT)
If the port number is present as a local address from the netstat
output, you may not use that port for the XP Server because the port
is
already in use by another process.
3. To determine whether the XP Server port is in use, try running
XPServer
manually using the following command:
xpserver -S
XP Server will not start if the XP Server port number is in use.
If you find processes using the same port number:
1. If a stale XP Server process was using the port number, terminate the
process:
o On UNIX, use the operating system kill command.
o On NT, press ctrl+alt+delete and click the Task Manager button to
open the Windows NT Task Manager window. Select the xpserver
process you want to terminate and click the End Process button.
Otherwise, if a process other than XP Server was using the port, use
another port number for XPServer by modifying the interfaces file
(UNIX) or sql.ini file (NT).
2. Start XP Server manually to verify that it can start successfully:
xpserver -S
3. Terminate the XP Server you started manually.
If you find no processes using the same port number:
1. Restart Adaptive Server.
2. Return to executing the ESP. XP Server should start automatically.
When using xp_cmdshell , I get the message "User access denied. Failed to
change the user context." What should I do?
This message means that you cannot execute the command because of the
current setting of your xp_cmdshell security context. To verify your
xp_cmdshell context issue the following isql command:
1> sp_configure "xp_cmdshell context"
2> go
The Run Value in the output is your current xp_cmdshell context. See the
System Administration Guide for possible values of this configuration
variable, then set the appropriate context and retry xp_cmdshell .
Where can I find the XP Server log file?
By default, the XP Server log file is located in the $SYBASE/install
directory (UNIX) or %SYBASE\install (NT). The naming convention is .log.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
SQL Server and ASE Maximum Shared Memory
No matter how many bits of addressing the hardware allows, Sybase
SQL Server is a 32 bit process and can address no more than 4GB of
memory. The maximum shared memory allowed on a system is the lesser
of:
* 1. 4GB accessible to a 32 bit process, or
* 2. The maximum allowed by the operating system. For example, many
operating systems use signed pointer values which only give you
31 bits. That would limit an application to 2GB of memory.
ASE is in the process of accomodating a 64 bit O/S
How SQL Server and ASE uses SHMMAX and SHMSEG
---------------------------------------------------------------------------
--------------------
SHMMAX represents the maximum size of a single shared memory
(SHMSEG) in bytes. SQL Server uses multiple segments if the
sp_configure memory values exceeds 1GB on some Operating
Systems.
SQL Server does support multiple shared memory segments on HP.
For example, to make use of 1.75 GB of shared memory, the O/S
parameter SHMMAX may be set to 0x40000000 using SAM. Specifically,
in this example of configuring memory for 1.75GB, given the O/S
limit of 1GB for shmmax, SQL Server creates and attaches a 1GB
shared memory segment, then creates and attaches a second .75GB
shared memory segment.
----------------------------------------------------------------------------
Other Useful Information
--------------------------------------
* SQL Server 11.x Bug Report
http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=2434
This report contains critical, severe and annoying bugs found in
Sybase SQL Server 11, versions 11.0, 11.0.1, 11.0.1.1, 11.0.2,
11.0.2.1, 11.0.2.2 and 11.0.3. It includes a description of the
bug and a workaround if one has been documented.
* Certification Reports
http://techinfo.sybase.com/css/techinfo.nsf/Certification
Browse by Certification
* Product Availability
http://techinfo.sybase.com/css/techinfo.nsf/New+Products
These Alerts feature new Sybase products which became available
for sale during the time span listed on the Alert. Included are
instructions on actions you need to take and an explanation of
abbreviations.
* End-of-Life/End-of-Support
http://techinfo.sybase.com/css/techinfo.nsf/Topic
Collection of end-of-support letters to customers having contracts
for various products on different platforms. Each letter describes
the end-of-support plan for these products.
* Error Messages
http://techinfo.sybase.com/css/techinfo.nsf/Topic
* Migrating to Sybase Adaptive Server Enterprise 11.5, a guide to help
you migrate from 4.x, 10.x and 11.0.x, including templates and
checklists.
http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=34982-01-1150-01
A guide to help you migrate from 4.x, 10.x and 11.0.x, including
templates and checklists.
* What's New
http://techinfo.sybase.com/css/techinfo.nsf/Whats+New
Browse the most recently released documents.
For frequent updates, visit the Sybase Technical Documents on the
World Wide Web at http://techinfo.sybase.com.
Electronic Case Management and Electronic Software Delivery access remains
limited to Sybase support contacts who are registered with SupportPlus
Online Services. ECM and ESD requires SSL web browser support. ECM and ESD
users also may access the Technical Information Library from behind the
firewall in the usual manner, allowing them to retain the security benefits
of using SSL with their web browser.
Subscription Information
--------------------------------------
Send comments to technews@sybase.com. To receive this document by regular
email, send email to majordomo@sybase.com, and in the body of your message
put subscribe inews-technews-full. For a summary version by email, put
subscribe inews-technews-summary. No subject line is necessary.
Staff
Principal Editor: Betsy Brazy
Production Editors: Deborah Blakeney, Rodney Esperanza, Leigh Ann Hussey
Contributors: Sandra Dellafiora, Bret Halford, Vinaye Misra, Carol Talbeck,
Eoin Woods
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.
----------------------------------------------------------------------------
--------------------------------------------------
If you ever want to remove yourself from this mailing list, you can send
mail to "majordomo@sybase.com" with the following command in the body of
your email message:
unsubscribe inews-technews-full
If you ever want to add yourself from this mailing list, you can send
mail to "majordomo@sybase.com" with the following command in the body of
your email message:
subscribe inews-technews-full