网络数据库_04数据库的维护操作

1、回回顾顾 数据库的 对象集合 主文件 .mdf 辅文件 .ndf 辅文件 .ndf 日志文件 .ldf PRIMARY组 （主文件一定 要属于该组） 用户命名的组 不属于任何组 辅文件 .ndf 图： 数据库的文件及其组的关系 Optimizing the Database Using Filegroups with Hardware-based RAIDFilegroupFileEFileFFileGFileHFileAFileBFileCFileDTransaction LogTransaction LogOperating System建立数据库的例子1n建立一个“订货”数据库，数据主文

2、件的初始大小为10MB，最大为50MB，增量为5MB；日志文件的初始大小为5MB，最大为25MB，增量为5MB。 CREATE DATABASE 订货ON ( NAME = order_dat,FILENAME = c:mssqldataorderdat.mdf,SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 )LOG ON( NAME = order_log,FILENAME = d:mssqllogorderlog.ldf,SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB ) 建立数据库的例子2n建立一个“订货管理”数据库，数

3、据主文件的初始大小为10MB，最大为50MB，增量为5MB；另外建立一个数据次文件初始大小也为10MB，最大为50MB，增量为5MB；日志文件的初始大小为5MB，最大为25MB，增量为5MB。 CREATE DATABASE 订货管理ON PRIMARY( NAME = orderman_dat,FILENAME = c:mssqldataorderman.mdf,SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 ),FILEGROUP 仓库 ( NAME = warehouse,FILENAME = e:mssqldatawh.ndf,SIZE = 10,MAXSI

4、ZE = 50,FILEGROWTH = 5 )LOG ON( NAME = orderman_log,FILENAME = d:mssqllogorderman.ldf,SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB )使用使用Transact-SQL命令增加数据库空间命令增加数据库空间n在查询分析器中，通过在查询分析器中，通过Transact-SQL命令增加数据库命令增加数据库空间的命令语句格式如下。空间的命令语句格式如下。nALTER DATABASE 数据库名数据库名nMODIFY FILEn(NAME=逻辑文件名，逻辑文件名，nSIZE=文件大小

5、，文件大小，nMAXSIZE=增长限制增长限制n)更改数据库例子n”Book“数据库中添加一个数据文件“book_data2”,并指定起初始大小为3mb,最大大小不受限制,设置增长为15%更改数据库例子nALTER DATABASE BOOKnADD FILEn(NAME=BOOK_DATA2nFILENAME=C:PROGRAMFILESSQLSERVERBOOK_DATA2.MDFnSIZE=3MBnMAXSIZE=UNLIMITED,nFILEGROWTH=15%n)更改数据库n增加一个”third”文件组,并为该文件组添加两个数据文件,数据文件名为BOOK_DATA3,BOOK_DAT

6、A4更改数据库例子nALTER DATABASE BOOKnADD FILEGROUP THIRDnGOnALTER DATABASE BOOKnADD FILE(nNAME=BOOK_DATA3,nFILENAME=C:.BOOK_DATA3.NDFn)n(NAME=BOOK_DATA4,nFILENAME=C:.BOOK_DATA4.NDFn)nTO FILEGROUP THIRD更改数据库例子n删除third文件组 必须先删除其中的数据文件.更改数据库例子nALTER DATABASE BOOKnREMOVE FILE BOOK_DATA3nGOnALTER DATABASE BOOKn

7、REMOVE FILE BOOK_DATA4nGOnALTER DATABASE BOOKnREMOVE FILEGROUP THIRD更改数据库例子n添加一个日志文件book_log1nALTER DATABASE BOOKnADD LOG FILEn(NAME=BOOK_LOG1,nFILENAME=C:.BOOK_LOG1.LDFnSIZE=5MBnFILEGROWTH=6MBn)nALTER DATABASE BOOKnREMOVE FILE BOOK_LOG1每次只能修改数据文件的一个属性nALTER DATABASE BOOKnMODIFY FILE n(NAME=BOOK_DAT

8、A2,nSIZE=8MBn)nALTER DATABASE BOOKnMODIFY FILE n(NAME=BOOK_DATA2,nMAXSIZE=120MBMBn)查看数据库状态信息查看数据库状态信息 要确认数据库的当前状态，通过要确认数据库的当前状态，通过“数据库属性数据库属性”窗口窗口的的“常规常规”选项卡查看数据库属性以外，还可以选选项卡查看数据库属性以外，还可以选择择 sys.databases 目录视图中的目录视图中的 state_desc 列。列。在查询设计器窗口中输入如下代码并执行，如图所在查询设计器窗口中输入如下代码并执行，如图所示。示。 Select name,state,

