Sybase数据库维护篇-高级

培训计划

培训内容培训目标培训对象培训内容Sybase数据库简介Sybase产品（ASA，ASE，IQ）ASE它是基于客户/服务器体系结构的数据库OLTP1:OracleServer是一个真正的多进程数据库引擎，Sybase数据库管理系统采用的是单进程多线程的引擎结构。2:Sybase的数据库引擎还代替操作系统来管理一部分硬件资源，如端口、内存、硬盘，绕过了操作系统这一环节，提高了性能。数据库(raid0,1,5)数据库设备分配结构数据库page分配结构Sybase的多库结构

安装Sybase数据库时自动生成的下列系统数据库：●主数据库master:数据库全面控制和管理用户数据库以及SQLSERVER上的一切操作。●模型数据库model:为新建的用户数据库提供一个原形●系统过程数据库sybsystemprocs:SYBASE的系统存储过程被储存在这个数据库中●临时数据库tempdb:作为临时表和其他的临时工作空间提供一个存储区域。TEMPDB的空间为全体数据库用户共享也可选择下列数据库：●安全审核数据库sybsecurity:包含SQLSERVER的审计系统●示例数据库pubs2:这是一个样本库

●命令语法数据库sybsyntax:为用户提供联机帮助用户数据库用户数据库是指用Createdatabase命令创建的数据库。使用”use数据库名”来切换不同的数据库执行sp_changedbowner改变数据库的属主关系建议事务日志放在单独的设备上(log)master库

它是管理和控制用户数据库以及维护服务器正常运行的核心数据库，它包含许多系统表和系统过程，从总体上控制用户数据库和SQLServer的操作，构成了SYBASE系统的数据字典，它保存了大量的系统信息，如服务器配置、用户、设备等。在master数据库中不允许普通用户在其中创建数据库对象，否则会使得master数据库的事务日志很快变满。如果事务日志用尽，就无法使用dumptransaction命令释放master数据库中的空间。master数据库主要记录信息为：

登录帐号（SQL服务器用户名）；syslogins,sysremolelogins正在执行的过程；sysprocesses可修改的环境变量；sysconfigures系统错误信息；sysmessagesSQL服务器上每个数据库的信息；sysdatabases每个数据库占用的存储空间；sysusages系统上安装的设备及磁带信息；sysdevices活动锁信息；syslocksTEMPDB库

它是个临时数据库，为服务器运行与处理提供一个共享的存储区域，如groupby和orderby的中间结果就存放在这里。Tempdb的空间为服务器中所有数据库的所有用户所共享。每次重启SQLServer，服务器的一个自动进程都拷贝model数据库到tempdb数据库，并清除tempdb中原来的内容。因此tempdb中的用户表都是临时的。临时表分为两类：可共享的和不可共享的。不可共享的临时表在由createtable中将符号#置于表名之前创立；可共享的临时表通过createtable中指定表名前缀tempdb..而创立。不可共享的临时表SQLServer自动为其添加数字后缀名，且它只存在于当前会话中数据库有关概念（segment/device)数据库设备从逻辑上被划分为数据库段以允许将某一特定的对象放置在指定的段上（创建对象时指定），一数据库设备可拥有多达192个段，一段可使用255个逻辑设备上的存储空间。当用户创建一个数据库时，SQLSERVER会自动在该数据库中创建三个段：SYSTEM、LOGSEGMENT、DEFAULT，这三个段分别用来存储数据库的系统表、事务日志和其他数据库对象提高性能：处于不同磁盘设备上的段可以并行地读写；处理大表：利用段，可以将一个大表分段放在独立的物理设备上。所谓数据库设备，不是指一个可识别的物理设备，而是指用于存储数据库和数据库对象的磁盘原始分区或操作系统文件。初始化数据库设备使用DISKINIT命令：DISKINITNAME=设备名，PHYSNAME=物理设备名，VDEVNO=虚拟设备号，SIZE=块数数据库有关概念（segment/device)SybasesegmentsTable创建于segment上每个db最多可有32个segments创建段：sp_addsegment‘seg',‘db_name',‘device_name‘注：创建表需要指定索引和数据的存放段名称Device和segment关系：单个device上可以创建多个segment，单个segment可以跨越多个deviceTable和segment关系： 单个segment上创建多个table，单个table可以跨越多个segment（分区表）。数据库对象(1)表(Tables)(2)视图(Views):视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，视图的作用类似于筛选。作用:安全;可使复杂的查询易于理解和使用

