第4章管理数据库

1、第4章管理数据库 一、Sql Server数据库的文件构成一个数据库可以由多个事务日志文件和多个数据文件构成，至少应由一个事务日志文件和一个数据文件构成。1. 数据文件存储数据和各种对象以及数据的结构。主数据文件：*.mdf,本数据库的系统表都保存在主数据文件中，只能有一个 次数据文件:*.ndf，可以没有或有多个次要数据文件 注意：（1）主数据文件是一个数据库必须具备的数据文件，次数据文件可根据数据库数据的复杂程度创建或不创建。（2）主数据文件只有一个(?)，在创建数据库时同时创建，次数据可有多个，创建数据库的同时或以后均可创建（3）同一数据库的不同数据文件可以分别创建在不同的磁盘上。（4）

2、同一个数据库中的对象是存储在不同的数据文件中的，对一个数据库的操作，实际上是在不同的数据文件上进行。2事务（ACID）日志文件记录了每一个事务的开始、对数据的改变和修改等信息，随着数据库的操作，事务日志不断增加，是数据库进行恢复的重要依据。*.ldf，一个数据库可以有多个。注意：sql server遵循先写日志后修改数据库的原则，所以如果日志文件空间被占满，将导致无法对数据库进行操作。3、数据库文件组 数据文件的逻辑集合，是对数据文件进行分类管理的逻辑单位，方便数据的管理与分配，一个数据文件只能属于一个文件组。(1).文件组的分类主文件组：primary ，主数据文件默认属于主文件组 用户定义

3、的文件组：由用户命名定义的文件组 默认文件组:数据文件在不指定文件组归属时，默认所属于的文件组(2). 文件组与数据库对象的关系 Sql Server通过创建文件组的方式把表、索引等数据库对象创建在某个指定的数据文件上。 Create table tablename ( ) on filegroup|defaultfilegroup（不能直接指定数据文件） 也可在表的primary key和 unique约束中使用，否则索引和表创建在一个文件组中(3).使用文件组和数据文件的建议（1）尽可能把数据文件分散到不同的物理磁盘驱动器中（2）如果有用户文件组，可以把默认文件组设为用户定义的文件组，从而

4、防止用户数据在primary 文件组上的增长。（3）把数据文件和日志文件分散到不同的物理磁盘驱动器中，且把日志文件放到查询量比较轻的磁盘驱动器上（4）如果文件组中的某个数据文件遭到破坏，整个文件组中的数据都无法再使用。二、数据库的创建以下示例在 SQL Server 实例上创建了一个数据库。该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件。主数据文件在主文件组中，而用户定义文件组包含两个次要数据文件。ALTER DATABASE 语句将用户定义文件组指定为默认文件组。然后通过指定用户定义文件组来创建表。USE master;GO- Create the database with

5、the default data- filegroup and a log file. Specify the- growth increment and the max size for the- primary data file.CREATE DATABASE MyDBON PRIMARY ( NAME=MyDB_Primary, -逻辑文件名FILENAME=c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataMyDB_Prm.mdf, -物理文件名 SIZE=4MB, MAXSIZE=10MB, FILEGROWTH=1MB),FIL

6、EGROUP MyDB_FG1 ( NAME = MyDB_FG1_Dat1, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataMyDB_FG1_1.ndf, SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=10%), ( NAME = MyDB_FG1_Dat2, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataMyDB_FG1_2.ndf, SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=

7、1MB)LOG ON ( NAME=MyDB_log, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataMyDB.ldf, SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB);GOALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULT;GO- Create a table in the user-defined filegroup.USE MyDB;goCREATE TABLE MyTable ( cola int PRIMARY KEY,

8、 colb char(8) )ON MyDB_FG1;GO参考联机帮助CREATE DATABASE (Transact-SQL)三、物理存储结构（估算数据库大小）SQL Server 中数据存储的基本单位是页，8k。为数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行。也就是说，SQL Server 读取或写入所有数据页。页：和事务日志文件无关，是SQL Server 中数据存储的基本单位。sql server可以管理的最小物理空间，8k,实际存储空间为8060字节。表中一行数据不能跨页存放，意味着一行数据

9、的最大值为8060字节。 示例：表中一行数据3500字节，10000行数据，需要占用多少存储空间?（8k*5000）区（盘区）：8个连续的页，是空间分配的最小单元，区是八个物理上连续的页的集合，用来有效地管理页。所有页都存储在区中参考联机帮助物理数据库体系结构四、查看数据库信息(1)使用management studio 查看并修改数据库属性（选项）(2)使用系统存储过程、目录视图 如:sp_spaceused ，sp_helpdb 五、数据库的修改（ALTER DATABASE）（1）数据库改名 （modify name）（2）更改数据文件或日志文件大小,位置(modify file)（3）

10、增加数据文件或日志文件(add file/add log file)（4）移除数据文件或日志文件(remove file)（5）修改默认文件组（modify filegroup） (6) 删除文件组（remove filegroup）(7) 收缩数据库 (不使用alter databse 语句，使用dbcc数据库维护命令中的dbcc shinkdatabase 或dbcc shinkfile语句)（8）设置数据库选项数据库总是处于某个特定的状态中，每一个数据库选项可以用ALTER DATEBASE 语句中的SET子句来设置。设置数据库的选项应该在master数据库中执行。使用sp_config