9、state_desc From sys.databases查看数据库状态信息查看数据库状态信息 可以在查询设计器窗口中输入如下代码并执行，可以在查询设计器窗口中输入如下代码并执行，即可查看到相关数据文件的状态信息。即可查看到相关数据文件的状态信息。Select name,physical_name,type,type_desc,state, state_descFrom sys.master_file 查看数据库状态信息查看数据库状态信息 1. 数据库状态含义数据库状态含义nONLINE表示可以对数据库进行访问。表示可以对数据库进行访问。nOFFLINE表示数据库无法使用。表示数据库无法使用。

10、nRESTORING表示正在还原主文件组的一个或多个表示正在还原主文件组的一个或多个文件，或正在离线还原一个或多个辅助文件，此时文件，或正在离线还原一个或多个辅助文件，此时数据库不可用。数据库不可用。nRECOVERING表示正在恢复数据库。表示正在恢复数据库。查看数据库状态信息查看数据库状态信息 1. 数据库状态含义数据库状态含义nRECOVERY PENDING表示表示SQL Server 在恢复过在恢复过程中遇到了与资源相关的错误，数据库未损坏，但程中遇到了与资源相关的错误，数据库未损坏，但是可能缺少文件，或系统资源限制可能导致无法启是可能缺少文件，或系统资源限制可能导致无法启动数据库。

11、动数据库。nSUSPECT表示至少主文件组可疑或可能已损坏。表示至少主文件组可疑或可能已损坏。nEMERGENCY表示用户更改了数据库，并将其状态表示用户更改了数据库，并将其状态设置为设置为 EMERGENCY。查看数据库状态信息查看数据库状态信息 2. 数据库文件状态含义数据库文件状态含义nONLINE表示文件可用于所有操作。表示文件可用于所有操作。nOFFLINE表示文件不可访问，并且可能不显示在磁表示文件不可访问，并且可能不显示在磁盘中。盘中。nRESTORING表示正在还原文件。表示正在还原文件。nRECOVERY PENDING表示文件恢复被推迟。表示文件恢复被推迟。nSUSPECT

12、表示在线还原过程中，恢复文件失败。表示在线还原过程中，恢复文件失败。nDEFUNCT表示当文件不处于在线状态时被删除。表示当文件不处于在线状态时被删除。数据库的属性设置数据库的属性设置2. 限制用户对数据库的访问限制用户对数据库的访问在在SQL Server 2005 的运行过程中，有时需要限制用的运行过程中，有时需要限制用户的访问，例如，管理员要维护数据库，系统需要户的访问，例如，管理员要维护数据库，系统需要升级等。这时可以设置限定只能由特定用户访问数升级等。这时可以设置限定只能由特定用户访问数据库据库。数据库的属性设置数据库的属性设置2. 限制用户对数据库的访问限制用户对数据库的访问在在S

13、QL Server 2005 的运行过程中，有时需要限制用的运行过程中，有时需要限制用户的访问，例如，管理员要维护数据库，系统需要户的访问，例如，管理员要维护数据库，系统需要升级等。这时可以设置限定只能由特定用户访问数升级等。这时可以设置限定只能由特定用户访问数据库。据库。n在数据库在数据库test01的的“数据库属性数据库属性”对话框中选择对话框中选择“选项选项”选项卡，如图所示。选项卡，如图所示。n选择选择“状态状态”|“限制访问限制访问”下拉框，出现下拉框，出现3个选项：个选项：nMultiple： 数据库处于正常生产状态，允数据库处于正常生产状态，允许多个用户同时访问数据库。许多个用户

14、同时访问数据库。nSingle：指定一次只能一个用户访问。：指定一次只能一个用户访问。nRestricted：限制除：限制除db_ower（数据库所有（数据库所有者）、者）、 dbcreator （数据库创建者）和（数据库创建者）和sysadmin（系统管理员）以外的角色成员（系统管理员）以外的角色成员访问数据库。访问数据库。数据库的属性设置数据库的属性设置 3修改数据库的排序规则修改数据库的排序规则（1）了解排序规则的含义。以）了解排序规则的含义。以Chinese_PRC_CS_AI_WS为例，为例，该规则可以分成两部分来理解。前半部分指排序规则所支持的字该规则可以分成两部分来理解。前半部分

