----------------------------------------------------------------------------
SYBASE TECHNICAL NEWS
Volume 7, Number 9 September 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 Library CD and
web pages.
----------------------------------------------------------------------------
In This Issue
--------------------------------------
Tip of the Month
TechNotes:
* FAQs about dbcc
http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20036
Doc Summary:
This FAQ contains answers to frequently asked questions about dbcc.
* Setting Up dbccdb
http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20266
Doc Summary:
These "quick start" steps orient you to dbccdb setup and refer to
documentation details.
* Moving syslogins to Another Server
http://techinfo.sybase.com/css/techinfo.nsf/DocId/ID=20269
Doc Summary:
This document describes how to move the contents of syslogins table to
another server. However, if your platforms are different, you may need
to take additional steps because of platform differences in default
character sets and password encryption.
Other Useful Information
Subscription
----------------------------------------------------------------------------
Tip of the Month: Recovering a Suspended Log
----------------------------------------------------------------------------
Log suspends can occur when last-chance thresholds are reached on the
log segment. This tip describes how to recover or un-suspend a suspended
log, including a new command in Adaptive Server Enterprise 11.9.x.
Prevent suspended logs by regularly dumping logs that use
sp_thresholdaction. See the chapter "Managing Free Space with Thresholds" in
the Adaptive Server Enterprise System Administration Guide.
RECOVERING a SUSPENDED LOG
How you restart a suspended log depends on whether you believe that you can
add enough transaction log space for the transaction to complete.
If you can add enough log space, perform the following alter database
statement and let the transaction run:
1> alter database "mydatabase", log on mydatabaselog
2> go
If you know you cannot add enough log space, kill or abort the process and
dump the transaction. Each choice has its advantages:
* Killing the process allows you to keep the transaction log but may be
time-consuming.
* Aborting the transaction is quicker but does not preserve the
transaction log.
The following sections describe how to perform these actions.
Un-suspending a log by killing a process
1. Free the transaction log space held by large or long-running
transactions:
1. Find the process id (spid) of the oldest open transaction in a
log:
1> select spid from master..syslogshold
2> where dbid = ID /* is id of database with LOG SUSPENDed users */
3> go
2. Using the spid returned from the previous select statement, kill
the transaction process. This cancels the transaction to rollback.
This step could take some time.
kill spid
3. After the transaction process is killed, perform dump transaction
for the database with LOG SUSPENDed users. You can detect the kill
by monitoring the master..syslogshold table for a change in spid
for that dbid).
2. Run sp_who to verify that the processes are not suspended.
sp_who
Examine the output. If LOG SUSPENDed users still exist, repeat these
steps.
Un-suspending a log by aborting the transaction
1. Log into isql.
2. Abort the transaction and truncate the log. The checkpoint command
performs the abort:
1> sp_dboption db_name, "abort tran on log full", true
2> go
1> sp_dboption "trunc log on chkpt", true
2> go
1> use db_name
2> checkpoint
3> go
Un-suspending a log by aborting the transaction with lct_admin abort
(Adaptive Server 11.9.x only)
Sybase introduced a new command in Adaptive Server 11.9.x, lct_admin abort,
to terminate suspended transactions at the beginning of a log.
1. Find the spid of the transaction process:
1> select spid from master..syslogshold
2> where dbid = ID /* is id of database with LOG SUSPENDed users */
3> go
2. Abort the transaction process. Only a System Administrator can use
lct_admin abort.
1> select lct_admin("abort", spid, dbid)
2> go
This terminates and closes the suspended transaction.
3. Perform dump transaction for the database with LOG SUSPENDed users.
For more information about lct_admin abort, see the "System Administration
Changes" chapter in New Functionality in Sybase Adaptive Server Enterprise
11.9.2 .
TechNotes
-----------------------------------------------------------------------------
FAQs about dbcc
-----------------------------------------------------------------------------
Contents
This FAQ answers the following questions:
* When should I run dbccs?
* How long does it take to run dbccs?
* How can I make dbccs run faster?
* Why is the dbcc memusage command not recommended in a production
environment?
* What are the available dbcc trace flags and what do they do?
* What are the new dbcc commands in 11.5?
* What dbcc commands have become obsolete?
* What does each dbcc command do?
* Where can I get more information on dbccs?
WHEN SHOULD I RUN dbccs?
Run dbccs when they will least impact your production environment.
See also TechNote 920 FAQs about Database Dumps and Loads.
HOW LONG DOES IT TAKE TO RUN dbccs?
The length of time it takes to run dbccs varies widely from platform to
platform. However, there are formulas which will give you approximate
numbers to work with.
For dbcc checktable, the formula is: [time to do one io] * [number of rows
in table] * [number of indexes on table]
Time to do dbcc checkdb will be the sum of the checktable times for all of
the tables in the database.
dbcc checkalloc does approximately one io for each allocated page in the
database.
HOW CAN I MAKE dbccs RUN FASTER?
A frequent reason given for not running dbcc's regularly is that they "take
too long". In order to run dbccs more frequently and more efficiently you
can break down dbcc checkalloc and checkdb into separate sets of dbcc
tablealloc and dbcc checktable.
Single User and Fix options
Only dbcc checkalloc and tablealloc/indexalloc use the fix option. Moreover,
it is only necessary to run them in single user mode with the fix option if
you are running them on system tables.
Technique
On a given day run a certain number of checktable and tablealloc commands
for a portion of the database. On subsequent days run different tables. Over
a period of days you can accomplish a complete check of your databases for
integrity.
For example if your database has 200 tables in addition to the system
tables, run dbccs on the system tables on night one, run dbccs on each of
the first 50 of the user tables on night two, the next 50 the next night and
so on, until at the end of five nights you have checked every table in the
database. On the sixth night you can begin the cycle again.
See the System Administration Guide for more information on dbcc strategies.
WHY IS THE dbcc memusage COMMAND NOT RECOMMENDED IN A PRODUCTION
ENVIRONMENT?
The dbcc memusage command locks memory, thereby restricting memory access to
other processes.
WHAT ARE THE AVAILABLE dbcc TRACEFLAGS AND WHAT DO THEY DO?
You set traceflags using
dbcc { traceon | traceoff } (traceflag)
See the Sybase Adaptive Server Enterprise Reference Manual or Sybase
Adaptive Server Enterprise Troubleshooting and Error Messages Guide for more
information on setting traceflags.
Table 1:
Traceflag Description
302 Prints index selection. See the Performance & Tuning Guide, and
Analyzing, Handling and Resolving Optimizer Problems/Symptoms.
310 Prints join selection. See Analyzing, Handling and Resolving
Optimizer Problems/Symptoms.
1204 Prints deadlock information.
1205 Prints deadlock information by printing stacks for the affected
spids. Intended for use by Sybase engineering. 1204 is preferred.
Turns off async i/o and forces standard unix io. Can be useful if
1603 symptoms include transient corruption errors that may be caused
by bad drives or controllers.
1608 Instructs server engine 0 to not on-line any other dataserver
engines.
1610 tcp no delay. See the System Administration Guide for more
information.
2512 Instructs dbcc checkalloc to skip the syslogs table during
processing.
Instructs dbcc checkalloc, tablealloc and indexalloc to check for
2513 whether foreign objects are stranded on a particular segment
within a database.
3604 Directs output from dbcc commands to window where the command is
executed. Refer to Performance & Tuning Guide.
3605 Directs output from dbcc commands to the Server error log.
Instructs the dbcc monitor command to insert a valid description
8399 into the field_name column in the sysmonitors table. Not intended
for use by users. Appears in the errorlog when sp_sysmon is used.
WHAT ARE THE NEW dbcc COMMANDS IN 11.5?
dbcc has been enhanced to improve its performance in parallel environments
and to expand the way in which it detects, records, and reports errors. The
dbcc checkstorage command allows you to check a database that is in use,
with little or no impact on performance. It stores the results of the check
in the dbccdb database. When the check is complete, you can use the new dbcc
stored procedures to generate reports based on the data that is collected.
See What's New in Adaptive Server 11.5 for more information on new dbcc
commands.
WHAT dbcc COMMANDS HAVE BECOME OBSOLETE?
The following dbcc commands were removed as of release 11.0.2.1:
* ind -- Display every index page for a table
* tab -- Print each data page for a table
WHAT DOES EACH dbcc COMMAND DO?
Command: allocdump
Desciption: Displays all the relevant information about extents for a given
allocation page.
Command: checkalloc
Description: Detects allocation errors within a database.
Command: checkcatalog
Description: Checks the consistency of the system tables for a database.
Command: checkdb
Description: Validates the integrity of a data and index pages for each
table within a database.
Command: checkstorage
Description: Records dbcc checkstorage activity and results in the dbccdb
database, which allows trend analysis and a source of accurate
diagnostic information for support. See the Sybase Adaptive
Server Reference Manual for more information.
Command: checktable
Description: Validates the integrity of a data and index pages for a table.
Command: extentcheck
Description: Displays all extents within a database which are used by an
object.
Command: extentdump
Description: Displays the extent structure for a given page number.
Command: findstranded
Description: Searches for non-syslog extents on logsegments.
Command: gettrunc
Description: Displays the current status of the log transfer state for a
database.
Command: help
Description: Displays the syntax for the specified dbcc command.
Command: indexalloc
Description: Validates and/or fixes the integrity of an object's allocation
for a specific indid.
Command: listoam
Description: Displays the oam page(s) information for an object.
Command: lock
Description: Displays the table, page, address and semaphore locks that are
currently in use.
Command: log
Description: Displays log records.
Command: memusage
Description: Displays information on Adaptive Server's memory usage
Command: page
Description: Prints the contents of a page within a database.
Command: pglinkage
Description: Traverses a page chain validating its integrity.
Command: settrunc
Description: Modifies the Log Transfer Manager (LTM) information for a
database.
Command: tablealloc
Description: Validates and/or fixes the integrity of an object's
allocation.
Command: textalloc
Description: Validates and/or fixes the integrity of an object's allocation
for text and image columns indid.
Command: traceflags
Description: Displays traceflags enabled on the Server.
Command: traceoff
Description: Disables server trace flags.
Command: traceon
Description: Enables server trace flags.
Command: tune
Description: Modifies the current runtime values for certain SQL server
configuration options on a server wide or object basis.
WHERE CAN I GET MORE INFORMATION ON dbccs?
* Sybase Adaptive Server Enterprise System Administration Guide, Chapter
18, Checking Database Consistency.
* Sybase Adaptive Server Enterprise Troubleshooting and Error Messages
Guide, Chapter 2, Encyclopedia of Tasks.
* Sybase Adaptive Server Enterprise Reference Manual, Chapter 1,
Transact-SQL Commands, and Chapter 6, dbcc Stored Procedures.
----------------------------------------------------------------------------
Setting Up dbccdb
----------------------------------------------------------------------------
This technote gives a "quick start" view of how to set up the dbccdb
database. When you run the dbcc command with the checkstorage option, it
uses this database to store results. The setup steps include:
* Determine Size
* Initialize Disk Devices
* Create dbccdb
* Install Stored Procedures
* Evaluate Configuration
* Configure Adaptive Server
* Make dbccdb Changes
* Run dbcc checkstorage
* "Follow up In Case of Problems"
Before performing these steps, be sure to review the details on preparing to
use dbcc checkstorage in the System Administration Guide.
DETERMINE SIZE
The largest database determines what size the dbccdb database needs to be.
The dbcc checkstorage command estimates a ten percent database growth rate
over time. In general, making the dbccdb database one percent of your
largest database is adequate. If the growth rate of your database is higher,
however, consider making dbccdb two percent of the largest database.
To find out the recommended size for dbccdb for each database, run the
stored procedure sp_plan_dbccdb.
Here is an example of the information that sp_plan_dbccdb provides:
Recommended size for dbccdb database is 15MB (data = 13MB, log = 2MB).
Recommended devices for dbccdb are:
Logical Device Name Device Size (KB)
------------------- ---------------
dbccdev 20480
Recommended values for workspace size, cache size, and process count are:
dbname scan ws text ws cache process count
master 80K 48K 640K 1
tempdb 80K 48K 640K 1
model 64K 48K 640K 1
sybsystemprocs 560K 144K 640K 1
sybsystemdb 80K 48K 640K 1
mytest 13520K 3392K 3383K 2
INITIAL DISK DEVICES
Use disk init to initialize data and log devices on a specific database for
the dbccdb data and log.
CREATE dbccdb
Create dbccdb on the initialized disk devices, using this syntax:
create database dbccdb on device=size
INSTALL STORED PROCEDURES
Install the dbcc stored procedures, using this syntax:
isql -Usa -Ppassword -Sserver -iinstalldbccdb -e/tmp/error.out
EVALUATE CONFIGURATION
Once you have installed the stored procedures, use the sp_dbcc_evaluatedb
command to evaluate your configuration. For example:
use dbccdb go sp_dbcc_evaluatedb "mytest" go
The output lists both current and recommended values for workspace size,
cache size, and process count, enabling you to make adjustments as needed.
Here is an example of sp_dbcc_evaluatedb output:
database name : mytest
current scan workspace size : 13520K suggested size : 13250K
current scan text workspace size : 3072K suggested size : 3072K
current cache size : 3072K suggested size : 5344K
current process count : 1 suggested count : 2
CONFIGURE ADAPTIVE SERVER
Set the number of worker processes configuration option. Based on the
example output above, where the suggested process count is 2, you would set
the value as follows:
sp_configure "number of worker processes",2
Site-specific considerations are as follows:
* Based on the max worker processes value, set the sum of processes high
enough.
* The lower the value of number of worker processes, the more dbcc
checkstorage performance and resource consumption is reduced.
* If you are using parallelism, remember:
o number of worker processes >= max parallel degree
o max parallel degree >= max scan parallel degree
Note: Once you set number of worker processes, shut down and then restart
the server.
MAKE dbccdb CHANGES
The newly set up dbccdb database requires these changes:
Initialize dbcc_config
Initialize the dbcc_config table for the target database:
sp_dbcc_updateconfig target_database, "max worker processes",1
sp_dbcc_updateconfig target_database, "dbcc named cache", default_data_cache, "1M"
Create and Initialize Workspaces
Create and initialize the scan and text workspaces for dbccdb, based on the
suggested size for scan and text workspaces given in sp_dbcc_evaluatedb
output. Multiply these sizes by enough to accommodate database growth. For
example, if you anticipate that the database will triple in size across time
and the suggested scan workspace size is 13.5MB, generously triple it as
follows:
sp_dbcc_createws "dbccdb", "default data segment", scan_workspace_name, scan, "50M"
sp_dbcc_createws "dbccdb", "default data segment", text_workspace_name, text, "10M"
The scan and text workspaces are where dbcc checkstorage does its work.
UPDATE INFORMATION
Update dbccdb information, using the parameters you just specified for
target_database, scan_workspace_name, and text_workspace_name. The syntax
is:
sp_dbcc_updateconfig target_database, "scan workspace", scan_workspace_name
sp_dbcc_updateconfig target_database, "text workspace", text_workspace_name
If you need to reconfigure the workspace sizes, use sp_dbcc_alterws.
Run dbcc checkstorage
Make sure that you can run dbcc checkstorage. The syntax is:
dbcc checkstorage(target_database)
Note: You can run dbcc checkstorage concurrently.
For example, here is dbcc checkstorage output for a target database called
mytest:
checking mytest Storage checks for 'mytest' are complete. DBCC is now
recording the results of DBCC CHECKSTORAGE for database 'mytest' sequence
55 completed at Jul 15 1998 4:04:58. 0 faults and 0 suspect conditions
were located. 0 checks were aborted.
You should investigate the recorded faults, and plan a course of action
that will correct them.
If there is a potential problem, the output would be:
Suspect conditions are to be treated as faults if the
same suspect conditions persists in subsequent CHECKSTORAGE operations,
or if they are also detected by other DBCC functions.
FOLLOW UP IN CASE OF PROBLEMS
If the checkstorage output suggests problems, use any of the following means
to determine causes:
* Run sp_dbcc_faultreport "short" or sp_dbcc_faultreport "long" (more
detail than "short"). For details, see the section on generating
reports from dbccdb in the System Administration Guide.
* Query the dbcc_faults and dbcc_types tables. For details, see the
chapter on dbccdb tables in the System Administration Guide.
* Run dbcc checkstorage again, and then either run the
sp_dbcc_differentialreport or compare the last two reports from
sp_dbcc_faultreport.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Moving syslogins to Another Server
Methods for Moving syslogins
You can move syslogins in one of the following ways:
* By bcp
* By script
You can use bcp to move syslogins to the same or a similar platform.
However, syslogins contains encrypted passwords, and encryption may work
differently on different platforms. Therefore, if you are moving the logins
from UNIX to Windows NT, you cannot use bcp, but must use sp_addlogin
manually or automatically in a script.
The following sections describe the procedures.
By bcp
This section describes using bcp to move logins to another server on the
same platform.
Same server versions
For example, when moving from 11.0.x to another 11.0.x server, or from
11.5.x to another 11.5.x server.
1. Using the old server, bcp out syslogins to a text file,
copysyslogins.txt.
bcp master..syslogins out copysyslogins.txt -c -Usa -P
WARNING! This method fails when using different versions because of
changes to syslogins.
2. Log into the new (target) server and reconfigure to allow updates to
system tables.
sp_configure "allow updates to system tables", 1
go
3. Bcp in copysyslogins.txt the new server with a batch size of 1 to
eliminate duplication of system logins. Otherwise, bcp fails on the
first row for "sa" (suid 1).
bcp master..syslogins in copysyslogins.txt -c -b1 -Usa -P
4. Turn off "allow updates."
sp_configure "allow updates to system tables", 0
go
Moving to Adaptive Server 11.5.x
Sybase introduced a new syslogins column, "srvname" in Adaptive Server. Use
this method when moving logins from SQL Server 11.0.x to Adaptive Server
11.5.x on the same platform.
1. Log into isql on the 11.0.x server.
isql -Usa -P
2. Run the following script to create a temporary table with an extra
column for srvname.
select *, convert(varchar(30),null) as "srvname"
into tempdb..mybcpout
from master..syslogins
go
3. Log out of isql.
quit
4. Using the old server, bcp out the temporary table, mybcpout, to a text
file, copysyslogins.txt.
bcp out tempdb..mybcpout copysyslogins.txt -c -Usa -P
5. Log into the target 11.5.x server and reconfigure to allow updates to
system tables.
sp_configure "allow updates to system tables", 1
go
6. Bcp in to the 11.5.x server with a batch size of 1 to eliminate
duplication or overwrite of system logins such as "sa" and "probe."
Otherwise, bcp fails on the first row (suid 1).
bcp master..syslogins in copysyslogins.txt -c -b1 -Usa -P
7. Turn off "allow updates."
sp_configure "allow updates to system tables", 0
go
See also Technical Document 10827, "Synchronizing Logins Between Servers by
BCPing syslogins".
Which Version of bcp?
* Using bcp 10.0.4
Bcp 10.0.4 is certified to work with Adaptive Server Enterprise 11.5.x
and Sybase SQL Server 11.0.x.
* Using bcp 11.1.1
If you use bcp 11.1.1, you must have the following EBFs installed:
o Solaris - 7883
o Digital UNIX - 7887
o AIX - 7884
o HP - 7885
o Windows NT - 7886
o Windows 3.1 - 7888
EBFs are available from the Electronic Software Distribution section of
Sybase's customer support website, http://support.sybase.com . You must
be a registered user to download EBFs.
By script
A login-creation script has several advantages:
* Platform-independent.
* Easy to add users to a server.
* Automates maintenance.
WARNING! Sybase recommends you restrict access of this file only to system
administrators because it contains passwords.
Using a script
1. Create a script, loginscript.txt, of user logins and passwords. For
example:
sp_addlogin "loginame1", "password1"
go
sp_addlogin "loginame2", "password2"
go
2. Log into the new server and run the login script. For example:
isql -Usa -P -i loginscript.txt
Note: This script resets passwords to the original password. Remember to
alert users that their passwords were reset.
----------------------------------------------------------------------------
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/eolproducts
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
* SQL Server 11 Migration Guide: Moving from 10.x or 4.x to 11.x,
including templates and checklists.
http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=36066-01-1100-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 Library 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 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
Kalpana Aravamuthu, Steven Bologna, Shelly Hand, Kip Johannsen, Mike Maas,
Vinaye Misra, Victor Moy, Tracy Sedgwick, Michael Shields, Carol Talbeck,
Jennifer Tifft
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