Sybase FAQ: 1/17 - index
Pablo Sanchez
URL: http://reality.sgi.com/pablo/Sybase_FAQ
Sybase Frequently Asked Questions supported by Silicon Graphics
_Adaptive Server Enterprise FAQ 2000, version 3 released 12/08/98_
_________________________________________________________________
Keyword and Phrase Search
Enter search words/phrases: ____________________ ___ Ignore case?
Help!
_________________________________________________________________
Index of Sections
* Section 0: Acknowledgements & What's New in this Release
* Section 1: SQL Server Administration
* Section 2: User Database Administration
* Section 3: DBCC's
* Section 4: isql
* Section 5: bcp
* Section 6: SQL Fundamentals
* Section 7: SQL Advanced
* Section 8: Performance and Tuning
* Section 9: Freeware
* Section 10: Sybase Technical News
* Section 11: Web Links
* Section 12: Miscellany
_________________________________________________________________
To get a text version of this FAQ:
ftp://sgigate.sgi.com/pub/Sybase_FAQ/FAQ_txt.Z
To get the HTML for this FAQ:
ftp://sgigate.sgi.com/pub/Sybase_FAQ/FAQ_html.tar.Z
_________________________________________________________________
SQL Server Administration
1.1) How do I start/stop SQL Server when the CPU reboots?
1.2) How do I clear a log suspend'ed connection?
1.3) What's the best value for cschedspins?
1.4) What traceflags are available?
1.5) How do I use traceflags 5101 and 5102?
1.6) What is cmaxpktsz good for? see also Q1.8
1.7) How do I move tempdb off of the master device?
1.8) What do all the parameters of a a buildmaster -d<device> -yall
mean?
1.9) How do I correct timeslice -201?
1.10) What is a SQL Server anyway?
1.11) The how's and why's on becoming a Certified Sybase Professional
(CSPDBA)?
1.12) RAID and Sybase
1.13) How to swap a db device with another
1.14) Server naming and renaming
1.15) How can I tell the datetime my Server started?
1.16) Raw partitions or regular files?
1.17) Is Sybase Y2K (Y2000) compliant?
_________________________________________________________________
User Database Administration
2.1) Changing varchar(m) to varchar(n)
2.2) Frequently asked questions on Table partitioning 2.3) How do I
turn off marked suspect on my database?
2.4) How do I manually drop a table?
2.5) Why not create all my columns varchar(255)?
2.6) What's a good example of a transaction?
2.7) What's a natural key?
2.8) Making a Stored Procedure invisible
2.9) Saving space when inserting rows monotonically
2.10) How to compute database fragmentation
2.11) Tasks a DBA should do...
2.12) How to implement database security
2.13) How to shrink a database
_________________________________________________________________
DBCC's
3.1) How do I set TS Role in order to run DBCC ...?
3.2) What are some of the hidden/trick DBCC commands?
3.3) The unauthorized DBCC list with doco - see Q11.4.1
3.4) Fixing a Munged Log
3.5) Another site with DBCC commands - see Q11.4.2
_Performing any of the above may_ corrupt your SQL Server. Please do
_not_ call Sybase Technical Support after screwing up your SQL
Server. Remember, _always_ take a dump of the master database and
any other databases that are to be affected.
_________________________________________________________________
isql
4.1) How do I hide my password using isql?
4.2) How do I remove row affected and/or dashes when using isql?
4.3) How do I pipe the output of one isql to another?
_________________________________________________________________
bcp
5.1) How do I bcp null dates?
5.2) Can I use a named pipe to bcp/dump data out or in?
5.3) How do I exclude a column?
_________________________________________________________________
SQL Fundamentals
6.1) Are there alternatives to row at a time processing?
6.2) When should I execute an sp_recompile?
6.3) What are the different types of locks and what do they mean?
6.4) What's the purpose of using holdlock?
6.5) What's the difference between an _update in place_ versus a
_deferred update_? - see Q8.9
6.6) How do I find the oldest open transaction?
6.7) How do I check if log truncation is blocked?
6.8) The timestamp datatype
6.9) Stored Procedure Recompilation and Reresolution
_________________________________________________________________
SQL Advanced
7.1) How to emulate the Oracle decode function/crosstab
7.2) How to implement if-then-else within a select-clause.
7.3) _deleted due to copyright hassles by the publisher_
7.4) How to pad with leading zeros an int or smallint.
7.5) Divide by zero and nulls. 7.6) Convert months to financial
months. 7.7) Hierarchy traversal - BOMs. 7.8) Is it possible to call a
UNIX command from within a stored procedure or a trigger?
7.9) Information on Identities and Rolling your own Sequential Keys
_________________________________________________________________
Performance and Tuning
8.1) What are the nitty gritty details on Performance and Tuning?
8.2) What is best way to use temp tables in an OLTP environment?
8.3) What's the difference between clustered and non-clustered
indexes?
8.4) Optimistic versus Pessimistic locking?
8.5) How do I force an index to be used?
8.6) Why place tempdb and log on low numbered devices?
8.7) Have I configured enough memory for my SQL Server?
8.8) Why should I use stored procedures?
8.9) I don't understand showplan's output, please explain.
8.10) Poor man's sp_sysmon.
8.11) View MRU-LRU procedure cache chain. 8.12) Improving Text/Image
Type Performance
_________________________________________________________________
Freeware
9.1) sp_freedevice - lists device, size, used and free.
9.2) sp_whodo - augments sp_who by including additional columns: cpu,
I/O...
9.3) SQL and sh(1)to dynamically generate a dump/load database
command.
9.4) SybPerl - Perl interface to Sybase.
9.5) dbschema.pl - SybPerl script to take a logical snap of a
database.
9.6) Sybtcl - TCL interface to Sybase.
9.7) Augmented system stored procedures.
9.8) Examples of Open Client and Open Server programs -- see Q11.4.14.
9.9) SQL to determine the space used for an index.
9.10) xsybmon - an X interface to sp_monitor
9.11) sp_dos - This procedure graphically displays the scope of a
object
9.12) sqsh - a superset of dsql with local variables, redirection,
pipes and all sorts of goodies.
9.13) sp_getdays - returns days in current month.
9.14) ddl_insert.pl - creates insert DDL for a table.
9.15) sp_ddl_create_table - creates DDL for all user tables in the
current database
9.16) int.pl - converts interfaces file to tli
9.17) How to access a SQL Server using Linux see also Q11.4.6
9.18) sp__revroles - creates DDL to sp_role a mirror of your SQL
Server
9.19) sp__rev_configure - creates DDL to sp_configure a mirror of your
SQL Server
9.20) sp_servermap - overview of your SQL Server
9.21) sp__create_crosstab - simplify crosstable queries
9.22) update statistics script
9.23) lightweight Sybase Access via Win95/NT
9.24) Sybase on Linux Linux Penguin
9.25) sp_spaceused_table
_________________________________________________________________
Sybase Technical News
10.1.1) Volume 3, Number 2
10.1.2) Volume 3, Number 3
10.1.3) Volume 3, Number 4
10.2.1) Volume 4, Number 1
10.2.2) Volume 4, Number 2
10.2.3) Volume 4, Number 3
10.2.4) Volume 4, Number 4
10.3.1) Volume 5, Number 1
10.3.2) Special Supplement -- Migration to System 11
10.3.3) Volume 5, Number 2
10.3.4) Volume 5, Number 3
10.3.5) Volume 5, Number 4
10.4.1) Volume 6, Number 1
10.4.2) Volume 6, Number 2
10.4.3) Volume 6, Number 3
10.4.4) Volume 6, Number 4
10.4.5) Volume 6, Number 5
10.4.6) Volume 6, Number 6
10.4.7) Volume 6, Number 7
10.4.8) Volume 6, Number 8
10.4.9) Volume 6, Number 9
10.5.1) Volume 7, Number 1
10.5.2) Volume 7, Number 2
10.5.3) Volume 7, Number 3
10.5.4) Volume 7, Number 4
10.5.5) Volume 7, Number 5
10.5.6) Volume 7, Number 6
10.5.7) Volume 7, Number 7
10.5.8) Volume 7, Number 8
10.5.9) Volume 7, Number 9
10.5.10) Volume 7, Number 10
10.5.11) Volume 7, Number 11
10.5.12) Volume 7, Number 12
_________________________________________________________________
Web Links
_Academia_
11.1.1) Yale Centre for Medical Informatics
http://paella.med.yale.edu/topics/database.html
11.1.2) NC State University http://www.acs.ncsu.edu:80/Sybase
11.1.3) Simon Fraser University
http://www.cs.sfu.ca/CourseCentral/Software/Sybase
11.1.4) University of California
http://www-act.ucsd.edu/webad/sybase.html
11.1.5) Rutgers http://paul.rutgers.edu/sybase.html
_Sybase Resources_
11.2.1) Pacific Rim Network Systems Inc Sybase Resource Links
http://www.alaska.net/~pacrim/sybase.html
11.2.2) The Sybase Contractors' Resource Page by Magnum Solutions
http://www.mag-sol.com/Sybase
11.2.3) The SQL Workshop http://www.sqlworkshop.com
11.2.4) SQL Server and Rep Server on NT
http://www.xs4all.nl/~reinoud/ntsqlrep.html
11.2.5) Sybase Replication Quick Reference guide - see Q11.4.16
_Books, Magazines and Articles_
11.3.1) Sybase Books http://sybooks.sybase.com
11.3.2) Intro to Sybase Architecture -
http://www2.dgsys.com/~dcasug/sybintro/intro.html
11.3.3) Papers from SQL Forum http://www.sqlforum.com/sybart.htm
11.3.4) ASE 11.9 Optimizer Statisitics
ftp://sgigate.sgi.com/pub/Sybase_FAQ/mamet.pdf [276K ]
_Shareware_
11.4.1) The unauthorized documentation of DBCC by Al Huntley
http://user.icx.net/~huntley/dbccinfo.htm
11.4.2) More DBCC's by KaleidaTech Associates, Inc. -
http://www.kaleidatech.com/dbcc1.htm
11.4.3) sybinit4ever: Sybase ASE 11.5 ASCII-only server creation tool
- see Q11.4.16
11.4.4) Sybase Freeware and Shareware
http://www.tiac.net/users/sqltech
11.4.5) Peter Thawley's '97 ISUG Talk
ftp://sgigate.sgi.com/pub/Sybase_FAQ/Thawleyhndout.ppt.ps.Z [3670K]
11.4.6) DBI/DBD:Sybase on Linux
http://www.brodeur.com/~pjacob/dbdsybase
11.4.7) BusinessObjects FAQ -
http://www.upenn.edu/computing/da/bo/busob-faq.html
11.4.8) Sybase Scheme Extensions -
http://www.cs.indiana.edu/scheme-repository/ext.html
11.4.9) SQShell SQL shell for Unix by Scott Gray
http://www.voicenet.com/~gray/sqsh.html
11.4.10) A login widget for Sybase
http://ftp.digital.com/pub/plan/perl/CPAN/CPAN.html#sybase
11.4.11) ISUG's Freeware Collection
http://www.isug.com/ISUG2/links.html
11.4.12) Sybase to HTML Converter
http://www.algonet.se/~bergkarl/lasse/scripts_eng.html
11.4.13) Tool to access Sybase server with line editing and history
recall http://www.mcs.net/~ivank/sybtool.html
11.4.14) Sybase connectivity libraries
http://www.sybase.com/products/samples/
11.4.15) A web to Sybase interface http://archive.eso.org/wdb/html/
11.4.16) Nifty Sybase tools as well as sybinit4ever
http://www.euronet.nl/~syp_rob/download.html
_User Groups_
11.5.1) Indiana Sybase User's Group
http://www.cs.bsu.edu/homepages/sam/isug
11.5.2) Ontario Sybase User Group (OSUG) Website -
http://www.interlog.com/~osug
11.5.3) SUGBay, Bay Area Sybase User Group - http://www.sugbay.com
11.5.4) DCASUG, DC Area Sybase User Group -
http://www2.dgsys.com/~dcasug
11.5.5) International Sybase User Group - http://www.sybase.com/ISUG
_Commercial Links_
The following sites are placed here without any endorsement by the FAQ
maintainer.
11.6.1) Ed Barlow's site of sites
http://www.tiac.net/users/sqltech/links.htm#commercial_links
The mother ship may be reached at http://www.sybase.com
_________________________________________________________________
Miscellany
12.1) What can Sybase IQ do for me?
12.2) Net-review of Sybase books
12.3) email lists
12.4) Finding Information at Sybase
_________________________________________________________________
Acknowledgements ...
Thanks to all the folks in comp.databases.sybase who have made this
FAQ possible. Furry instance, this release has two minor contributions
by me (although admining the thing can be a nightmare at times!) and
the rest has come from folks on the net and at Sybase. I've
degenerated into a brain stem. Add a little water and I'm your chia
pet.
Please mail pa...@sgi.com any changes, comments, complaints and/or
disagreements and I will respond in due time. Heck I may even fix
them.
Please abide by the following and include it if redistributing the
Sybase FAQ:
_Do not use this information for profit but do_ share it with
anyone.
So who is this guy?
_________________________________________________________________
Hall of Fame
The following people have made the _Sybase FAQ Hall of Fame_. Partly
because Sybase refuses to acknowledge the good work that these people
have done for _their_ product and sales but most importantly because
they have done it for us!
* Michael Peppler (mpep...@bix.com) - author of SybPerl and
provider of awesome support.
* Scott Gray (gr...@voicenet.com) - author of sqsh and
ultra-responsive developer. The best tool this side of the Ozarks.
_________________________________________________________________
What's New in this Release?
_A non-exhaustive list of contributors for 11.5 Changes through
11.5.6_
* Q2.11 - to...@cs.umbc.edu - Added ls -la my_sybase_disk_devices
* Q11.22 - siwa...@cix.compulink.co.uk - The SQL Workshop link
* Q9.18/.19 - clayto...@pobox.com - Reverse engineer user roles
and sp_configure data
* Q1.2 - jo...@lehman.com - Correction on using lct_admin command on
System 11
* Q9.5 - mpep...@mbay.net - Updated version of dbschema.pl by David
Whitmarsh: constraints/defaults/etc + primary/foreign keys
* Q6.9 - Michael_C...@dev.japan.ml.com - Corrections to bad
grammar and increased coverage from System 10 to anything below
and including System 10.
* Q6.9 - anonymous - Culled from the sybase-tuning list see Q12.3
* Q9.5 - ji...@sgi.com/mpep...@mbay.net - Updated link to SybPerl
FAQ
* Q9.20 - unknown author, provided by Mark.Meredith - sp_servermap,
gives an overview of your SQL Server
* Q10.4.[2-9] - Sybase Inc. - Sybase Technical News - I'm not doing
much formatting on these because I want to stay up to date with
the FAQ. Feedback from you, gentle reader, has told me that you
want the data so that you can take the entire FAQ to a client site
on your lap top.
* Q1.4 - doh...@itginc.com - Added traceflags 303 and 319
* Q9.16 - m...@beasys.com - Update e-mail address
* Q7.1 - clayto...@pobox.com - Submits his submission on
crosstabs. Implemented way before the supposed _copyrighted_
version.
* Q4.1 - br...@dhatt.com - Added warning to _script #7_
* Q1.13 - rle...@acm.org - Updates _his_ answer.
* Q6.7 - g...@zappa.informatics.jax.org - Fixed the title of the
answer
* Q2.13 - br...@sybase.com - His answer on how to shrink a database
* Q1.15 - pskri...@compuserve.com - An alternative method to
determining when your Server started - fixed!
* Q2.3 - west...@mfg.sgi.com - sp_configure "allow" wasn't specific
enough for System 10, fixed the FAQ
* Q11.1/Q3.5 - ji...@corp.sgi.com/in...@kaleidatech.com - Q11.1 was
Web pointer to PNL site... dead! I've replaced it with the DBCC
site for Q3.5
* Q2.4 - doh...@itginc.com - Moved _use master_ to the right spot
* Q7.8 - me - Updated to include 11.5 information
* Q7.9 - bo...@netcom.com - A beautiful writeup on using Identities
or your own Sequential Keys
* Q2.1 - mcvi...@sybase.com - Noted that these instructions can be
used for increasing not just shrinking
* index - me - removed that darn counter (it kept resetting), Ed
Barlow was catching up anyway... or so he thinks! :-)
* Q11.15 - br...@sybase.com/a...@ornl.gov - Updated DBCC web link
* Q2.4 - nee...@tc.fluke.com - Delete entries in sysdepends
* Q3.2 - ceas...@virtualogic.com - rebuild_log entry
* Q9.13 - Sam_V...@email.fpl.com/paul...@jpmorgan.come - spruced
up sp_getdays
* Q2.8 - rei...@xs4all.nl - Advised that 11.5 doesn't allow one to
delete the data in syscomments. Instead use sp's to encrypt.
* I cleaned up the Sybase links that I used to keep. Namely, I
removed them! :-) For the books, I simply have a link to the books
URL and folks should be able to navigate within The Mother Ship.
This makes it less probable of storing stale URL's Thanks!
* Q8.13 - me - nuked! It was a link to the Performance and Tuning
books. It was stale... see previous note on my reasoning.
* Q11.6/Q9.17 - pja...@brodeur.com - DBI/DBD:Sybase on linux how to
* Q1.7 - miles.purdy@@pangea.ca - An alternative to moving tempdb
off of master, fill it! Neat trick, see writeup.
* Q12.4 - eb...@sybase.com - Self-help... from Sybase. :-)
* Q9.21 - clayto...@pobox.com - sp__create_crosstab
* Q9.6 - tpoi...@nyx.net - the ever-popular Sybtcl
_Changes in 11.5.7 - 2/9/98_
* Q1.7 - ms...@blackrock.com - we needed to fill with 1024 rows
* Q10.5.2 - sybase - added Sybase Technical News
_Changes in 11.5.8 - 2/19/98_
* Q7.7 - ngo...@incyte.com - provided a fix to the hierarchy
traversal
* Q4.1 - paul...@jpmorgan.com - yet another way to hide your
password
* Q3.2 - gil...@dssolutions.com - contributed _dbcc settrunc()_
* Q9.10 - dmon...@csd.sgi.com - alerted me that Q9.10's link is
dead. Thanks!
_Changes in 11.5.9 - 4/13/98_
* Q10.5.2/Q10.5.3 - Sybase Technical News
* Q6.4 - mdch...@Japan.ml.COM - asked that I provide an example. So
I did. It's more of an example how it works rather than a psuedo
real world example.
* Q6.9 - david.w...@dial.pipex.com - suggested I mention
traceflag 299 to Q6.9. So I did. Good suggestion.
* index - a...@agd.nsw.gov.au - corrected the correction. :-)
* Q7.9 - dav...@lexis-nexis.com - gave an update on where to find
the Malcolm Colton white paper
_Changes in 11.5.10 - 6.8.98_
* Q3.2 - joop.b...@bigfoot.com - dbrepair (dbid, ltmignore)
* Section 11 - mg...@fatsinc.com - various web links
* Q9.22 - flu...@hway.net - intelligent update statistics
* Q10.5.5/Q10.5.6 - Sybase Technical News
_Changes in 11.5.11 - 7.13.98_
* Q10.5.7 - Sybase Technical News
* Section 11 - MG...@fatsinc.com - reorg of this section
* Q3.2 - srama...@lucent.com - dbcc corrupt
_Changes in 11.5.12 - 7.31.98_
* Q11.2.4 - rei...@xs4all.nl - SQL Server and Rep Server on NT
_Changes in 11.5.13 - 8.14.98_
* Q10.5.8 - Sybase Technical News
_Changes in 11.5.14 - 9.2.98_
* Q9.23 - coll...@rabo-bank.com - NTQuery.exe freeware
* Relabeled Shareware to be Freeware - we aren't charging
* Q1.17 - rle...@dogbert.demon.co.uk - Y2K stuff
_Changes in 11.5.15 - 9.23.98_
* Q10.5.9 - Sybase Technical News
* Q1.4 - Andrew....@aib.ie - contributed traceflag 304
_Changes in 1 and 2 - 11.6.98_
* Renamed FAQ to be hip 'n cool: ...FAQ 2000 and fixed typos
* Q9.24 - wten...@sybase.com - Sybase on Linux, what else is there
to say?
* Q10.5.10 - Sybase Technical News
* Q10.5.11 - Sybase Technical News
* Q11.4.16/Q11.2.5 - r...@sypron.nl - sybinit4ever and other goodies
website
* Q11.6.1 - sql...@tiac.net - Added Ed Barlow's list of commercial
sites. I've created a new subsection under weblinks named
_Commercial Links_ Of course as the FAQ maintainer, I do not
endorse any of the listed sites.
* Q9.25 - ms...@blackrock.com - sp_spaceused_table and ksh script
used to determine space usage for temp tables.
* Q11.4.11 - mere...@sybase.com - update to ISUG's link
_Changes in 3 - 12.8.98_
* Q10.5.12 - Sybase Technical News
* Q11.3.4 - mamet_...@jpmorgan.com - ASE 11.9 Optimizer
Statistics from the ISUG Technical Journal
_________________________________________________________________
--
Pablo Sanchez | Ph # (650) 933.3812 Fax # (303) 939.8523
pa...@sgi.com | Pg # (800) 930.5635 -or- pab...@pager.sgi.com
-------------------------------------------------------------------------------
I am accountable for my actions. http://reality.sgi.com/pablo
Pablo Sanchez
URL: http://reality.sgi.com/pablo/Sybase_FAQ
Q1.1: HOW TO START/STOP SQL SERVER WHEN CPU REBOOTS
_________________________________________________________________
Below is an example of the various files (on _Irix_) that are needed
to start/stop a SQL Server. The information can easily be extended to
any UNIX platform.
The idea is to allow as much flexibility to the two classes of
administrators that admin the machine:
* The System Administrator
* The Database Administrator
Any errors introduced by the DBA will not interfere with the System
Administrator's job.
With that in mind we have the system startup/shutdown file
_/etc/init.d/sybase_ invoking a script defined by the DBA:
_/usr/sybase/sys.config/{start,stop}.sybase_
_/etc/init.d/sybase_
On some operating systems this file must be linked to a corresponding
entry in _/etc/rc.0_ and _/etc/rc.2_ -- see _rc0(1M)_ and _rc2(1M)_
#!/bin/sh
# last modified: 10/17/95, sr.
#
# Make symbolic links so this file will be called during system stop/start.
# ln -s /etc/init.d/sybase /etc/rc0.d/K19sybase
# ln -s /etc/init.d/sybase /etc/rc2.d/S99sybase
# chkconfig -f sybase on
# Sybase System-wide configuration files
CONFIG=/usr/sybase/sys.config
if $IS_ON verbose ; then # For a verbose startup and shutdown
ECHO=echo
VERBOSE=-v
else # For a quiet startup and shutdown
ECHO=:
VERBOSE=
fi
case "$1" in
'start')
if $IS_ON sybase; then
if [ -x $CONFIG/start.sybase ]; then
$ECHO "starting Sybase servers"
/bin/su - sybase -c "$CONFIG/start.sybase $VERBOSE &"
else
<error condition>
fi
fi
;;
'stop')
if $IS_ON sybase; then
if [ -x $CONFIG/stop.sybase ]; then
$ECHO "stopping Sybase servers"
/bin/su - sybase -c "$CONFIG/stop.sybase $VERBOSE &"
else
<error condition>
fi
fi
;;
*)
echo "usage: $0 {start|stop}"
;;
esac
_/usr/sybase/sys.config/{start,stop}.sybase_
start.sybase
#!/bin/sh -a
#
# Script to start sybase
#
# NOTE: different versions of sybase exist under /usr/sybase/{version}
#
# Determine if we need to spew our output
if [ "$1" != "spew" ] ; then
OUTPUT=">/dev/null 2>&1"
else
OUTPUT=""
fi
# 10.0.2 servers
HOME=/usr/sybase/10.0.2
cd $HOME
# Start the backup server
eval install/startserver -f install/RUN_BU_KEPLER_1002_52_01 $OUTPUT
# Start the dataservers
# Wait two seconds between starts to minimize trauma to CPU server
eval install/startserver -f install/RUN_FAC_WWOPR $OUTPUT
sleep 2
eval install/startserver -f install/RUN_MAG_LOAD $OUTPUT
exit 0
stop.sybase
#!/bin/sh
#
# Script to stop sybase
#
# Determine if we need to spew our output
if [ -z "$1" ] ; then
OUTPUT=">/dev/null 2>&1"
else
OUTPUT="-v"
fi
eval killall -15 $OUTPUT dataserver backupserver sybmultbuf
sleep 2
# if they didn't die, kill 'em now...
eval killall -9 $OUTPUT dataserver backupserver sybmultbuf
exit 0
If your platform doesn't support _killall_, it can easily be simulated
as follows:
#!/bin/sh
#
# Simple killall simulation...
# $1 = signal
# $2 = process_name
#
#
# no error checking but assume first parameter is signal...
# what ya want for free? :-)
#
kill -$1 `ps -ef | fgrep $2 | fgrep -v fgrep | awk '{ print $1 }'`
_________________________________________________________________
Q1.2: HOW TO CLEAR A _LOG SUSPEND_
_________________________________________________________________
A connection that is in a _log suspend_ state is there because the
transaction that it was performing couldn't be logged. The reason it
couldn't be logged is because the database transaction log is full.
Typically, the connection that caused the log to fill is the one
suspended. We'll get to that later.
In order to clear the problem you must dump the transaction log. This
can be done as follows:
dump tran _db_name_ to _data_device_
go
At this point, any completed transactions will be flushed out to disk.
If you don't care about the recoverability of the database, you can
issue the following command:
dump tran _db_name_ with truncate_only
If that doesn't work, you can use the _with no_log_ option instead of
the _with truncate_only_.
After successfully clearing the log the suspended connection(s) will
resume.
Unfortunately, as mentioned above, there is the situation where the
connection that is suspended is the culprit that filled the log.
Remember that dumping the log _only_ clears out completed transaction.
If the connection filled the log with one large transaction, then
dumping the log isn't going to clear the suspension.
System 10
What you need to do is issue a SQL Server _kill_ command on the
connection and then unsuspend it:
select lct_admin("unsuspend", db_id("_db_name_"))
System 11
See Sybase Technical News Volume 6, Number 2
Retaining Pre-System 10 Behavior
By setting a database's _abort xact on log full_ option, pre-System 10
behavior can be retained. That is, if a connection cannot log its
transaction to the log file, it is aborted by the SQL Server rather
than suspended.
_________________________________________________________________
Q1.3: WHAT'S THE BEST VALUE FOR _CSCHEDSPINS_?
_________________________________________________________________
It is crucial to understand that _cschedspins_ is a tunable parameter
(recommended values being between 1-2000) and the optimum value is
completely dependent on the customer's environment. _cschedspins_ is
used by the scheduler only when it finds that there are no runnable
tasks. If there are no runnable tasks, the scheduler has two options:
1. Let the engine go to sleep (which is done by an OS call) for a
specified interval or until an event happens. This option assumes
that tasks won't become runnable because of tasks executing on
other engines. This would happen when the tasks are waiting for
I/O more than any other resource such as locks. Which means that
we could free up the CPU resource (by going to sleep) and let the
system use it to expedite completion of system tasks including
I/O.
2. Go and look for a ready task again. This option assumes that a
task would become runnable in the near term and so incurring the
extra cost of an OS context switch through the OS sleep/wakeup
mechanism is unacceptable. This scenario assumes that tasks are
waiting on resources such as locks, which could free up because of
tasks executing on other engines, more than they wait for I/O.
_cschedspins_ controls how many times we would choose option 2 before
choosing option 1. Setting _cschedspins_ low favors option 1 and
setting it high favors option 2. Since an I/O intensive task mix fits
in with option 1, setting _cschedspins_ low may be more beneficial.
Similarly since a CPU intensive job mix favors option 2, setting
_cschedspins_ high may be beneficial.
The consensus is that a single cpu server should have _cschedspins_
set to 1. However, I strongly recommend that users carefully test
values for _cschedspins_ and monitor the results closely. I have seen
more than one site that has shot themselves in the foot so to speak
due to changing this parameter in production without a good
understanding of their environment.
_________________________________________________________________
Q1.4: Trace Flag Definitions
------------------------------------------------------------------------
To activate trace flags, add them to the RUN_* script. The following example
is using the 1611 and 260 trace flags.
Use of these traceflags is not recommended by Sybase. Please use
at your own risk.
% cd ~sybase/install
% cat RUN_BLAND
#!/bin/sh
#
# SQL Server Information:
# name: BLAND
# master device: /usr/sybase/dbf/BLAND/master.dat
# master device size: 25600
# errorlog: /usr/sybase/install/errorlog_BLAND
# interfaces: /usr/sybase
#
/usr/sybase/dataserver -d/usr/sybase/dbf/BLAND/master.dat \
-sBLAND -e/usr/sybase/install/errorlog_BLAND -i/usr/sybase \
-T1611 -T260
------------------------------------------------------------------------
Trace Flags
Flag Description
200 Displays messages about the before image of the query-tree.
201 Displays messages about the after image of the query-tree.
241 Compress all query-trees whenever the SQL dataserver is started.
Reduce TDS (Tabular Data Stream) overhead in stored procedures.
Turn off done-in-proc packets. Do not use this if your application
is a ct-lib based application; it'll break.
260
Why set this on? Glad you asked, typically with a db-lib
application a packet is sent back to the client for each batch
executed within a stored procedure. This can be taxing in a WAN/LAN
environment.
This trace flag instructs the dataserver to not recompile a child
299 stored procedure that inherits a temp table from a parent
procedure.
302 Print information about the optimizer's index selection.
303 Display OR strategy
Revert special or optimizer strategy to that strategy used in
304 pre-System 11 (this traceflag resolved several bug issues in System
11, most of these bugs are fixed in SQL Server 11.0.3.2)
310 Print information about the optimizer's join selection.
311 Display the expected IO to satisfy a query. Like statistics IO
without actually executing.
317 Provide extra optimization information.
319 Reformatting strategies.
320 Turn off the join order heuristic.
324 Turn off the like optimization for ad-hoc queries using
@local_variables.
602 Prints out diagnostic information for deadlock prevention.
603 Prints out diagnostic information when avoiding deadlock.
699 Turn off transaction logging for the entire SQL dataserver.
1204* Send deadlock detection to the errorlog.
1205 Stack trace on deadlock.
1206 Disable lock promotion.
1603* Use standard disk I/O (i.e. turn off asynchronous I/O).
1605 Start secondary engines by hand
Create a debug engine start file. This allows you to start up a
debug engine which can access the server's shared memory for
running diagnostics. I'm not sure how useful this is in a
1606 production environment as the debugger often brings down the
server. I'm not sure if Sybase have ported the debug stuff to
10/11. Like most of their debug tools it started off quite strongly
but was never developed.
Startup only engine 0; use dbcc engine(online) to incrementally
1608 bring up additional engines until the maximum number of configured
engines.
1610* Boot the SQL dataserver with TCP_NODELAY enabled.
1611* If possible, pin shared memory -- check errorlog for
success/failure.
1613 Set affinity of the SQL dataserver engine's onto particular CPUs --
usually pins engine 0 to processor 0, engine 1 to processor 1...
1615 SGI only: turn on recoverability to filesystem devices.
2512 Prevent dbcc from checking syslogs. Useful when you are constantly
getting spurious allocation errors.
Display each log record that is being processed during recovery.
3300 You may wish to redirect stdout because it can be a lot of
information.
3500 Disable checkpointing.
3502 Track checkpointing of databases in errorlog.
3601 Stack trace when error raised.
3604 Send dbcc output to screen.
3605 Send dbcc output to errorlog.
3607 Do not recover any database, clear tempdb, or start up checkpoint
process.
3608 Recover master only. Do not clear tempdb or start up checkpoint
process.
3609 Recover all databases. Do not clear tempdb or start up checkpoint
process.
3610 Pre-System 10 behavior: divide by zero to result in NULL instead of
error - also see Q7.5.
3620 Do not kill infected processes.
4012 Don't spawn chkptproc.
4013 Place a record in the errorlog for each login to the dataserver.
4020 Boot without recover.
Forces all I/O requests to go thru engine 0. This removes the
5101 contention between processors but could create a bottleneck if
engine 0 becomes busy with non-I/O tasks. For more
information...5101/5102.
5102 Prevents engine 0 from running any non-affinitied tasks. For more
information...5101/5102.
7103 Disable table lock promotion for text columns.
8203 Display statement and transaction locks on a deadlock error.
* Starting with System 11 these are sp_configure'able
------------------------------------------------------------------------
Q1.5: TRACE FLAGS -- 5101 AND 5102
_________________________________________________________________
5101
Normally, each engine issues and checks for its own Disk I/O on behalf
of the tasks it runs. In completely symmetric operating systems, this
behavior provides maximum I/O throughput for SQL Server. Some
operating systems are not completely symmetic in their Disk I/O
routines. For these environments, the server can be booted with the
5101 trace flag. While tasks still request disk I/O from any engine,
the actual request to/from the OS is performed by engine 0. The
performance benefit comes from the reduced or eliminated contention on
the locking mechanism inside the OS kernel. To enable I/O affinity to
engine 0, start SQL Server with the 5101 Trace Flag.
Your errorlog will indicate the use of this option with the message:
Disk I/O affinitied to engine: 0
This trace flag only provides performance gains for servers with 3 or
more dataserver engines configured and being significantly utilized.
_Use of this trace flag with fully symmetric operating systems will
degrade performance!_
5102
The 5102 trace flag prevents engine 0 from running any non-affinitied
tasks. Normally, this forces engine 0 to perform Network I/O only.
Applications with heavy result set requirements (either large results
or many connections issuing short, fast requests) may benefit. This
effectively eliminates the normal latency for engine 0 to complete
running its user thread before it issues the network I/O to the
underlying network transport driver. If used in conjuction with the
5101 trace flag, engine 0 would perform all Disk I/O and Network I/O.
For environments with heavy disk and network I/O, engine 0 could
easily saturate when only the 5101 flag is in use. This flag allows
engine 0 to concentrate on I/O by not allowing it to run user tasks.
To force task affinity off engine 0, start SQL Server with the 5102
Trace Flag.
Your errorlog will indicate the use of this option with the message:
I/O only enabled for engine: 0
_________________________________________________________________
_Warning: Not supported by Sybase. Provided here for your enjoyment._
Q1.6: WHAT IS _CMAXPKTSZ_ GOOD FOR?
_________________________________________________________________
_cmaxpktsz_ corresponds to the parameter "maximum network packet size"
which you can see through _sp_configure_. I recommend only updating
this value through _sp_configure_. If some of your applications send
or receive large amounts of data across the network, these
applications can achieve significant performance improvement by using
larger packet sizes. Two examples are large bulk copy operations and
applications reading or writing large text or image values. Generally,
you want to keep the value of default network packet size small for
users performing short queries, and allow users who send or receive
large volumes of data to request larger packet sizes by setting the
maximum network packet size configuration variable.
_caddnetmem_ corresponds to the parameter "additional netmem" which
you can see through _sp_configure_. Again, I recommend only updating
this value through _sp_configure_. "additional netmem" sets the
maximum size of additional memory that can be used for network packets
that are larger than SQL Server's default packet size. The default
value for additional netmem is 0, which means that no extra space has
been allocated for large packets. See the discussion below, under
maximum network packet size, for information on setting this
configuration variable. Memory allocated with additional netmem is
added to the memory allocated by memory. It does not affect other SQL
Server memory uses.
SQL Server guarantees that every user connection will be able to log
in at the default packet size. If you increase maximum network packet
size and additional netmem remains set to 0, clients cannot use packet
sizes that are larger than the default size: all allocated network
memory will be reserved for users at the default size. In this
situation, users who request a large packet size when they log in
receive a warning message telling them that their application will use
the default size. To determine the value for additional netmem if your
applications use larger packet sizes:
* Estimate the number of simultaneous users who will request the
large packet sizes, and the sizes their applications will request.
* Multiply this sum by three, since each connection needs three
buffers.
* Add 2% for overhead, rounded up to the next multiple of 512
_________________________________________________________________
Q1.7: HOW DO I MOVE _TEMPDB_ OFF OF THE MASTER DEVICE?
_________________________________________________________________
_Note:_ I received a message from Sybase TS recommending that the
FAQ no longer advocate the physical removal of entries from the
_sysusages/sysdatabases_ tables. It makes recovery _extremely_
painful.
After reviewing their write-up I agree.
A quick alternative - Sybase TS Preferred Method
This is the Sybase TS method of removing _most_ activity off of the
master device:
1. Alter tempdb on another device:
1> alter database tempdb on ...
2> go
2. Use the tempdb:
1> use tempdb
2> go
3. Drop the segments:
1> sp_dropsegment "default", tempdb, master
2> go
1> sp_dropsegment "logsegment", tempdb, master
2> go
1> sp_dropsegment "system", tempdb, master
2> go
Note that there is still _some_ activity on the master device. On a
three connection test that I ran:
while ( 1 = 1 )
begin
create table #x (col_a int)
drop table #x
end
there was one write per second. Not bad.
Yet another alternative
The idea of this handy script is to simply fill the first 2MB of
tempdb thus effectively blocking anyone else from using it. The
_slight_ gotcha with this script, since we're using model, is that all
subsequent database creates will also have _tempdb_filler_ installed.
This is easily remedied by dropping the table after creating a new
database.
This script works because tempdb is rebuilt every time the SQL Server
is rebooted. Very nice trick!
/* this isql script creates a table in the model database. */
/* Since tempdb is created from the model database when the */
/* server is started, this effectively moves the active */
/* portion of tempdb off of the master device. */
use model
go
/* note: 2k row size */
create table tempdb_filler(
a char(255) not null,
b char(255) not null,
c char(255) not null,
d char(255) not null,
e char(255) not null
)
go
/* insert 1024 rows */
declare @i int
select @i = 1
while (@i
__________________________________________________________________________
Q1.8: BUILDMASTER CONFIGURATION DEFINITIONS
_________________________________________________________________
_Attention!_ Please notice, be very careful with these parameters.
Use only at your own risk. Be sure to have a copy of the original
parameters. Be sure to have a dump of all dbs (include master)
handy.
_________________________________________________________________
The following is a list of configuration parameters and their effect
on the SQL Server. Changes to these parameters can affect performance
of the server. Sybase does not recommend modifying these parameters
without first discussing the change with Sybase Tech Support. This
list is provided for information only.
These are categorized into two kinds:
* Configurable through sp_configure and
* not configurable but can be changed through 'buildmaster
-y<variable>=value -d<dbdevice>'
Configurable variables:
crecinterval:
The recovery interval specified in minutes.
ccatalogupdates:
A flag to inform whether system catalogs can be updated or not.
cusrconnections:
This is the number of user connections allowed in SQL
Server. This value + 3 (one for checkpoint, network
and mirror handlers) make the number of pss configured
in the server.
_________________________________________________________________
cfgpss:
Number of PSS configured in the server. This value will
always be 3 more than cusrconnections. The reason is we
need PSS for checkpoint, network and mirror handlers.
THIS IS NOT CONFIGURABLE.
_________________________________________________________________
cmemsize:
The total memory configured for the Server in 2k
units. This is the memory the server will use for both
Server and Kernel Structures. For Stratus or any 4k
pagesize implementation of SQL Server, certain values
will change as appropriate.
cdbnum:
This is the number of databases that can be open in SQL
Server at any given time.
clocknum:
Variable that defines and controls the number of logical
locks configured in the system.
cdesnum:
This is the number of open objects that can be open at
a given point of time.
cpcacheprcnt:
This is the percentage of cache that should be used
for procedures to be cached in.
cfillfactor:
Fill factor for indexes.
ctimeslice:
This value is in units of milli-seconds. This value determines
how much time a task is allowed to run before it yields.
This value is internally converted to ticks. See below
the explanations for cclkrate, ctimemax etc.
ccrdatabasesize:
The default size of the database when it is created.
This value is Megabytes and the default is 2Meg.
ctappreten:
An outdated not used variable.
crecoveryflags:
A toggle flag which will display certain recovery information
during database recoveries.
cserialno:
An informational variable that stores the serial number
of the product.
cnestedtriggers:
Flag that controls whether nested triggers allowed or not.
cnvdisks:
Variable that controls the number of device structures
that are allocated which affects the number of devices
that can be opened during server boot up. If user
defined 20 devices and this value is configured to be
10, during recovery only 10 devices will be opened and
the rest will get errors.
cfgsitebuf:
This variable controls maximum number of site handler
structures that will be allocated. This in turn
controls the number of site handlers that can be
active at a given instance.
cfgrembufs:
This variable controls the number of remote buffers
that needs to send and receive from remote sites.
Actually this value should be set to number of
logical connections configured. (See below)
cfglogconn:
This is the number of logical connections that can
be open at any instance. This value controls
the number of resource structure allocated and
hence it will affect the overall logical connection
combined with different sites. THIS IS NOT PER SITE.
cfgdatabuf:
Maximum number of pre-read packets per logical connections.
If logical connection is set to 10, and cfgdatabuf is set
to 3 then the number of resources allocated will be
30.
cfupgradeversion:
Version number of last upgrade program ran on this server.
csortord:
Sort order of the SQL Server.
cold_sortdord:
When sort orders are changed the old sort order is
saved in this variable to be used during recovery
of the database after the Server is rebooted with
the sort order change.
ccharset:
Character Set used by the SQL server
cold_charset:
Same as cold_sortord except it stores the previous
Character Set.
_________________________________________________________________
cdflt_sortord:
page # of sort order image definition. This should
not be changed at any point. This is a server only
variable.
cdflt_charset:
page # of character set image definition. This should
not be changed at any point. This is a server only
variable.
cold_dflt_sortord:
page # of previous sort order image definition. This
should not be changed at any point. This is a server
only variable.
cold_dflt_charset:
page # of previous chracter set image definition. This
should not be changed at any point. This is a server
only variable.
_________________________________________________________________
cdeflang:
Default language used by SQL Server.
cmaxonline:
Maximum number of engines that can be made online. This
number should not be more than the # of cpus available on this
system. On Single CPU system like RS6000 this value is always
1.
cminonline:
Minimum number of engines that should be online. This is 1 by
default.
cengadjinterval:
A noop variable at this time.
cfgstacksz:
Stack size per task configured. This doesn't include the guard
area of the stack space. The guard area can be altered through
cguardsz.
_________________________________________________________________
cguardsz:
This is the size of the guard area. The Sql Server will
allocate stack space for each task by adding cfgstacksz
(configurable through sp_configure) and cguardsz (default is
2K). This has to be a multiple of PAGESIZE which will be 2k
or 4k depending on the implementation.
cstacksz:
Size of fixed stack space allocated per task including the
guard area.
_________________________________________________________________
Non-configurable values :
_________________________________________________________________
_TIMESLICE, CTIMEMAX ETC:_
_________________________________________________________________
1 millisecond = 1/1000th of a second.
1 microsecond = 1/1000000th of a second. "Tick" : Interval between two
clock interrupts occur in real time.
"cclkrate" :
A value specified in microsecond units.
Normally on systems where a fine grained timer is not available
or if the Operating System cannot set sub-second alarms, this
value is set to 1000000 milliseconds which is 1 second. In
other words an alarm will go off every 1 second or you will
get 1 tick per second.
On Sun4 this is set to 100000 milliseconds which will result in
an interrupt going at 1/10th of a second. You will get 6 ticks
per second.
"avetimeslice" :
A value specified in millisecond units.
This is the value given in "sp_configure",<timeslice value>.
Otherwise the milliseconds are converted to milliseconds and
finally to tick values.
ticks = <avetimeslice> * 1000 / cclkrate.
"timeslice" :
_________________________________________________________________
The unit of this variable is in ticks.
This value is derived from "avetimeslice". If "avetimeslice"
is less than 1000 milliseconds then timeslice is set to 1 tick.
"ctimemax" :
The unit of this variable is in ticks.
A task is considered in infinite loop if the consumed ticks
for a particular task is greater than ctimemax value. This
is when you get timeslice -201 or -1501 errors.
"cschedspins" :
For more information see Q1.3.
This value alters the behavior of the SQL Server scheduler.
The scheduler will either run a qualified task or look
for I/O completion or sleep for a while before it can
do anything useful.
The cschedspins value determines how often the scheduler
will sleep and not how long it will sleep. A low value
will be suited for a I/O bound SQL Server but a
high value will be suited for CPU bound SQL Server. Since
the SQL Server will be used in a mixed mode, this value
need to be fined tuned.
Based on practical behavior in the field, a single engine
SQL Server should have cschedspins set to 1 and a multi-engine
server should have set to 2000.
Now that we've defined the units of these variables what happens when
we change cclkrate ?
Assume we have a cclkrate=100000.
A clock interrupt will occur every (100000/1000000) 1/10th
milliseconds. Assuming a task started with 1 tick which can go upto
"ctimemax=1500" ticks can potentially take 1/10us * (1500 + 1) ticks
which will be 150 milliseconds or approx. .15 milliseconds per task.
Now changing the cclkrate to 75000
A clock interrupt will occur every (75000/1000000) 1/7th milliseconds.
Assuming a task started with 1 tick which can go upto ctimemax=1500
ticks can potentially take 1/7us * (1500 + 1) ticks which will be 112
milliseconds or approx. .11 milliseconds per task.
Decreasing the cclkrate value will decrease the time spent on each
task. If the task couldnot voluntarily yield within the time, the
scheduler will kill the task.
UNDER NO CIRCUMSTANCES the cclkrate value should be changed. The
default ctimemax value should be set to 1500. This is an empirical
value and this can be changed under special circumstances and strictly
under the guidance of DSE.
_________________________________________________________________
cfgdbname:
Name of the master device is saved here. This is 64
bytes in length.
cfgpss:
This is a derived value from cusrconnections + 3.
See cusrconnections above.
cfgxdes:
This value defines the number of transactions that
can be done by a task at a given instance.
Changing this value to be more than 32 will have no
effect on the server.
cfgsdes:
This value defines the number of open tables per
task. This will be typically for a query. This
will be the number of tables specified in a query
including subqueries.
Sybase Advises not to change this value. There
will be significant change in the size of per user
resource in SQL Server.
cfgbuf:
This is a derived variable based on the total
memory configured and subtracting different resource
sizes for Databases, Objects, Locks and other
Kernel memories.
cfgdes:
This is same as cdesnum. Other values will have no effect on it.
cfgprocedure:
This is a derived value. Based on cpcacheprcnt variable.
cfglocks:
This is same as clocknum. Other values will have no effect on it.
cfgcprot:
This is variable that defines the number of cache protectors per
task. This is used internally by the SQL Server.
Sybase advise not to modify this value as a default of 15 will
be more than sufficient.
cnproc:
This is a derived value based on cusrconnections + <extra> for
Sybase internal tasks that are both visible and non-visible.
cnmemmap:
This is an internal variable that will keep track of SQL Server
memory.
Modifying this value will not have any effect.
cnmbox:
Number of mail box structures that need to be allocated.
More used in VMS environment than UNIX environment.
cnmsg:
Used in tandem with cnmbox.
cnmsgmax:
Maximum number of messages that can be passed between mailboxes.
cnblkio:
Number of disk I/O request (async and direct) that can be
processed at a given instance. This is a global value for all
the engines and not per engine value.
This value is directly depended on the number of I/O request
that can be processed by the Operating System. It varies
depending on the Operating System.
cnblkmax:
Maximum number of I/O request that can be processed at any given
time.
Normally cnblkio,cnblkmax and cnmaxaio_server should be the same.
cnmaxaio_engine:
Maximum number of I/O request that can be processed by one engine.
Since engines are Operating System Process, if there is any limit
imposed by the Operating System on a per process basis then
this value should be set. Otherwise it is a noop.
cnmaxaio_server:
This is the total number of I/O request the SQL Server can do.
This value s directly depended on the number of I/O request
that can be processed by the Operating System. It varies
depending on the Operating System.
csiocnt:
not used.
cnbytio:
Similar to disk I/O request, this is for network I/O request.
This includes disk/tape dumps also. This value is for
the whole SQL Server including other engines.
cnbytmax:
Maximum number of network I/O request including disk/tape dumps.
cnalarm:
Maximum number of alarms including the alarms used by
the system. This is typically used when users do "waitfor delay"
commands.
cfgmastmirror:
Mirror device name for the master device.
cfgmastmirror_stat:
Status of mirror devices for the master device like serial/dynamic
mirroring etc.
cindextrips:
This value determines the aging of a index buffer before it
is removed from the cache.
coamtrips:
This value determines the aging of a OAM buffer before it
is removed from the cache.
cpreallocext:
This value determines the number of extents that will be
allocated while doing BCP.
cbufwashsize:
This value determines when to flush buffers in the cache
that are modified.
Q1.9: HOW DO I CORRECT _TIMESLICE -201_
_________________________________________________________________
Why Increase It?
Basically, it will allow for a task to be scheduled onto the CPU in a
longer time. Each task on the system is scheduled onto the CPU for a
fixed period of time, called the timeslice, during which it does some
work, which is resumed when its next turn comes around.
The process has up until the value of _ctimemax_ (a config block
variable) to finish its task. As the task is working away, the
scheduler counts down ctimemax units. When it gets to the value of
_ctimemax_ - 1, if it gets _stuck_ and for some reason cannot be taken
off the CPU, then a timeslice error gets generated and the process
gets infected.
On the other hand, SQL Server will allow a Server process to run as
long as it needs to. It will not swap the process out for another
process to run. The process will decide when it is "done" with the
Server CPU. If, however, a process goes on and on and never
relinquishes the Server CPU, then Server will timeslice the process.
Potential Fix
1. Shutdown the SQL Server
2. %buildmaster -d_your_device_ -yctimemax=2000
3. Restart your SQL Server. If the problem persists contact Sybase
Technical Support notifying them what you have done already.
_________________________________________________________________
Q1.10: What is a SQL Server?
----------------------------------------------------------------------------
Overview
Before Sybase System 10 (as they call it) we had Sybase 4.x. Sybase System
10 has some significant improvements over Sybase 4.x product line. Namely:
* the ability to allocate more memory to the dataserver without degrading
its performance.
* the ability to have more than one database engine to take advantage of
multi-processor cpu machines.
* a minimally intrusive process to perform database and transaction
dumps.
Background and More Terminology
A SQL Server is simply a Unix process. It is also known as the database
engine. It has multiple threads to handle asynchronous I/O and other tasks.
The number of threads spawned is the number of engines (more on this in a
second) times five. This is the current implementation of Sybase System 10,
10.0.1 and 10.0.2 on IRIX 5.3.
Each SQL dataserver allocates the following resources from a host machine:
* memory and
* raw partition space.
Each SQL dataserver can have up to 255 databases. In most implementations
the number of databases is limited to what seems reasonable based on the
load on the SQL dataserver. That is, it would be impractical to house all of
a large company's databases under one SQL dataserver because the SQL
dataserver (a Unix process) will become overloaded.
That's where the DBA's experience comes in with interrogation of the user
community to determine how much activity is going to result on a given
database or databases and from that we determine whether to create a new SQL
Server or to house the new database under an existing SQL Server. We do make
mistakes (and businesses grow) and have to move databases from one SQL
Server to another. And at times SQL Servers need to move from one CPU server
to another.
With Sybase System 10, each SQL Server can be configured to have more than
one engine (each engine is again a Unix process). There's one primary engine
that is the master engine and the rest of the engines are subordinates. They
are assigned tasks by the master.
Interprocess communication among all these engines is accomplished with
shared memory.
Some times when a DBA issues a Unix kill command to extinguish a
maverick SQL Server, the subordinate engines are forgotten. This
leaves the shared memory allocated and eventually we may get in to
situations where swapping occurs because this memory is locked. To
find engines that belong to no master SQL Server, simple look for
engines owned by /etc/init (process id 1). These engines can be
killed -- this is just FYI and is a DBA duty.
Before presenting an example of a SQL Server, some other topics should be
covered.
Connections
A SQL Server has connections to it. A connection can be viewed as a user
login but it's not necessarily so. That is, a client (a user) can spark up
multiple instances of their application and each client establishes its own
connection to the SQL dataserver. Some clients may require two or more per
invocation. So typically DBA's are only concerned with the number of
connections because the number of users typically does not provide
sufficient information for us to do our job.
Connections take up SQL Server resources, namely memory, leaving
less memory for the SQL Servers' available cache.
SQL Server Buffer Cache
In Sybase 4.0.1 there was a limit to the amount of memory that could be
allocated to a SQL Server. It was around 80MB, with 40MB being the typical
max. This was due to internal implementations of Sybase's data structures.
With Sybase System 10 there really is no limit. For instance, we have a SQL
Server cranked up to 300MB.
The memory in a SQL Server is primarily used to cache data pages from disk.
Consider that the SQL Server is a light weight Operating System: handling
user (connections), allocating memory to users, keeping track of which data
pages need to be flushed to disk and the sort. Very sophisticated and
complex. Obviously if a data page is found in memory it's much faster to
retrieve than going out to disk.
Each connection takes away a little bit from the available memory that is
used to cache disk pages. Upon startup, the SQL Server pre-allocates the
memory that is needed for each connection so it's not prudent to configure
500 connections when only 300 are needed. We'd waste 200 connections and the
memory associated with that. On the other hand, it is also imprudent to
under configure the number of connections; users have a way of soaking up a
resource (like a SQL Server) and if users have all the connections a DBA
cannot get into the server to allocate more connections.
One of the neat things about a SQL Server is that it reaches (just like a
Unix process) a working set. That is, upon startup it'll do a lot of
physical I/O's to seed its cache, to get lookup information for typical
transactions and the like. So initially, the first users have heavy hits
because their requests have to be performed as a physical I/O. Subsequent
transactions have less physical I/O and more logical I/O's. Logical I/O is
an I/O that is satisfied in the SQL Servers' buffer cache. Obviously, this
is the preferred condition.
DSS vs OLTP
We throw around terms like everyone is supposed to know this high tech
lingo. The problem is that they are two different animals that require a SQL
Server to be tuned accordingly for each.
Well, here's the low down.
DSS
Decision Support System
OLTP
Online Transaction Processing
What do these mean? OLTP applications are those that have very short orders
of work for each connection: fetch this row and with the results of it
update one or two other rows. Basically, small number of rows affected per
transaction in rapid sucession, with no significant wait times between
operations in a transaction.
DSS is the lumbering elephant in the database world (unless you do some
tricks... out of this scope). DSS requires a user to comb through gobs of
data to aggregate some values. So the transactions typically involve
thousands of rows. Big difference than OLTP.
We never want to have DSS and OLTP on the same SQL Server because the nature
of OLTP is to grab things quickly but the nature of DSS is to stick around
for a long time reading tons of information and summarizing the results.
What a DSS application does is flush out the SQL Server's data page cache
because of the tremendous amount of I/O's. This is obviously very bad for
OTLP applications because the small transactions are now hurt by this
trauma. When it was only OLTP a great percentage of I/O was logical
(satisfied in the cache); now transactions must perform physical I/O.
That's why it's important in Sybase not to mix DSS and OLTP, at least until
System 11 arrives.
Sybase System 11 release will allow for the mixing of OLTP and DSS
by allowing the DBA to partition (and name) the SQL Server's
buffer cache and assign it to different databases and/or objects.
The idea is to allow DSS to only affect their pool of memory and
thus allowing OLTP to maintain its working set of memory.
Asynchronous I/O
Why async I/O? The idea is in a typical online transaction processing (OLTP)
application you have many connections (over 200 connections) and short
transactions: get this row, update that row. These transactions are
typically spread across different tables of the databases. The SQL Server
can then perform each one of these asynchronously without having to wait for
others to finish. Hence the importance of having async I/O fixed on our
platform.
Engines
Sybase System 10 can have more than one engine (as stated above). Sybase has
trace flags to pin the engines to a given CPU processor but we typically
don't do this. It appears that the master engine goes to processor 0 and
subsequent subordinates to the next processor.
Currently, Sybase does not scale linearly. That is, five engines doesn't
make Sybase perform five times as fast however we do max out with four
engines. After that, performs starts to degrade. This is supposed to be
fixed with Sybase System 11.
Putting Everything Together
As previously mentioned, a SQL Server is a collection of databases with
connections (that are the users) to apply and retrieve information to and
from these containers of information (databases).
The SQL Server is built and its master device is typically built over a
medium sized (50MB) raw partition. The tempdb is built over a cooked
(regular - as opposed to a raw device) file system to realize any
performance gains by buffered writes. The databases themselves are built
over the raw logical devices to ensure their integrity.
Physical and Logical Devices
Sybase likes to live in its own little world. This shields the DBA from the
outside world known as Unix (or VMS). However, it needs to have a conduit to
the outside world and this is accomplished via devices.
All physical devices are mapped to logical devices. That is, given a
physical device (such as /lv1/dumps/tempdb_01.efs or /dev/rdsk/dks1ds0) it
is mapped by the DBA to a logical device. Depending on the type of the
device, it is allocated, by the DBA, to the appropriate place (vague
enough?).
Okay, let's try and clear this up...
Dump Device
The DBA may decide to create a device for dumping the database nightly. The
DBA needs to create a dump device.
We'll call that logically in the database datadump_for_my_db but we'll map
it to the physical world as /lv1/dumps/in_your_eye.dat So the DBA will write
a script that connects to the SQL Server and issues a command like this:
dump database my_stinking_db to datadump_for_my_db
go
and the backupserver (out of this scope) takes the contents of
my_stinking_db and writes it out to the disk file /lv1/dumps/in_your_eye.dat
That's a dump device. The thing is that it's not preallocated. This special
device is simply a window to the operating system.
Data and Log Devices
Ah, now we are getting into the world of pre-allocation. Databases are built
over raw partitions. The reason for this is because Sybase needs to be
guaranteed that all its writes complete successfully. Otherwise, if it
posted to a file system buffer (as in a cooked file system) and the machine
crashed, as far as Sybase is concerned the write was committed. It was not,
however, and integrity of the database was lost. That is why Sybase needs
raw partitions. But back to the matter at hand...
When building a new SQL Server, the DBA determines how much space they'll
need for all the databases that will be housed in this SQL Server.
Each production database is composed of data and log.
The data is where the actual information resides. The log are where the
changes are kept. That is, every row that is updated/deleted/inserted gets
placed into the log portion then applied to the data portion of the
database.
That's why DBA strives to place the raw devices for logs on
separate disks because everything has to single thread through the
log.
A transaction is a collection of SQL statements (insert/delete/update) that
are grouped together to form a single unit of work. Typically they map very
closely to the business.
I'll quote the Sybase SQL Server System Administration guide on the role of
the log:
The transaction log is a write-ahead log. When a user issues a
statement that would modify the database, SQL Server automatically
writes the changes to the log. After all changes for a statement
have been recorded in the log, they are written to an in-cache
copy of the data page. The data page remains in cache until the
memory is needed for another database page. At that time, it is
written to disk. If any statement in a transaction fails to
complete, SQL Server reverses all changes made by the transaction.
SQL Server writes an "end transaction" record to the log at the
end of each transaction, recording the status (success or failure)
of the transaction
As such, the log will grow as user connections affect changes to the
database. The need arises to then clear out the log of all transactions that
have been flushed to disk. This is performed by issuing the following
command:
dump transaction my_stinking_db to logdump_for_my_db
go
The SQL Server will write to the dumpdevice all transactions that have been
committed to disk and will delete the entries from its copy, thus freeing up
space in the log. Dumping of the transaction logs is accomplished via cron.
We schedule the heavily hit databases every 20 minutes during peak times.
A single user can fill up the log by having begin transaction with
no corresponding commit/rollback transaction. This is because all
their changes are being applied to the log as an open-ended
transaction, which is never closed. This open-ended transaction
cannot be flushed from the log, and therefore grows until it
occupies all of the free space on the log device.
And the way we dump it is with a dump device. :-)
An Example
If the DBA has four databases to plop on this SQL Server and they need a
total of 800MB of data and 100MB of log (because that's what really matters
to us), then they'd probably do something like this:
1. allocate sufficient raw devices to cover the data portion of all the
databases
2. allocate sufficient raw devices to cover the log portion of all the
databases
3. start allocating the databases to the devices.
For example, assuming the following database requirements:
Database
Requirements
DB Data Log
a 300 30
b 400 40
c 100 10
and the following devices:
Devices
Logical Physical Size
dks3d1s2_data /dev/rdsk/dks3d1s2 500
dks4d1s2_data /dev/rdsk/dks4d1s2 500
dks5d1s0_log /dev/rdsk/dks5d1s0 200
then the DBA may elect to create the databases as follows:
create database a on dks3d1s2_data = 300 log on dks5d1s0_log = 30
create database b on dks4d1s2_data = 400 log on dks5d1s0_log = 40
create database c on dks3d1s2_data = 50, dks4d1s2_data = 50 log on
dks5d1s0_log = 10
Some of the devices will have extra space available because out database
allocations didn't use up all the space. That's fine because it can be used
for future growth. While the Sybase SQL Server is running, no other Sybase
SQL Server can re-allocate these physical devices.
TempDB
TempDB is simply a scratch pad database. It gets recreated when a SQL Server
is rebooted. The information held in this database is temporary data. A
query may build a temporary table to assist it; the Sybase optimizer may
decide to create a temporary table to assist itself.
Since this is an area of constant activity we create this database over a
cooked file system which has historically proven to have better performance
than raw - due to the buffered writes provided by the Operating System.
Port Numbers
When creating a new SQL Server, we allocate a port to it (currently, DBA
reserves ports 1500 through 1899 for its use). We then map a host name to
the different ports: hera, fddi-hera and so forth. We can actually have more
than one port number for a SQL Server but we typically don't do this.
----------------------------------------------------------------------------
Q1.11: CERTIFIED SYBASE PROFESSIONAL - _CSPDBA_
_________________________________________________________________
Here's a list of commonly asked questions about becoming a _CSPDBA_:
What are the exams like?
The exams are administered by Drake Testing and Technologies and are
given at Drake authorized testing centers. The Environment and
Operations exams each take an hour, and the Fundamentals exam takes an
hour and a half. Each exam contains between 60 and 90 questions. Many
of the questions are _multiple choice_, some are _select all that
apply_ and some are _fill in the blank_. Depending on the exam, a
score of 67% - 72% is required to pass. The exams are challenging, but
fair.
Before taking an exam, Drake provides you with a short _tutorial exam_
that you can take to get an idea of the format of the exam questions.
You receive a report each time you complete an exam. The report shows
the passing score, your total score, and your score in various
sections of the exam. (You aren't told which specific questions you
answered correctly or incorrectly.)
How do I register for the exams?
Call 1-800-8SYBASE, select option 2, then option 2 again. You will be
connected to a Drake representative. Currently each exam costs $150.
What happens once I pass?
You will receive a certificate in the mail about a month after you've
passed all the exams. When you receive your certificate, you'll also
have the opportunity to enter into a licensing agreement that will
allow you to use the Certified Sybase Professional service mark (logo)
in your office and on your business cards. If your company is an Open
Solutions partner, your certification is acknowledged by the
appearance of the CSP logo with your company's name in the Open
Solutions Directory. If you have a CompuServe account, you can obtain
access to a private section of _Sybase OpenLine_, a technical forum on
CompuServe.
What topics are covered?
* Sybase SQL Server Fundamentals Exam Topics:
+ Sybase client/server architecture
+ SQL Server objects
+ Use of tables
+ Use of indexes
+ Use of columns
+ Use of defaults
+ Use of triggers
+ Use of keys
+ Use of check constraints
+ Use of datatypes
+ Use of cursors
+ System datatypes
+ Views
+ Data integrity
+ Rules
+ Select statements
+ Transaction management
+ Locking
+ Stored procedures
+ Local and global variables
* Sybase SQL Server Environment Exam Topics:
+ Configuration and control
+ Starting the SQL Server
+ Accessing remote servers
+ Stopping the SQL Server
+ Using buildmaster
+ Installing the SQL Server
+ Using the standard databases
+ Admin Utilities and Tools
+ System stored procedures
+ Using system tables
+ Load and unload utilities
+ Resources
+ Disk mirroring
+ Creating databases
+ Managing segments
+ Managing transaction logs
+ Managing thresholds
+ Managing audit logs
+ Devices
+ Security
+ Establishing security
+ Roles
+ Managing user accounts
* Sybase SQL Server Operations Exam Topics:
+ Monitoring
+ Starting the Backup Server
+ Monitoring the errorlog
+ Diagnostics
+ Resolving contention and locking problems
+ Managing application stored procedures
+ Recovery
+ Backup
+ Load
+ Backup strategies
+ Security
+ Establishing security
+ Roles
+ Managing user accounts
+ Admin utilities and tools
+ System stored procedures
+ Using system tables
+ Load and unload utilities
_________________________________________________________________
Q1.12: RAID AND SYBASE
_________________________________________________________________
Here's a short summary of what you need to know about Sybase and RAID.
The newsgroup comp.arch.storage has a detailed FAQ on RAID, but here
are a few definitions:
RAID
RAID means several things at once. It provides increased performance
through disk striping, and/or resistance to hardware failure through
either mirroring (fast) or parity (slower but cheaper).
RAID 0
RAID 0 is just striping. It allows you to read and write quickly, but
provides no protection against failure.
RAID 1
RAID 1 is just mirroring. It protects you against failure, and
generally reads and writes as fast as a normal disk. It uses twice as
many disks as normal (and sends twice as much data across your SCSI
bus, but most machines have plenty of extra capacity on their SCSI
busses.)
_Sybase mirroring always reads from the primary copy, so it does not
increase read performance. _
RAID 0+1
RAID 0+1 (also called RAID 10) is striping and mirroring together.
This gives you the highest read and write performance of any of the
raid options, but uses twice as many disks as normal.
RAID 4/RAID 5
RAID 4 and 5 have disk striping and use 1 extra disk to provide
_parity_. Various vendors have various optimizations, but this RAID
level is generally much slower at writes than any other kind of RAID.
RAID 7
RAID 7 is a marketing slogan used by a company which unethically
advertises on Usenet. I would not advise doing business with them.
Details
Most hardware RAID controllers also provide a battery-backed RAM cache
for writing. This is very useful, because it allows the disk to claim
that the write succeeded before it has done anything. If there is a
power failure, the information will (hopefully) be written to disk
when the power is restored. The cache is very important because
database log writes cause the process doing the writes to stop until
the write is successful. Systems with write caching thus complete
transactions much more quickly than systems without.
What RAID levels should my data, log, etc be on? Well, the log disk is
_frequently written_, so it should not be on RAID 4 or 5. If your data
is _infrequently written_, you could use RAID 4 or 5 for it, because
you don't mind that writes are slow. If your data is frequently
written, you should use RAID 0+1 for it. Striping your data is a very
effective way of avoiding any one disk becoming a hot-spot.
Traditionally Sybase databases were divided among devices by a human
attempting to determine where the hot-spots are. Striping does this in
a straight-forward fashion, and also continues to work if your data
access patterns change.
Your tempdb is data but it is frequently written, so it should not be
on RAID 4 or 5.
If your RAID controller does not allow you to create several different
kinds of RAID volumes on it, than your only hope is to create a huge
RAID 0+1 set. If your RAID controler does not support RAID 0+1, you
shouldn't be using it for database work.
_________________________________________________________________
Q1.13: HOW TO SWAP A DB DEVICE WITH ANOTHER
_________________________________________________________________
Here are four approaches. Before attempting _any_ of the following:
Backup, Backup, Backup.
1. Dump and Restore
1. Backup the databases on the device, drop the databases, drop
the devices. and
2. Rebuild the devices
3. Rebuild the databases (Make sure you recreate the fragments
correctly - See Ed Barlows scripts for a sp that helps you do
this if you've lost your notes. Failure to do this will
possibly lead to log pages in data pages, and vice versa).
4. Reload the database dumps!
2. Twiddle the Data Dictionary - for brave _experts_ only.
1. Shut down the server.
2. Do a physical dump (using _dd(1)_, or such utility) of the
device to be moved.
3. Load the dump to the new device
4. Edit the data dictionary (sysdevices.physname) to point to
the new device.
3. The Mirror Trick
1. Create a mirror of the old device, on the new device.
2. Unmirror the primary device, thereby making the _backup_ the
primary device.
3. Repeat this for all devices until the old disk is free.
4. (Unix only) This option is no use if you need to move a device
now, rather if you anticipate moving a device at some point in the
future.
You may want to use this approach for creating _any_ database.
Create (or use) a directory for symbolic links to the devices you
wish to use. Then create your database, but instead of going to
/dev/device, go to /directory/symlink - When it comes time to move
your devices, you shut down the server, simply _dd(1)_ the data
from the old device to the new device, recreate the symbolic links
to the new device and restart the SQL Server. Simple as that.
_Backups are a requisite in all cases, just in case_.
_________________________________________________________________
Q1.14: SERVER NAMING AND RENAMING
_________________________________________________________________
There are three totally separate places where SQL Server _names_
reside, causing much confusion.
SQL Server Host Machine _interfaces_ File
A _master_ entry in here for server _TEST_ will provide the network
information that the server is expected to listen on. The -S parameter
to the dataserver executable tells the server which entry to look for,
so in the RUN_TEST file, -STEST will tell the dataserver to look for
the entry under TEST in the interfaces file and listen on any network
parameters specified by 'master' entries.
TEST
master tcp ether hpsrv1 1200
query tcp ether hpsrv1 1200
Note that preceding the _master/query_ entries there's a tab.
This is as far as the name _TEST_ is used. Without further
configuration the server does not know its name is _TEST_, nor do any
client applications. Typically there will also be _query_ entries
under _TEST_ in the local _interfaces_ file, and client programs
running on the same machine as the server will pick this connection
information up. However, there is nothing to stop the _query_ entry
being duplicated under another name entirely in the same _interfaces_
file.
ARTHUR
query tcp ether hpsrv1 1200
_isql -STEST_ or _isql -SARTHUR_ will connect to the same server. The
name is simply a search parameter into the _interfaces_ file.
Client Machine _interfaces_ File
Again, as the server name specified to the client is simply a search
parameter for Open Client into the _interfaces_ file, SQL.INI or
WIN.INI the name is largely irrelevant. It is often set to something
that means something to the users, especially where they might have a
choice of servers to connect to. Also multiple query entries can be
set to point to the same server, possibly using different network
protocols. Eg. if _TEST_ has the following master entries on the host
machine:
TEST
master tli spx /dev/nspx/ \xC12082580000000000012110
master tcp ether hpsrv1 1200
Then the client can have a meaningful name:
ACCOUNTS_TEST_SERVER
query tcp ether hpsrv1 1200
or alternative protocols:
TEST_IP
query tcp ether hpsrv1 1200
TEST_SPX
query tli spx /dev/nspx/ \xC12082580000000000012110
sysservers
This system table holds information about remote SQL Servers that
local one might want to connect to, and also provides a method of
naming the local server.
Entries are added using the sp_addserver system procedure - add a
remote server with this format:
sp_addserver server_name, null, network_name
server_name is any name you wish to refer to a remote server by, but
network_name must be the name of the remote server as referenced in
the interfaces file local to your local server. It normally makes
sense to make the server_name the same as the network_name, but you
can easily do:
sp_addserver LIVE, null, ACCTS_LIVE
When you execute for example, exec LIVE.master..sp_helpdb the local
SQL Server will translate LIVE to ACCTS_LIVE and try and talk to
ACCTS_LIVE via the ACCTS_LIVE entry in the local interfaces file.
Finally, a variation on the sp_addserver command:
sp_addserver LOCALSRVNAME, local
names the local server (after a restart). This is the name the server
reports in the errorlog at startup, the value returned by
@@SERVERNAME, and the value placed in Open Client server messages. It
can be completely different from the names in RUN_SRVNAME or in local
or remote interfaces - it has _no_ bearing on connectivity matters.
_________________________________________________________________
Q1.15: HOW CAN I TELL THE DATETIME MY SERVER STARTED?
_________________________________________________________________
Method #1
The normal way would be to look at the errorlog, but this is not
always convenient or even possible. From a SQL session you find out
the server startup time to within a few seconds using:
select "Server Start Time" = crdate
from master..sysdatabases
where name = "tempdb"
Method #2
Another useful query is:
select * from sysengines
which gives the address and port number at which the server is
listening.
_________________________________________________________________
Q1.16: RAW PARTITIONS OR REGULAR FILES?
_________________________________________________________________
Hmmm... as always, this answer depends on the vendor's implementation
on a cooked file system for the SQL Server...
Performance Hit (synchronous vs asynchronous)
If on this platform, the SQL Server performs file system I/O
synchronously then the SQL Server is blocked on the read/write and
throughput is decreased tremendously.
The way the SQL Server typically works is that it will issue an I/O
(read/write) and save the I/O control block and continue to do other
work (on behalf of other connections). It'll periodically poll the
workq's (network, I/O) and resume connections when their work has
completed (I/O completed, network data xmit'd...).
Performance Hit (bcopy issue)
Assuming that the file system I/O is asynchronous (this can be done on
SGI), a performance hit may be realized when bcopy'ing the data from
kernel space to user space.
Cooked I/O typically (again, SGI has something called directed I/O
which allows I/O to go directly to user space) has to go from disk, to
kernel buffers and from kernel buffers to user space; on a read. The
extra layer with the kernel buffers is inherently slow. The data is
moved from kernel buffers to/fro user space using bcopy(). On small
operations this typically isn't that much of an issue but in a RDBMS
scenario the bcopy() layer is a significant performance hit because
it's done so often...
Performance Gain!
It's true, using file systems, at times you can get performance gains
assuming that the SQL Server on your platform does the I/O
asynchronously (although there's a caveat on this too... I'll cover
that later on).
If your machine has sufficient memory and extra CPU capacity, you can
realize some gains by having writes return immediately because they're
posted to memory. Reads will gain from the anticipatory fetch
algorithm employed by most O/S's.
You'll need extra memory to house the kernel buffered data and you'll
need extra CPU capacity to allow bdflush() to write the dirty data out
to disk... eventually... but with everything there's a cost: extra
memory and free CPU cycles.
One argument is that instead of giving the O/S the extra memory (by
leaving it free) to give it to the SQL Server and let it do its
caching... but that's a different thread...
Data Integrity and Cooked File System
If the Sybase SQL Server is _not_ certified to be used over a cooked
file system, because of the nature of the kernel buffering (see the
section above) you may face database corruption by using cooked file
system anyway. The SQL Server _thinks_ that it has posted its changes
out to disk but in reality it has gone only to memory. If the machine
halts without bdflush() having a chance to flush memory out to disk,
your database _may_ become corrupted.
Some O/S's allow cooked files to have a _write through_ mode and it
really depends if the SQL Server has been certified on cooked file
systems. If it has, it means that when the SQL Server opens a device
which is on a file system, it fcntl()'s the device to write-through.
When to use cooked file system?
I typically build my tempdb on cooked file system and I don't worry
about data integrity because tempdb is _rebuilt_ everytime your SQL
Server is rebooted.
_________________________________________________________________
Q1.17: IS SYBASE Y2K (Y2000) COMPLIANT?
_________________________________________________________________
Sybase is year 2000 compliant at specific revisions of each product.
Full details are available at http://www.sybase.com, specifically (as
these links will undoubtly change):
http://www.sybase.com/success/inc/corpinfo/year2000_int.html
http://www.sybase.com/Company/corpinfo/year2000_matrix.html
Pablo Sanchez
URL: http://reality.sgi.com/pablo/Sybase_FAQ
Q2.1: CHANGING VARCHAR(M) TO VARCHAR(N)
_________________________________________________________________
Before you start:
select max(datalength(column_name)) from _affected_table_
In other words, _please_ be sure you're going into this with your head
on straight.
How To Change System Catalogs
This information is _Critical To The Defense Of The Free World_, and
you would be _Well Advised To Do It Exactly As Specified_:
use master
go
sp_configure "allow updates", 1
go
reconfigure with override /* System 10 and below */
go
use _victim_database_
go
select name, colid
from syscolumns
where id = object_id("_affected_table_")
go
begin tran
go
update syscolumns
set length = _new_value_
where id = object_id("_affected_table_")
and colid = _value_from_above_
go
update sysindexes
set maxlen = maxlen + _increase/decrease?_
where id=object_id("_affected_table_")
and indid = 0
go
/* check results... cool? Continue... else _rollback tran_ */
commit tran
go
use master
go
sp_configure "allow updates", 0
go
reconfigure /* System 10 and below */
go
_________________________________________________________________
Q2.2: FAQ ON PARTITIONING
_________________________________________________________________
Index of Sections
* What Is Table Partitioning?
+ Page Contention for Inserts
+ I/O Contention
+ Caveats Regarding I/O Contention
* Can I Partition Any Table?
+ How Do I Choose Which Tables To Partition?
* Does Table Partitioning Require User-Defined Segments?
* Can I Run Any Transact-SQL Command on a Partitioned Table?
* How Does Partition Assignment Relate to Transactions?
* Can Two Tasks Be Assigned to the Same Partition?
* Must I Use Multiple Devices to Take Advantage of Partitions?
* How Do I Create A Partitioned Table That Spans Multiple Devices?
* How Do I Take Advantage of Table Partitioning with bcp in?
* Getting More Information on Table Partitioning
What Is Table Partitioning?
Table partitioning is a procedure that creates multiple page chains
for a single table.
The primary purpose of table partitioning is to improve the
performance of concurrent inserts to a table by reducing contention
for the last page of a page chain.
Partitioning can also potentially improve performance by making it
possible to distribute a table's I/O over multiple database devices.
Page Contention for Inserts
By default, SQL Server stores a table's data in one double-linked set
of pages called a page chain. If the table does not have a clustered
index, SQL Server makes all inserts to the table in the last page of
the page chain.
When a transaction inserts a row into a table, SQL Server holds an
exclusive page lock on the last page while it inserts the row. If the
current last page becomes full, SQL Server allocates and links a new
last page.
As multiple transactions attempt to insert data into the table at the
same time, performance problems can occur. Only one transaction at a
time can obtain an exclusive lock on the last page, so other
concurrent insert transactions block each other.
Partitioning a table creates multiple page chains (partitions) for the
table and, therefore, multiple last pages for insert operations. A
partitioned table has as many page chains and last pages as it has
partitions.
I/O Contention
Partitioning a table can improve I/O contention when SQL Server writes
information in the cache to disk. If a table's segment spans several
physical disks, SQL Server distributes the table's partitions across
fragments on those disks when you create the partitions.
A fragment is a piece of disk on which a particular database is
assigned space. Multiple fragments can sit on one disk or be spread
across multiple disks.
When SQL Server flushes pages to disk and your fragments are spread
across different disks, I/Os assigned to different physical disks can
occur in parallel.
To improve I/O performance for partitioned tables, you must ensure
that the segment containing the partitioned table is composed of
fragments spread across multiple physical devices.
Caveats Regarding I/O Contention
Be aware that when you use partitioning to balance I/O you run the
risk of disrupting load balancing even as you are trying to achieve
it. The following scenarios can keep you from gaining the load
balancing benefits you want:
* You are partitioning an existing table. The existing data could be
sitting on any fragment. Because partitions are randomly assigned,
you run the risk of filling up a fragment. The partition will then
steal space from other fragments, thereby disrupting load
balancing.
* Your fragments differ in size.
* The segment maps are configured such that other objects are using
the fragments to which the partitions are assigned.
* A very large bcp job inserts many rows within a single
transaction. Because a partition is assigned for the lifetime of a
transaction, a huge amount of data could go to one particular
partition, thus filling up the fragment to which that partition is
assigned.
Can I Partition Any Table?
No. You cannot partition the following kinds of tables:
1. Tables with clustered indexes
2. SQL Server system tables
3. Work tables
4. Temporary tables
5. Tables that are already partitioned. However, you can unpartition
and then re-partition tables to change the number of partitions.
How Do I Choose Which Tables To Partition?
You should partition heap tables that have large amounts of concurrent
insert activity. (A heap table is a table with no clustered index.)
Here are some examples:
1. An "append-only" table to which every transaction must write
2. Tables that provide a history or audit list of activities
3. A new table into which you load data with bcp in. Once the data is
loaded in, you can unpartition the table. This enables you to
create a clustered index on the table, or issue other commands not
permitted on a partition table.
Does Table Partitioning Require User-Defined Segments?
No. By design, each table is intrinsically assigned to one segment,
called the default segment. When a table is partitioned, any
partitions on that table are distributed among the devices assigned to
the default segment.
In the example under "How Do I Create A Partitioned Table That Spans
Multiple Devices?", the table sits on a user-defined segment that
spans three devices.
Can I Run Any Transact-SQL Command on a Partitioned Table?
No. Once you have partitioned a table, you cannot use any of the
following Transact-SQL commands on the table until you unpartition it:
1. create clustered index
2. drop table
3. sp_placeobject
4. truncate table
5. alter table table_name partition n
How Does Partition Assignment Relate to Transactions?
A user is assigned to a partition for the duration of a transaction.
Assignment of partitions resumes with the first insert in a new
transaction. The user holds the lock, and therefore partition, until
the transaction ends.
For this reason, if you are inserting a great deal of data, you should
batch it into separate jobs, each within its own transaction. See "How
Do I Take Advantage of Table Partitioning with bcp in?", for details.
Can Two Tasks Be Assigned to the Same Partition?
Yes. SQL Server randomly assigns partitions. This means there is
always a chance that two users will vie for the same partition when
attempting to insert and one would lock the other out.
The more partitions a table has, the lower the probability of users
trying to write to the same partition at the same time.
Must I Use Multiple Devices to Take Advantage of Partitions?
It depends on which type of performance improvement you want.
Table partitioning improves performance in two ways: primarily, by
decreasing page contention for inserts and, secondarily, by decreasing
i/o contention. "What Is Table Partitioning?" explains each in detail.
If you want to decrease page contention you do not need multiple
devices. If you want to decrease i/o contention, you must use multiple
devices.
How Do I Create A Partitioned Table That Spans Multiple Devices?
Creating a partitioned table that spans multiple devices is a
multi-step procedure. In this example, we assume the following:
* We want to create a new segment rather than using the default
segment.
* We want to spread the partitioned table across three devices,
data_dev1, data_dev2, and data_dev3.
Here are the steps:
1. Define a segment:
sp_addsegment newsegment, my_database,data_dev1
2. Extend the segment across all three devices:
sp_extendsegment newsegment, my_database, data_dev2
sp_extendsegment newsegment, my_database, data_dev3
3. Create the table on the segment:
create table my_table
(names, varchar(80) not null)
on newsegment
4. Partition the table:
alter table my_table partition 30
How Do I Take Advantage of Table Partitioning with bcp in?
You can take advantage of table partitioning with bcp in by following
these guidelines:
1. Break up the data file into multiple files and simultaneously run
each of these files as a separate bcp job against one table.
Running simultaneous jobs increases throughput.
2. Choose a number of partitions greater than the number of bcp jobs.
Having more partitions than processes (jobs) decreases the
probability of page lock contention.
3. Use the batch option of bcp in. For example, after every 100 rows,
force a commit. Here is the syntax of this command:
bcp table_name in filename -b100
Each time a transaction commits, SQL Server randomly assigns a new
partition for the next insert. This, in turn, reduces the
probability of page lock contention.
Getting More Information on Table Partitioning
For more information on table partitioning, see the chapter on
controlling physical data placement in the SQL Server Performance and
Tuning Guide.
_________________________________________________________________
Q2.3: HOW DO I TURN OFF _MARKED SUSPECT_ ON MY DATABASE?
_________________________________________________________________
Say one of your database is marked suspect as the SQL Server is coming
up. Here are the steps to take to unset the flag.
_Remember to fix the problem that caused the database to be marked
suspect after switching the flag. _
Pre System 10
1. sp_configure "allow updates", 1
2. reconfigure with override
3. select status - 320 from sysdatabases where dbid =
db_id("my_hosed_db") - save this value.
4. begin transaction
5. update sysdatabases set status = _-32767_ where dbid =
db_id("my_hosed_db")
6. commit transaction
7. you should be able to access the database for it to be cleared
out. If not:
1. shutdown
2. startserver -f RUN_*
8. _fix the problem that caused the database to be marked suspect_
9. begin transaction
10. update sysdatabases set status = _saved_value_ where dbid =
db_id("my_hosed_db")
11. commit transaction
12. sp_configure "allow updates", 0
13. reconfigure
System 10
1. sp_configure "allow updates", 1
2. reconfigure with override
3. select status - 320 from sysdatabases where dbid =
db_id("my_hosed_db") - save this value.
4. begin transaction
5. update sysdatabases set status = _-32768_ where dbid =
db_id("my_hosed_db")
6. commit transaction
7. shutdown
8. startserver -f RUN_*
9. _fix the problem that caused the database to be marked suspect_
10. begin transaction
11. update sysdatabases set status = _saved_value_ where dbid =
db_id("my_hosed_db")
12. commit transaction
13. sp_configure "allow updates", 0
14. reconfigure
15. shutdown
16. startserver -f RUN_*
_________________________________________________________________
Q2.4: HOW TO MANUALLY DROP A TABLE
_________________________________________________________________
Occasionally you may find that after issuing a _drop table_ command
that the SQL Server crashed and consequently the table didn't drop
entirely. Sure you can't see it but that sucker is still floating
around somewhere.
Here's a list of instructions to follow when trying to drop a corrupt
table:
1.
sp_configure allow, 1
go
reconfigure with override
go
2. Write _db_id_ down.
use _db_name_
go
select db_id()
go
3. Write down the _id_ of the _bad_table_:
select id from sysobjects where name = _bad_table_name_
go
4. You will need these index IDs to run _dbcc extentzap_. Also,
remember that if the table has a clustered index you will need to
run _extentzap_ on index "0", even though there is no sysindexes
entry for that indid.
select indid from sysindexes where id = _table_id_
go
5. This is not required but a good idea:
begin transaction
go
6. Type in this short script, this gets rid of all system catalog
information for the object, including any object and procedure
dependencies that may be present.
Some of the entries are unnecessary but better safe than sorry.
declare @obj int
select @obj = id from sysobjects where name =
delete syscolumns where id = @obj
delete sysindexes where id = @obj
delete sysobjects where id = @obj
delete sysprocedures where id in
(select id from sysdepends where depid = @obj)
delete sysdepends where depid = @obj
delete syskeys where id = @obj
delete syskeys where depid = @obj
delete sysprotects where id = @obj
delete sysconstraints where tableid = @obj
delete sysreferences where tableid = @obj
delete sysdepends where id = @obj
go
7. Just do it!
commit transaction
go
8. Gather information to run _dbcc extentzap_:
use master
go
sp_dboption _db_name_, read, true
go
use _db_name_
go
checkpoint
go
9. Run _dbcc extentzap_ once for _each_ index (including index 0, the
data level) that you got from above:
use master
go
dbcc traceon (3604)
go
dbcc extentzap (_db_id_, _obj_id_, _indx_id_, 0)
go
dbcc extentzap (_db_id_, _obj_id_, _indx_id_, 1)
go
Notice that extentzap runs _twice_ for each index. This is because
the last parameter (the _sort_ bit) might be 0 or 1 for each index,
and you want to be absolutely sure you clean them all out.
10. Clean up after yourself.
sp_dboption _db_name_, read, false
go
use _db_name_
go
checkpoint
go
sp_configure allow, 0
go
reconfigure with override
go
_________________________________________________________________
Q2.5: WHY NOT MAX OUT ALL MY COLUMNS?
_________________________________________________________________
People occasionally ask the following valid question:
Suppose I have varying lengths of character strings none of which
should exceed 50 characters.
_Is there any advantage of last_name varchar(50) over this last_name
varchar(255)?_
That is, for simplicity, can I just define all my varying strings to
be varchar(255) without even thinking about how long they may
actually be? Is there any storage or performance penalty for this.
There is no performance penalty by doing this but as another netter
pointed out:
If you want to define indexes on these fields, then you should
specify the smallest size because the sum of the maximal lengths of
the fields in the index can't be greater than 256 bytes.
and someone else wrote in saying:
Your data structures should match the business requirements. This
way the data structure themselves becomes a data dictionary for
others to model their applications (report generation and the like).
_________________________________________________________________
Q2.6: WHAT'S A GOOD EXAMPLE OF A TRANSACTION?
_________________________________________________________________
This answer is geared for Online Transaction Processing (OTLP)
applications.
To gain maximum throughput all your transactions should be in stored
procedures - see Q8.8. The transactions within each stored procedure
should be short and simple. All validation should be done outside of
the transaction and only the modification to the database should be
done within the transaction. Also, don't forget to name the
transaction for _sp_whodo_ - see Q9.2.
The following is an example of a _good_ transaction:
/* perform validation */
select ...
if ... /* error */
/* give error message */
else /* proceed */
begin
begin transaction acct_addition
update ...
insert ...
commit transaction acct_addition
end
The following is an example of a _bad_ transaction:
begin transaction poor_us
update X ....
select ...
if ... /* error */
/* give error message */
else /* proceed */
begin
update ...
insert ...
end
commit transaction poor_us
This is bad because:
* the first update on table X is held throughout the transaction.
The idea with OLTP is to get in and out _fast_.
* If an error message is presented to the end user and we await
their response, we'll maintain the lock on table X until the user
presses return. If the user is out in the can we can wait for
hours.
_________________________________________________________________
Q2.7: WHAT'S A NATURAL KEY?
_________________________________________________________________
Let me think back to my database class... okay, I can't think that far
so I'll paraphrase... essentially, a _natural key_ is a key for a
given table that uniquely identifies the row. It's natural in the
sense that it follows the business or real world need.
For example, assume that social security numbers are unique (I believe
it is strived to be unique but it's not always the case), then if you
had the following employee table:
employee:
ssn char(09)
f_name char(20)
l_name char(20)
title char(03)
Then a natural key would be _ssn_. If the combination of __name_ and
_l_name_ were unique at this company, then another _natural key_ would
be _f_name, l_name_. As a matter of fact, you can have many _natural
keys_ in a given table but in practice what one does is build a
surrogate (or artificial) key.
The surrogate key is guaranteed to be unique because (wait, get back,
here it goes again) it's typically a monotonically increasing value.
Okay, my mathematician wife would be proud of me... really all it
means is that the key is increasing linearly: i+1
The reason one uses a surrogate key is because your joins will be
faster.
If we extended our employee table to have a surrogate key:
employee:
id identity
ssn char(09)
f_name char(20)
l_name char(20)
title char(03)
Then instead of doing the following:
where a.f_name = b.f_name
and a.l_name = a.l_name
we'd do this:
We can build indexes on these keys and since Sybase's atomic storage
unit is 2K, we can stash more values per 2K page with smaller indexes
thus giving us better performance (imagine the key being 40 bytes
versus being say 4 bytes... how many 40 byte values can you stash in a
2K page versus a 4 byte value? -- and how much wood could a wood chuck
chuck, if a wood chuck could chuck wood?)
Does it have anything to do with natural joins?
Um, not really... from "A Guide to Sybase..", McGovern and Date, p.
112:
The equi-join by definition must produce a result containing two
identical columns. If one of those two columns is eliminated, what
is left is called the natural join.
_________________________________________________________________
Q2.8: MAKING A STORED PROCEDURE INVISIBLE
_________________________________________________________________
System 11.5 and above
It is now possible to encrypt your stored procedure code that is
stored in the syscomments table. This is preferred than the old method
of deleting the data as deleting will impact future upgrades. You can
encrypt the text with the sp_hidetext system procedure.
Pre-System 11.5
Perhaps you are trying to not allow the buyer of your software
_defncopy_ all your stored procedures. It is perfectly safe to delete
the syscomments entries of any stored procedures you'd like to
protect:
sp_configure "allow updates", 1
go
reconfigure with override /* System 10 and below */
go
use _affected_database_
go
delete syscomments where id = object_id("_procedure_name_")
go
use master
go
sp_configure "allow updates", 0
go
I believe in future releases of Sybase we'll be able to _see_ the SQL
that is being executed. I don't know if that would be simply the
stored procedure name or the SQL itself.
_________________________________________________________________
Q2.9: SAVING SPACE WHEN INSERTING ROWS MONOTONICALLY
_________________________________________________________________
If the columns that comprise the clustered index are monotonically
increasing (that is, new row key values are greater than those
previously inserted) the following System 11 dbcc tune will not split
the page when it's half way full. Rather it'll let the page fill and
then allocate another page:
dbcc tune(ascinserts, 1, "_my_table_")
By the way, SyBooks is wrong when it states that the above needs to be
reset when the SQL Server is rebooted. This is a permanent setting.
To undo it:
dbcc tune(ascinserts, 0, "_my_table_")
_________________________________________________________________
Q2.10: HOW TO COMPUTE DATABASE FRAGMENTATION
_________________________________________________________________
Command
dbcc traceon(3604)
go
dbcc tab(production, _my_table_, 0)
go
Interpretation
A delta of one means the next page is on the same track, two is a
short seek, three is a long seek. You can play with these constants
but they aren't that important.
A table I thought was unfragmented had L1 = 1.2 L2 = 1.8
A table I thought was fragmented had L1 = 2.4 L2 = 6.6
How to Fix
You fix a fragmented table with clustered index by dropping and
creating the index. This measurement isn't the correct one for tables
without clustered indexes. If your table doesn't have a clustered
index, create a dummy one and drop it.
_________________________________________________________________
Q2.11: Tasks a DBA should do...
----------------------------------------------------------------------------
I was asked by a poster to list what a DBA's tasks ought to be. Here's what
I believe (this will evolve as time progresses):
DBA Tasks
Task Reason Period
If your SQL Server permits,
I consider daily before your database
dbcc checkdb, these the dumps. If this is not possible
checkcatalog, minimal dbcc's due to the size of your
checkalloc to ensure the databases, then try the
integrity of different options so that the
your database end of, say, a week, you've run
them all.
Disaster recovery Always be
scripts - scripts prepared for
to rebuild your SQL the worst. Make
Server in case of sure to test
hardware failure them.
scripts to
logically dump your
master database,
that is bcp the You can
critical system selectively
tables: rebuild your
sysdatabases, database in Daily
sysdevices, case of
syslogins, hardware
sysservers, failure
sysusers,
syssegments,
sysremotelogins
A system
%ls -la upgrade is After any change as well as
disk_devices known to change daily
the
permissions.
dump the user
databases CYA Daily
dump the
transaction logs CYA Daily
dump the master After any change as well as
database CYA daily
System 11 and This is the
beyond - save the configuration After any change as well as
$DSQUERY.cfg to that you've daily
tape dialed in, why
redo the work?
Depending on how often your
major tables change. Some tables
are pretty much static (e.g.
lookup tables) so they don't
update statistics need an update statistics, other
on frequently To ensure the tables suffer severe trauma
changed tables and performance of (e.g. massive
sp_recompile your SQL Server updates/deletes/inserts) so an
update stats needs to be run
either nightly/weekly/monthly.
This should be done using
cronjobs.
create a dummy SQL
Server and do bad
things to it: See disaster
delete devices, recovery! When time permits
destroy
permissions...
Talk to the It's better to
application work with them As time permits.
developers. than against
them.
Learn new tools So you can As time permits.
sleep!
Read c.d.s Passes the Priority One!
time.
----------------------------------------------------------------------------
Q2.10: HOW TO IMPLEMENT DATABASE SECURITY
_________________________________________________________________
This is a brief run-down of the features and ideas you can use to
implement database security:
Logins, Roles, Users, Aliases and Groups
* sp_addlogin - Creating a login adds a basic authorisation for an
account - a username and password - to connect to the server. By
default, no access is granted to any individual databases.
* sp_adduser - A user is the addition of an account to a specific
database.
* sp_addalias - An alias is a method of allowing an account to use a
specific database by impersonating an existing database user or
owner.
* sp_addgroup - Groups are collections of users at the database
level. Users can be added to groups via the sp_adduser command.
A user can belong to only one group - a serious limitation that
Sybase might be addressing soon according to the ISUG enhancements
requests. Permissions on objects can be granted or revoked to or
from users or groups.
* sp_role - A role is a high-level Sybase authorisation to act in a
specific capacity for administration purposes. Refer to the Sybase
documentation for details.
Recommendations
Make sure there is a unique login account for each physical person
and/or process that uses the server. Creating generic logins used by
many people or processes is a _bad idea_ - there is a loss of
accountability and it makes it difficult to track which particular
person is causing server problems when looking at the output of
sp_who. Note that the output of sp_who gives a hostname - properly
coded applications will set this value to something meaningful (ie.
the machine name the client application is running from) so you can
see where users are running their programs. Note also that if you look
at master..sysprocesses rather than just sp_who, there is also a
program_name. Again, properly coded applications will set this (eg. to
'isql') so you can see which application is running. If you're coding
your own client applications, make sure you set hostname and
program_name via the appropriate Open Client calls. One imaginative
use I've seen of the program_name setting is to incorporate the
connection time into the name, eg APPNAME-DDHHMM (you have 16
characters to play with), as there's no method of determining this
otherwise.
Set up groups, and add your users to them. It is much easier to manage
an object permissions system in this way. If all your permissions are
set to groups, then adding a user to the group ensures that users
automatically inherit the correct permissions - administration is
*much* simpler.
Objects and Permissions
Access to database objects is defined by granting and/or revoking
various access rights to and from users or groups. Refer to the Sybase
documentation for details.
Recommendations
The ideal setup has all database objects being owned by the dbo,
meaning no ordinary users have any default access at all. Specific
permissions users require to access the database are granted
explicitly. As mentioned above - set permissions for objects to a
group and add users to that group. Any new user added to the database
via the group then automatically obtains the correct set of
permissions.
Preferably, no access is granted at all to data tables, and all read
and write activity is accomplished through stored procedures that
users have execute permission on. The benefit of this from a security
point of view is that access can be rigidly controlled with reference
to the data being manipulated, user clearance levels, time of day, and
anything else that can be programmed via T-SQL. The other benefits of
using stored procedures are well known (see Q8.8). Obviously whether
you can implement this depends on the nature of your application, but
the vast majority of in-house-developed applications can rely solely
on stored procedures to carry out all the work necessary. The only
server-side restriction on this method is the current inability of
stored procedures to adequately handle text and image datatypes (see
Q8.12). To get around this views can be created that expose only the
necessary columns to direct read or write access.
Views
Views can be a useful general security feature. Where stored
procedures are inappropriate views can be used to control access to
tables to a lesser extent. They also have a role in defining row-level
security - eg. the underlying table can have a security status column
joined to a user authorisation level table in the view so that users
can only see data they are cleared for. Obviously they can also be
used to implement column-level security by screening out sensitive
columns from a table.
Triggers
Triggers can be used to implement further levels of security - they
could be viewed as a last line of defence in being able to rollback
unauthorised write activity (they cannot be used to implement any read
security). However, there is a strong argument that triggers should be
restricted to doing what they were designed for - implementing
referential integrity - rather being loaded up with application logic.
Administrative Roles
With Sybase version 10 came the ability to grant certain
administrative roles to user accounts. Accounts can have sa-level
privilege, or be restricted to security or operator roles - see
sp_role.
Recommendations
The use of any generic account is not a good idea. If more than one
person requires access as sa to a server, then it is more accountable
and traceable if they each have an individual account with sa_role
granted.
_________________________________________________________________
Q2.13: HOW TO SHRINK A DATABASE
_________________________________________________________________
_Warning: This document has not been reviewed. Treat it as
alpha-test quality information and report any problems and
suggestions to br...@sybase.com _
It has historically been difficult to shrink any database except
tempdb (because it is created fresh every boot time). The two methods
commonly used have been:
1. Ensure that you have scripts for all your objects (some tools like
SA Companion or DB Artisian can create scripts from an existing
database), then bcp out your data, drop the database, recreate it
smaller, run your scripts, and bcp in your data.
2. Use a third-party tool such as DataTool's SQL Backtrack, which in
essence automates the first process.
This technote outlines a third possibility that can work in most
cases.
An Unsupported Method to Shrink a Database
This process is fairly trivial in some cases, such as removing a
recently added fragment or trimming a database that has a log fragment
as its final allocation, but can also be much more complicated or time
consuming than the script and bcp method.
General Outline
The general outline of how to do it is:
1. Make a backup of the current database
2. Migrate data from sysusages fragments with high lstart values to
fragments with low lstart values.
3. Edit sysusages to remove high lstart fragments that no longer have
data allocations.
4. Reboot sql server.
Details
1. Dump your database. If anything goes wrong, you will need to
recover from this backup!
2. Decide how many megabytes of space you wish to remove from your
database.
3. Examine sysusages for the database. You will be shrinking the
database by removing the fragments with the highest lstart values.
If the current fragments are not of appropriate sizes, you may
need to drop the database, recreate it so there are more
fragments, and reload the dump.
A trivial case: An example of a time when you can easily shrink a
database is if you have just altered it and are sure there has been
no activity on the new fragment. In this case, you can directly
delete the last row in sysusages for the db (this row was just added
by alter db) and reboot the server and it should come up cleanly.
4. Change the segmaps of the fragments you plan to remove to 0. This
will prevent future data allocations to these fragments.
Note: If any of the fragments you are using have user defined
segments on them, drop those segments before doing this.
sp_configure "allow updates", 1
go
reconfigure with override
go
update sysusages set segmap = 0
where dbid = <dbid> and lstart = <lstart>
go
dbcc dbrepair(<dbname>, remap)
go
Ensure that there is at least one data (segmap 3) and one log
(segmap 4) fragment, or one mixed (segmap 7) fragment.
If the server has been in use for some time, you can shrink it by
deleting rows from sysusages for the db, last rows first , after
making sure that no objects have any allocations on the usages.
5. Determine which objects are on the fragments you plan to remove.
dbcc traceon(3604)
go
dbcc usedextents( dbid,0,0,1)
go
Find the extent with the same value as the lstart of the first
fragment you plan to drop. You need to migrate every object
appearing from this point on in the output.
6. Migrate these objects onto earlier fragments in the database.
Objids other than 0 or 99 are objects that you must migrate or
drop. You can migrate a user table by building a new clustered
index on the table (since the segmap was changed, the new
allocations will not go on this fragment).
You can migrate some system tables (but not all) using the
sp_fixindex command to rebuild it's clustered index. However,
there are a few system tables that cannot have their clustered
indexes rebuilt, and if they have any allocations on the usage,
you are out of luck.
If the objid is 8, then it is the log. You can migrate the log by
ensuring that another usage has a log segment (segmap 4 or 7). Do
enough activity on the database to fill an extents worth of log
pages, then checkpoint and dump tran.
Once you have moved all the objects, delete the row from sysusages
and reboot the server.
Run dbcc checkdb and dbcc checkalloc on the database to be sure
you are ok, then dump the database again.