15、指排序规则所支持的字符集，如符集，如Chinese_PRC_表示对中国大陆地区简体字表示对中国大陆地区简体字UNICODE的排序规则，而后半部分常见的组合的含义如下。的排序规则，而后半部分常见的组合的含义如下。n_BIN：二进制排序。：二进制排序。n_CI(CS)：是否区分大小写，：是否区分大小写，CI不区分，不区分，CS区分。区分。n_AI(AS)：是否区分重音，：是否区分重音，AI不区分，不区分，AS区分。区分。n_KI(KS)：是否区分假名类型，：是否区分假名类型，KI不区分，不区分，KS区分。区分。n_WI(WS)：是否区分宽度，：是否区分宽度，WI不区分，不区分，WS区分。区分。数据

16、库的属性设置数据库的属性设置 3修改数据库的排序规则修改数据库的排序规则（2）排序规则的层次。）排序规则的层次。 SQL Server 2005 的排序规的排序规则分为则分为3个层次：服务器排序规则、数据库排序规则个层次：服务器排序规则、数据库排序规则和表的排序规则。和表的排序规则。n当排序规则在层次之间发生冲突时，以低层次、细当排序规则在层次之间发生冲突时，以低层次、细粒度为准。假如服务器的排序规则和数据库的排序粒度为准。假如服务器的排序规则和数据库的排序规则不一致，在数据库中自然以数据库的排序规则规则不一致，在数据库中自然以数据库的排序规则为准。为准。数据库的属性设置数据库的属性设置4更改

17、数据库所有者更改数据库所有者（1）在数据库属性窗体中选择）在数据库属性窗体中选择“文件文件”选项卡，选项卡，然后单击然后单击“所有者所有者”文本框后面的文本框后面的按钮，则按钮，则会弹出会弹出“选择数据库所有者选择数据库所有者”对话框。对话框。（2）单击）单击“浏览浏览”按钮，则会弹出按钮，则会弹出“查找对象查找对象”对话框。对话框。（3）在）在“匹配的对象匹配的对象”列表框中选择数据库所列表框中选择数据库所有者，单击有者，单击“”“”按钮即可实现更改数据库所有按钮即可实现更改数据库所有者的操作。者的操作。设置nANSI 默认设置：n递归触发器：选取此复选框后，则允许触发器递归触发。n残缺页检

18、测：选取此复选框后，则允许系统检测不完整的页面。n自动更新统计信息：选取此复选框后，当数据表中的数据变化时，SQL Server自动更新已经存在的统计信息，否则不自动更新。n自动关闭：选取此复选框后，则当一个用户退出SQL Server时将自动关闭数据库，并释放其秘占用的所有资源。n自动收缩：选取此复选框后，则当数据库文件中未使用空间超过文件长度的25%，SQL Server将要自动缩小数据库文件的长度。n自动创建统计信息：选取此复选框后，SQL Server自动创建索引列值分布的统计信息；否则，不创建索引列值分布的统计信息。n使用被引用的标识符：选取此复选框后，说明双引号可以用做标识符的定界

19、符；否则，不允许使用双引号做标识符的定界符。使用Transact-SQL命令查看和设置数据库选项n在查询分析器中，使用Transact-SQL命令修改数据库选项的语法格式如下。nsp_dboption 数据库名，选项名， TRUE | FALSE 查看数据库的信息n选择要执行操作的数据库选择要执行操作的数据库qUSE databasen使用系统存储过程查看数据库的信息使用系统存储过程查看数据库的信息qSp_helpdb db_nameqSp_databasesqSp_helpfile file_nameqSp_spacesused估算数据库大小估算数据库大小1影响数据库大小的因素：影响数据库大

20、小的因素：要估计数据库的大小，需要考要估计数据库的大小，需要考虑如下一些因素。虑如下一些因素。nmodel数据库和系统表的大小，包括预测到的增长。数据库和系统表的大小，包括预测到的增长。n表中数据的总量，包括预测到的增长。表中数据的总量，包括预测到的增长。n索引的数量和大小，特别是键值的大小、行的数量和填充因子的索引的数量和大小，特别是键值的大小、行的数量和填充因子的设置。设置。n影响事务日志大小的因素，更改活动的总量和频率，每一个事务影响事务日志大小的因素，更改活动的总量和频率，每一个事务的大小以及备份日志的频率。的大小以及备份日志的频率。n系统表的大小。例如用户和对象的数量等。系统表的大小