Index与性能索引(index):索引就是加快检索表中数据的方法。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必Tablescan。

1.索引可以加快数据库的检索速度2.索引降低了数据库插入、修改、删除等维护任务的速度3.索引创建在表上，不能创建在视图上

4.索引既可以直接创建(createindex)，也可以间接创建(主键约束或者唯一性键约束时)Sybase支持三种不同类型的索引，一是复合索引，即包含几个列的索引；二是唯一性索引，即任意两行中的索引列不能有相同的取值；三是簇类索引（Clusteredindexes）和非簇类索引(Nonclusteredindexs)，Index与性能-2a)普通索引(createindex)和唯一性索引(createUNIQUEindex)b)单个索引和组合索引(最多16个字段组成)c)聚簇索引CLUSTERED和非聚簇索引UNCLUSTERED:聚簇索引：即物理索引，与基表的物理顺序相同(不断重排表行的物理顺序以使其与逻辑索引顺序相一致)，数据值的顺序总是按照顺序排列.即索引树的叶级页包含实际的数据.非聚簇索引:索引顺序与数据物理排列顺序无关,即叶级页指向表中的记录.,一个表最多可建249个非聚簇索引.可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块.每个表最多只能建立一个簇类索引，非簇类索引则可建立多个。与非簇类索引相比，簇类索引的检索速度要快。应当在建立任何非簇类索引以前建立簇类索引。Index与性能-31：选择Clustered索引的建议：主键时常作为where子句的条件