11、ure存储过程可以设置服务器级别的选项，使用ALTER DATEBASE语句可以设置数据库级别的选项，使用SET语句设置的选项只能影响当前用户的会话。以下示例将数据库设置为 SINGLE_USER 模式，以获得独占访问权。然后，该示例将 AdventureWorks 数据库的状态设置为 READ_ONLY，然后将对数据库的访问权返回给所有用户。USE master;GOALTER DATABASE AdventureWorksSET SINGLE_USER WITH ROLLBACK IMMEDIATE;GOALTER DATABASE AdventureWorksSET READ_ONLY;

12、GOALTER DATABASE AdventureWorksSET MULTI_USER;GO参考联机帮助设置数据库选项 SET (Transact-SQL) 六、优化数据库和数据库文件1放置数据文件和日志文件尽可能把数据文件分散在不同的磁盘驱动器上 把数据文件和日志文件分散在不同的物理磁盘驱动器中 好处：允许系统执行并行操作，从而提高系统使用数据的效率2.使用文件组表和索引应分布在不同的文件组中，提高查询语句效率。把默认文件组改成用户定义的文件组，以防止用户数据在primary文件组上的增长。可备份或恢复单个文件或文件组。3.使用RAID技术(Redundant Array of Inde

13、pendent Disks)冗余磁盘阵列是一种磁盘系统，多个磁盘驱动器合成一个磁盘阵列。提高数据存储的性能、可靠性。分为6个等级RAID0到RAID5。每一种提供不同的算法提高系统的性能。七、附加与分离数据库可以分离数据库的数据和事务日志文件，然后将它们重新附加到同一或其他 SQL Server 实例。如果要将数据库更改到同一计算机的不同 SQL Server 实例或要移动数据库，分离和附加数据库会很有用。分离数据库：分离数据库是指将数据库从 SQL Server 实例中删除，但是数据库在其数据文件和事务日志文件中保持不变。（1）使用系统存储过程EXEC sp_detach_db mydb,

14、true 附加数据库：附加数据库时，所有数据文件（MDF 文件和 NDF 文件）都必须可用。EXEC sp_attach_db mydb, c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLdataMyDB_Prm.mdf, c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatamydb.ldf ;（2）使用create database 语句（3）使用对象资源管理器修改数据库示例注意：示例中所列物理文件位置应根据实验本机环境进行调整。A. 向数据库中添加文件以下示例将一个 5MB 的数据文件添加到 A

15、dventureWorks 数据库。USE master;GO- Get the SQL Server data pathALTER DATABASE AdventureWorks ADD FILE ( NAME = Test1dat2, FILENAME = d:sqlservert1dat2.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)GOB. 向数据库中添加由两个文件组成的文件组以下示例在 AdventureWorks 数据库中创建文件组 Test1FG1，然后将两个 5MB 的文件添加到该文件组。 USE masterGOALT

16、ER DATABASE AdventureWorksADD FILEGROUP Test1FG1;GOALTER DATABASE AdventureWorks ADD FILE ( NAME = test1dat3, FILENAME = d:sqlservert1dat3.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)( NAME = test1dat4, FILENAME = d:sqlservert1dat4.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)TO FILEGRO

17、UP Test1FG1GOC. 向数据库中添加两个日志文件以下示例向 AdventureWorks 数据库中添加两个 5 MB 的日志文件。USE master;GO-ALTER DATABASE AdventureWorks ADD LOG FILE ( NAME = test1log2, FILENAME = d:sqlservertest2log.ldf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB),( NAME = test1log3, FILENAME = d:sqlservertest3log.ldf, SIZE = 5MB, MA

18、XSIZE = 100MB, FILEGROWTH = 5MB)GOD. 从数据库中删除文件以下示例删除示例 B 中添加的一个文件。USE master;GOALTER DATABASE AdventureWorksREMOVE FILE test1dat4;GOE. 修改文件以下示例增加示例 B 中添加的一个文件的大小。USE master;GOALTER DATABASE AdventureWorks MODIFY FILE (NAME = test1dat3, SIZE = 20MB);GOF. 将文件移至新位置以下示例将在示例 A 中创建的 Test1dat2 文件移至新目录中。 注意

19、： 必须先将该文件实际移至新目录中，然后才能运行此示例。然后，停止和启动 SQL Server 的实例，或使 AdventureWorks 数据库 OFFLINE 再 ONLINE，以实施更改。 USE master;GOALTER DATABASE AdventureWorksMODIFY FILE( NAME = Test1dat2, FILENAME = Nc:t1dat2.ndf);GOH. 使文件组成为默认文件组以下示例使示例 B 中创建的 Test1FG1 文件组成为默认文件组。然后，默认文件组被重置为 PRIMARY 文件组。请注意，必须使用括号或引号分隔 PRIMARY。USE master;GOALTER DATABASE AdventureWorks MODIFY FILEGROUP Test1FG1 DEFAULT;GOALTER DATABASE Adv