21、。例如用户和对象的数量等。估算数据库大小估算数据库大小2估计表中数据的总量：估计表中数据的总量：估计表所需的页数和表占用估计表所需的页数和表占用的磁盘空间。具体可以采用如下方法。的磁盘空间。具体可以采用如下方法。n通过统计每列包含的字节数，计算通过统计每列包含的字节数，计算1行的字节数量。行的字节数量。对于列中定义为可变长度，可以采用取平均值的方对于列中定义为可变长度，可以采用取平均值的方法估算。法估算。n确定平均每一个数据页包含行的数目。即用确定平均每一个数据页包含行的数目。即用8060除除以以1行的字节数，取整即可得到结果。行的字节数，取整即可得到结果。n表中行的近似数目除以一个数据页包含

22、的行数，结表中行的近似数目除以一个数据页包含的行数，结果就是需要存储到表中的页数果就是需要存储到表中的页数 。 数据文件 数据文件的数据v数据库的文件数据库的文件 以标准的8KB（页面，Page）为基本单位。8个连续的页面构成一个区（Extent）。 数据页面头部：96 字节 行偏移数组： 每个记录占据2 字节 数据区 Slot 0 Slot 1 日志文件 日志文件的数据v数据库的文件数据库的文件 大小不规则的日志记录（Log Record）组成。 （1）数据库存储空间相关概念v数据库的存储空间数据库的存储空间 文件：数据文件和日志文件 文件组：数据文件的管理单位，延伸空间、比例填充 堆：没有

23、建立索引的表 B+树：索引 区间：存储空间的分配单位 页面：存储空间的基本组成单位 槽：页面中的标准“存储格子” 日志记录：不规则大小的日志条目 （2）数据库存储空间的组成 数据库总存储空间=所有的数据文件+日志文件空间 数据库可用空间=所有的数据文件可用空间+日志文件可用空间吗？ 数据库总空间=已经分配的空间+尚未分配的空间 数据库可用空间=尚未分配的空间+尚未使用的空间 数据库可以收缩的空间=尚未分配的空间v数据库的存储空间数据库的存储空间 已经使用的空间+尚未使用的空间数据文件存储空间v数据库的存储空间数据库的存储空间 已经分配尚未分配已经使用尚未使用数据使用管理使用执行DBCC SHO

24、WFILESTATS查询SYS.Allocation_Units收缩数据库收缩数据库1设置自动收缩数据库设置自动收缩数据库n设置数据库的自动收缩，可以在数据库的属性中设置数据库的自动收缩，可以在数据库的属性中“选项选项”选项卡页面中设置，只要将选项中的选项卡页面中设置，只要将选项中的“自自动收缩动收缩”设为设为True即可。即可。2手动收缩数据库手动收缩数据库:步骤。步骤。（1）在）在SQL Server Management Studio中，右击相应的数据库，中，右击相应的数据库，如如test01，从弹出的快捷菜单中依次选择，从弹出的快捷菜单中依次选择“任务任务”|“收缩收缩”|“数数据库据

25、库”。（2）在弹出的对话框中进行设置，如图所示。）在弹出的对话框中进行设置，如图所示。收缩数据库收缩数据库2手动收缩数据库手动收缩数据库:步骤。步骤。（3）如果单击）如果单击“脚本脚本”按钮，系统还能够将收按钮，系统还能够将收缩操作的脚本显示到缩操作的脚本显示到“新建查询新建查询”界面中，界面中，结果如下。结果如下。USE test01GODBCC SHRINKDATABASE(Ntest01, 45 )GO收缩数据库收缩数据库3手动收缩数据库文件手动收缩数据库文件（1）在）在SQL Server Management Studio中，右击相中，右击相应的数据库，如应的数据库，如test01，