某一列经常以这样的格式出现在where表达式中(x<=column<="y)"

某一列非常频繁地被访问

某列被用作orderby或groupby某列很少被改写

某列常出现在join中。

2：选择Non-Clustered索引的建议：某列常用于Aggregate函数（如Sum,）

某列常用于join,orderby,groupby。

查寻检索出的数据不超过表中数据量的20%Index与性能-4维护索引统计表：

数据库拥有者必须用命令维护统计表。updatestatisticstable_name[index_name]

索引优化调整

用这条命令可以改善创建索引的性能,减少建索引所用的时间。在SQLSERVER11.x版本中可以调整如下参数：sp_configure"extenti/obuffers",nnnn带来的影响是增加了extenti/obuffers大小，在SQLServer使用内存不变情况下，减少了procedure和datacathe，而且同一时刻仅有一个用户能用到extentbuffer。性能

sp_configure'maxmemory'先调整内存值重新启动serversp_cacheconfig‘defaultdatacache’,‘500000’调整‘defaultdatacache‘为500Msybase目录结构它是个临时数据库，为服务器运行与处理提供一个共享的存储区域，如groupby和orderby的中间结果就存放在这里。Tempdb的空间为服务器中所有数据库的所有用户所共享。每次重启SQLServer，服务器的一个自动进程都拷贝model数据库到tempdb数据库，并清除tempdb中原来的内容。因此tempdb中的用户表都是临时的。临时表分为两类：可共享的和不可共享的。不可共享的临时表在由createtable中将符号#置于表名之前创立；可共享的临时表通过createtable中指定表名前缀tempdb..而创立。不可共享的临时表SQLServer自动为其添加数字后缀名，且它只存在于当前会话中由于这些表都是临时的，所以每当用户退出或系统失败，这些表都将消失。数据库属性数据库属性：语法：sp_dboption[dbname,optin_name,{true|false}]aborttranonlogfull日志满时事物中断

allownullsbydefault允许缺省值为空

dbouseonly只有库的属主才可以使用此库

ddlintran是否允许在事物中使用DDL语句

nochkptonrecovery库恢复时是否做checkpoint

nofreespaceacctg是否计算段的空闲空间（可以加快库的恢复时间）

readonly只读

selectinto/bulkcopy/pllsort是否允许BCP批量copy

singleuser是否只允许单用户访问

trunclogonchkpt

数据库全局变量sybase的全局变量格式为@@变量名Select@@error：检查最近执行的语句错误状态=0:成功，否则为错误号Select@@rownum：上一个语句影响的行数Select@@sqlstatus：上次的语句状态信息Select@@servername：服务器名Select@@version：版本

Select@@language：使用的语言

Select@@spid：当前DBServer服务进程ID

Select@@max_connections：可连接的最大连接数

Select@@identity：上次用insert时插入表的identiy值Select@@client_csname:客户端的字符集名称Select@@total_read/write：自启动以来sqlserver从磁盘读/写的页数Sybase事务日志-1每一个数据库，无论是系统数据库，还是用户数据库，都有自己的transactionlog，每个库都有syslogs表。Log记录用户对数据库修改的操作，所以如果不用命令清除，log会一直增长直至占满空间。清除log可用dumptransaction命令；或者开放数据库选项trunclogonchkpt，数据库会每隔一段间隔自动清除log。管理好数据库log是用户操作数据库必须考虑的一面。

事务在运行过程中，SQLServer把事务开始、事务结束以及对数据库的插入、删除和更新等每一个操作作为一个日志记录存放到事务日志中。事务中的更新操作首先在数据库缓冲区（内存）中进行，缓冲区分别有用来记录操作活动的数据页（datapage）和日志页（logpage）。当运行到committran时，日志页首先从缓冲区写到磁盘上，而后数据页从缓冲区写到磁盘上，即遵循“先与日志（write_aheadlog）”的原则，这样保证出现故障的情况下，通过日志能够得到最大限度的恢复。恢复必须撤消发生故障时还未提交的事务。Sybase事务日志-2Transactionlogs和checkpoint进程

heckpoint命令的功能是强制所有“脏”页（自上次写入数据库设备后被更新过的页）写入数据库设备。自动的checkpoint间隔是由SQLServer根据系统活动和系统表sysconfigures中的恢复间隔（recoveryinterval）值计算出的。Transactionlog的大小update,insert和delete的频率

每个transaction中数据的修改量

SQLServer系统参数recoveryinterval值

log是否存到介质上用于数据库恢复

检测log的大小在syslogs表用sp_spaceused命令Sp_helpsegment“logsegment”Sybase事务日志-3log的清除

执行命令dumptransaction截断日志1：dumptransactionwithtruncate_only命令和trunclogonchkpt一样，只是清除log而不保留log到备份设备上。2：dumptransactionwithno_log也可以截断日志若你在对数据库的修改发生时使用dumptransactionwithno_log，你就会冒整个数据库崩溃的风险。在多数情况下，它们被反映成813或605错误。为了在数据库被修改时，删除transactionlog中的不活跃部分可使用dumptransactionwithtruncate_only。3：正常应该是需要备份，应做dumptransactiondatabase_nametodumpdevice。Sybase事务日志-4管理大的transactions有些操作是大批量地修改/bcp数据，log增长速度十分快，未完成之前就可能使log满，引起1105错误1:为避免这些情况，我们可以把这个大的transaction分成几个小的transactions，并执行dumptransaction动作。例如：1>usemaster

2>go

1>sp_dboptiondatabase_name,trunc,true

2>go

1>usedatabase_name

2>go

1>checkpoint

2>go

Bcp...-b100(onunix)

一个批执行100行拷贝Sybase事务日志-5增加阈值（sp_addthreshold）例如：sp_addthresholdpubs2,logsegment,200,dump_transaction

其中，存储过程定义为：createproceduredump_transaction

@dbnamevarchar(30),

@segmentnamevarchar(30),

@space_leftint,

@statusint

as

dumptransaction@dbnameto"/dev/rmtx"那么，当日志段上可用空间小于200页时，SQLServer运行存储过程dump_transaction,将pubs2数据库的日志转储到另一台设置上。数据库锁

数据库行/页/表锁为了在使用的一个表中改变加锁类型，可以采用如下的句法格式：altertable<tablename>lock[allpages|datapages|datarows]在索引页面中不会破坏事务加锁。相反，而是采用了一种称之为锁存的机制。锁存是一种类似于旋转锁（spinlocks）的同步方法sp_configurenumberofpre-allocatedextent设置为16也将对系统性能有明显的积极的效果，减小锁资源的竞争。共享锁、排他锁，死锁数据库的锁存技术将那些在索引页中发生死锁的情形尽量排除掉。减小死锁的方法：1：尽量对数据多次并发访问2：配置“带有加锁方式的读提交”（"readcommittedwithlock”），并可用如下的命令加以配置：sp_configure‘readcommittedwithlock’,0|1数据库死锁

可能发生的死锁的原因：1：插入与非群聚性的索引访问2：数据更新的范围当第一个用户到达所确定的页面边缘，即该范围即将终止，下一批所组块并妨碍的页面范围即将开始之时，就可能发生死锁。3：按照不同顺序访问表即使访问行是不同的，对表按照任何不一致的顺序进行访问，也会意味着可能发生死锁。4：如何查锁阻塞Sp_whosp_lock,select*fromsysprocesseswhereblocked>05:如何查锁的表名

sp_lock查其中table_id=XXXXXXX对应的表select*fromsysobjectswhereid=XXXXXXX数据库的备份和恢复

数据库的备份：必须启动backupserver由于文件的大小超出了操作系统的用户最大文件限制。而有些操作系统不支持大于2GB的文件，这时可以使用BackupServer将一个数据库后备到多个文件中。dumpdatabasepubs2to"/usr/sybase/pubs2_dump.1"stripeon"/usr/sybase/pubs2_dump.2"stripeon"/usr/sybase/pubs2_dump.3"go

这种方法还可以提高后备及恢复的速度，但注意恢复也必须用相应多的设备。例如：1>loaddatabasepubs2from"/usr/sybase/pubs2_dump.1"2>stripeon"/usr/sybase/pubs2_dump.2"3>stripeon"/usr/sybase/pubs2_dump.3"4>go如何生成bcp命令文件以pubs2为例编辑一个文本文件select.sql，内容如下：

setnocounton

usepubs2

go

select"bcppubs2.."+name+"out"+name+".bcp-Usa-P-c"

fromsysobjectswheretype="U"

go检查修改服务器基本配置参数内存配置：sp_configure“totalmemory”,new_value

(修改内存配置，new_value的单位为2K)CPU数：sp_configure“maxonlineengines”,cpu_number(修改CPU配置，cpu_number小于等于实际cpu数)并发用户连接数：sp_configure“numberofuserconnections”

(检查当前的并发用户连接数)sp_configure"numberoflock"sp_configure"procedurecachesize"sp_configure"numberofuser"sp_configure"numberofopendatabases"sp_configure"numberofopenobjects",150000sp_configure"numberofopenindexes",150000如何解决数据库被挂起的问题1现象：Error926

SeverityLevel14

ErrorMessageText

Database'xx'cannotbeopened-ithasbeenmarkedSUSPECTbyrecoverExplanation(一)：你使用Transact_SQL命令操作这个数据库的数据时,出现这个信息,这是一个严重的错误,如果你要使用这个数据库的数据,必须改正这个错误.

(二)：启动BackupServer,后备master数据库

1>dumpdatabasemasterto"/usr/sybase/master.dup"

2>go（三)用isql登录到SQLServer,须用sa帐号(本文以pubs2数据库为例)1>sp_configure“allowupdates”,1允许修改mask库

2>go

1>begintran

2>go

1>usemaster

2>go

1>updatesysdatabases

2>setstatus=-32768

3>Wherename="pubs2"

4>go

如何解决数据库被挂起的问题2如果得到(1rowaffected),则1>commit

2>go否则1>rollback

2>go（四)重新启动SQLServer.

注：SQLServer重新启动之后，当发现数据库本身存在不可恢复的问题时，如数据页损坏等，且没有完好的数据库备份，一定要用bcp...out备份用户数据库数据。此时，以下步骤省略，并按照“如何删除坏的用户数据库”文章删除此数据库。之后重建此数据库，恢复备份。否则，按以下步骤继续操作：

用sa帐号注册到SQLServer.

1>begintran

2>go

1>usemaster

2>go

如何解决数据库被挂起的问题31>updatesysdatabases

2>setstatus=0

3>Wherename="pubs2"

4>go如果得到(1rowaffected),则

1>commit

2>go否则1>rollback

2>go

1>sp_configure“allowupdates”,0不允许修改mask库

2>go(五)重新启动SQLServer.(六)如果你的数据库原来有dboption(例如"selectinto","trunclogonchkpt"等),你需要重新设置这些option.(七)数据库已经恢复可使用状态后，运行dbcc命令检查数据库的一致性(八)备份用户数据库：