26、从弹出的快捷菜单中选择，从弹出的快捷菜单中选择“任务任务”|“收缩收缩”|“文件文件”。（2）在弹出的对话框中进行设置，如图所示。数据库）在弹出的对话框中进行设置，如图所示。数据库test01的数据文件当前分配空间为的数据文件当前分配空间为15MB，设置收缩，设置收缩数据库文件参数，单击数据库文件参数，单击“确定确定”按钮，即可完成操按钮，即可完成操作。作。分离和附加用户数据库分离和附加用户数据库 1分离用户数据库分离用户数据库（1）在）在SQL Server Management Studio中，右击相中，右击相应的数据库，如应的数据库，如test02，从弹出的快捷菜单中依次，从弹出的快捷菜

27、单中依次选择选择“任务任务”|“分离分离”。（2）在弹出的对话框中进行设置，如图所示。设置数）在弹出的对话框中进行设置，如图所示。设置数据库据库test02的分离参数，单击的分离参数，单击“确定确定”按钮，即可按钮，即可完成操作。完成操作。其中的主要参数项含义如下。n删除连接：是否断开与指定服务器的连接。n更新统计信息：选择在分离数据库之前是否更新过时的优化统计信息。n保留全文目录：选择是否保留与数据库相关联的所有全文目录。n状态：显示数据库分离前是否“就绪”或“未就绪”。分离和附加用户数据库分离和附加用户数据库 2附加数据库附加数据库（1）右击）右击“对象资源管理器对象资源管理器”中中“数据

28、库数据库”，从弹出的快捷菜单，从弹出的快捷菜单中选择中选择 “附加附加”命令。命令。（2）在弹出的）在弹出的“附加数据库附加数据库”对话框中，单击对话框中，单击“添加添加”按钮。在按钮。在弹出的弹出的“数据库定位文件数据库定位文件”界面中，选择要添加的数据库的主数界面中，选择要添加的数据库的主数据文件，如图所示。数据库据文件，如图所示。数据库test02的主数据文件为的主数据文件为test02.mdf。（3）单击）单击“确定确定”按钮，返回按钮，返回“附加数据库附加数据库”对话框。单击对话框。单击“确确定定”按钮，数据库按钮，数据库test02就附加到当前的实例中了。就附加到当前的实例中了。联

29、机和脱机用户数据库联机和脱机用户数据库 1联机用户数据库。联机用户数据库。（1）在）在SQL Server Management Studio中，右击已中，右击已经脱机的数据库，从弹出的快捷菜单中依次选择经脱机的数据库，从弹出的快捷菜单中依次选择“任任务务”|“联机联机”。弹出如图所示的对话框。弹出如图所示的对话框。（2）完成联机过程后，单击）完成联机过程后，单击“关闭关闭”按钮，系统中将按钮，系统中将数据库恢复原样。数据库恢复原样。联机和脱机用户数据库联机和脱机用户数据库 2脱机用户数据库。脱机用户数据库。（1）在）在SQL Server Management Studio中，右击相中，右击

30、相应的数据库，如应的数据库，如test02，从弹出的快捷菜单中依次选，从弹出的快捷菜单中依次选择择“任务任务”|“脱机脱机”。弹出如图所示的对话框。弹出如图所示的对话框。（2）完成脱机过程后，单击）完成脱机过程后，单击“关闭关闭”按钮。系统中将按钮。系统中将数据库标注为。数据库标注为。 文件组是指将数据库相关的一组磁盘文件组成的集合。 1文件组的类型（1）主文件组： 主文件组包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。 （2）用户定义文件组：用户指定的任何文件组。n一个文件不可以是多个文件组的成员。表、索引和大型对象数据可以与指定的文件组相关联，它

31、们的所有页将被分配到该文件组。2创建文件组的必要性（1）对于大型数据库，如果硬件设置上需要多个磁盘驱动器，就可以把特定的对象或文件分配到不同的磁盘上，将数据库文件组织成用户文件组。（2）文件组可以帮助数据库管理人员执行相应的数据布局，以及某些管理任务。（3）利用文件组，可以在特定的文件中定位特定的对象，从而将频繁查询和频繁修改的文件分离出来，以提高磁盘驱动器的效率。（4）通过创建用户文件组，可以将数据文件集合起来，以便于管理、数据分配和放置。 3创建用户文件组（1）在SQL Server Management Studio中创建用户文件组。具体步骤如下： 在SQL Server Management Studio中，右击“对象资源管理器”|“数据库”|test01，从弹出的快捷菜单中选择 “属性”命令。 在弹出的“数据库属性”对话框中选择“文件组”选项卡，然后单击“添加”按钮。 在PRIMARY组后添加一个新的文件