1>dumpdatabasepubs2to"/usr/sybase/pubs2.dup"

2>go如何终止数据库恢复过程1当某一正常运行的大事务（例如：update、delete操作）被终止，且重新启动server后，运行该事务的数据库处于恢复状态，通常这种状态会持续很长时间，当在此恢复过程中没有出现任何异常时，建议耐心等待恢复过程完成。同时提供以下方法来终止此恢复过程，但请注意这些操作将带来数据不一致性。必要时，希望用户用完整、可靠的数据库备份恢复此数据库。

(1)启动BackupServer后，备份master数据库（2）用isql登录到SQLServer,须用sa帐号(本文以pubs2数据库为例)1>sp_configure"allowupdates",1

2>go

1>begintran

2>go

1>usemaster

2>go

1>updatesysdatabases

2>setstatus=-32768

3>Wherename="pubs2"

4>go如果得到(1rowaffected),则commit提交，否则rollback

如何终止数据库恢复过程2（3）这时重新启动SQLServer,再用sa帐号登录到SQLServer.

1>dumptranpubs2withno_log

2>go

1>begintran

2>go

1>usemaster

2>go

1>updatesysdatabases

2>setstatus=0

3>Wherename="pubs2"

4>go

如果得到(1rowaffected),则commit提交，否则rollback(4)如果你的数据库原来有dboption(例如"selectinto","trunclogonchkpt"等),你需要重新设置这些option..(5)当数据库已经恢复可使用状态后，运行dbcc命令检查数据库的一致性(参照"如何检查数据库中数据一致性"文章)(6)备份用户数据库

如何删除坏的用户数据库当使用dropdatabase无法删除数据库时，使用本文所示方法可以删除。(1)设置允许修改系统表

1>sp_configure"allowupdates",1

2>go(2)把要删除的用户数据库置为"suspect"状态

1>usemaster

2>go

1>begintran

2>go

1>updatesysdatabasessetstatus=256

2>wherename="pubs2"

3>go如果得到(1rowaffected),则则commit提交，否则rollback(3)重启server,并用isql以sa注册。(4)删除数据库

1>dbccdbrepair(pubs2,dropdb)

2>go(5)恢复允许修改系统表

1>sp_configure"allowupdates",0

2>go

数据一致性检查1数据库一致性检查(dbcc)提供了一些命令用于检查数据库的逻辑和物理一致性。Dbcc主要有两个功能：使用checkstorage或checktable及checkdb在页一级和行一级检查页链及数据指针。

使用checkstorage,checkalloc,或checkverify,tablealloc,及indexalloc检查页分配。

在下列情况中需要使用dbcc命令：作为数据库日常维护工作的一部