第4章管理数据库

第4章管理数据库一、SqlServer数据库的文件构成一个数据库可以由多个事务日志文件和多个数据文件构成，至少应由一个事务日志文件和一个数据文件构成。1.数据文件存储数据和各种对象以及数据的结构。主数据文件：*.mdf,本数据库的系统表都保存在主数据文件中，只能有一个次数据文件:*.ndf，可以没有或有多个次要数据文件注意：（1）主数据文件是一个数据库必须具备的数据文件，次数据文件可根据数据库数据的复杂程度创建或不创建。（2）主数据文件只有一个(?)，在创建数据库时同时创建，次数据可有多个，创建数据库的同时或以后均可创建（3）同一数据库的不同数据文件可以分别创建在不同的磁盘上。（4）同一个数据库中的对象是存储在不同的数据文件中的，对一个数据库的操作，实际上是在不同的数据文件上进行。2．事务（ACID）日志文件记录了每一个事务的开始、对数据的改变和修改等信息，随着数据库的操作，事务日志不断增加，是数据库进行恢复的重要依据。*.ldf，一个数据库可以有多个。注意：sqlserver遵循先写日志后修改数据库的原则，所以如果日志文件空间被占满，将导致无法对数据库进行操作。3、数据库文件组数据文件的逻辑集合，是对数据文件进行分类管理的逻辑单位，方便数据的管理与分配，一个数据文件只能属于一个文件组。(1).文件组的分类主文件组：primary，主数据文件默认属于主文件组用户定义的文件组：由用户命名定义的文件组默认文件组:数据文件在不指定文件组归属时，默认所属于的文件组(2).文件组与数据库对象的关系SqlServer通过创建文件组的方式把表、索引等数据库对象创建在某个指定的数据文件上。Createtabletablename()onfilegroup|defaultfilegroup（不能直接指定数据文件）也可在表的primarykey和unique约束中使用，否则索引和表创建在一个文件组中(3).使用文件组和数据文件的建议（1）尽可能把数据文件分散到不同的物理磁盘驱动器中（2）如果有用户文件组，可以把默认文件组设为用户定义的文件组，从而防止用户数据在primary文件组上的增长。（3）把数据文件和日志文件分散到不同的物理磁盘驱动器中，且把日志文件放到查询量比较轻的磁盘驱动器上（4）如果文件组中的某个数据文件遭到破坏，整个文件组中的数据都无法再使用。二、数据库的创建以下示例在SQLServer实例上创建了一个数据库。该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件。主数据文件在主文件组中，而用户定义文件组包含两个次要数据文件。ALTERDATABASE语句将用户定义文件组指定为默认文件组。然后通过指定用户定义文件组来创建表。USEmaster;GO--Createthedatabasewiththedefaultdata--filegroupandalogfile.Specifythe--growthincrementandthemaxsizeforthe--primarydatafile.CREATEDATABASEMyDBONPRIMARY(NAME='MyDB_Primary',--逻辑文件名FILENAME='c:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\data\MyDB_Prm.mdf',--物理文件名SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MB),FILEGROUPMyDB_FG1(NAME='MyDB_FG1_Dat1',FILENAME='c:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\data\MyDB_FG1_1.ndf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=10%),(NAME='MyDB_FG1_Dat2',FILENAME='c:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\data\MyDB_FG1_2.ndf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOGON(NAME='MyDB_log',FILENAME='c:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\data\MyDB.ldf',SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=1MB);GOALTERDATABASEMyDBMODIFYFILEGROUPMyDB_FG1DEFAULT;GO--Createatableintheuser-definedfilegroup.USEMyDB;goCREATETABLEMyTable(colaintPRIMARYKEY,colbchar(8))ONMyDB_FG1;GO参考联机帮助——CREATEDATABASE(Transact-SQL)三、物理存储结构（估算数据库大小）SQLServer中数据存储的基本单位是页，8k。为数据库中的数据文件（.mdf或.ndf）分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号）。磁盘I/O操作在页级执行。也就是说，SQLServer读取或写入所有数据页。页：和事务日志文件无关，是SQLServer中数据存储的基本单位。sqlserver可以管理的最小物理空间，8k,实际存储空间为8060字节。表中一行数据不能跨页存放，意味着一行数据的最大值为8060字节。示例：表中一行数据3500字节，10000行数据，需要占用多少存储空间?（8k*5000）区（盘区）：8个连续的页，是空间分配的最小单元，区是八个物理上连续的页的集合，用来有效地管理页。所有页都存储在区中参考联机帮助——物理数据库体系结构四、查看数据库信息(1)使用managementstudio查看并修改数据库属性（选项）(2)使用系统存储过程、目录视图如:sp_spaceused，sp_helpdb五、数据库的修改（ALTERDATABASE）（1）数据库改名（modifyname）（2）更改数据文件或日志文件大小,位置(modifyfile)（3）增加数据文件或日志文件(addfile/addlogfile)（4）移除数据文件或日志文件(removefile)（5）修改默认文件组（modifyfilegroup）(6)删除文件组（removefilegroup）(7)收缩数据库(不使用alterdatabse语句，使用dbcc数据库维护命令中的dbccshinkdatabase或dbccshinkfile语句)（8）设置数据库选项数据库总是处于某个特定的状态中，每一个数据库选项可以用ALTERDATEBASE语句中的SET子句来设置。设置数据库的选项应该在master数据库中执行。使用sp_configure存储过程可以设置服务器级别的选项，使用ALTERDATEBASE语句可以设置数据库级别的选项，使用SET语句设置的选项只能影响当前用户的会话。以下示例将数据库设置为SINGLE_USER模式，以获得独占访问权。然后，该示例将AdventureWorks数据库的状态设置为READ_ONLY，然后将对数据库的访问权返回给所有用户。USEmaster;GOALTERDATABASEAdventureWorksSETSINGLE_USERWITHROLLBACKIMMEDIATE;GOALTERDATABASEAdventureWorksSETREAD_ONLY;GOALTERDATABASEAdventureWorksSETMULTI_USER;GO参考联机帮助——设置数据库选项SET(Transact-SQL)六、优化数据库和数据库文件1．放置数据文件和日志文件尽可能把数据文件分散在不同的磁盘驱动器上把数据文件和日志文件分散在不同的物理磁盘驱动器中好处：允许系统执行并行操作，从而提高系统使用数据的效率2.使用文件组表和索引应分布在不同的文件组中，提高查询语句效率。把默认文件组改成用户定义的文件组，以防止用户数据在primary文件组上的增长。可备份或恢复单个文件或文件组。3.使用RAID技术(RedundantArrayofIndependentDisks)冗余磁盘阵列是一种磁盘系统，多个磁盘驱动器合成一个磁盘阵列。提高数据存储的性能、可靠性。分为6个等级RAID0到RAID5。每一种提供不同的算法提高系统的性能。七、附加与分离数据库可以分离数据库的数据和事务日志文件，然后将它们重新附加到同一或其他SQLServer实例。如果要将数据库更改到同一计算机的不同SQLServer实例或要移动数据库，分离和附加数据库会很有用。分离数据库：分离数据库是指将数据库从SQLServer实例中删除，但是数据库在其数据文件和事务日志文件中保持不变。（1）使用系统存储过程EXECsp_detach_db'mydb','true'附加数据库：附加数据库时，所有数据文件（MDF文件和NDF文件）都必须可用。EXECsp_attach_db'mydb','c:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\data\MyDB_Prm.mdf’,'c:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\mydb.ldf';（2）使用createdatabase语句（3）使用对象资源管理器修改数据库示例注意：示例中所列物理文件位置应根据实验本机环境进行调整。A.向数据库中添加文件以下示例将一个5MB的数据文件添加到AdventureWorks数据库。USEmaster;GO--GettheSQLServerdatapathALTERDATABASEAdventureWorksADDFILE(NAME=Test1dat2,FILENAME='d:\sqlserver\t1dat2.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)GOB.向数据库中添加由两个文件组成的文件组以下示例在AdventureWorks数据库中创建文件组Test1FG1，然后将两个5MB的文件添加到该文件组。USEmasterGOALTERDATABASEAdventureWorksADDFILEGROUPTest1FG1;GOALTERDATABASEAdventureWorksADDFILE(NAME=test1dat3,FILENAME='d:\sqlserver\t1dat3.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)(NAME=test1dat4,FILENAME='d:\sqlserver\t1dat4.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)TOFILEGROUPTest1FG1GOC.向数据库中添加两个日志文件以下示例向AdventureWorks数据库中添加两个5MB的日志文件。USEmaster;GO--ALTERDATABASEAdventureWorksADDLOGFILE(NAME=test1log2,FILENAME='d:\sqlserver\test2log.ldf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB),(NAME=test1log3,FILENAME='d:\sqlserver\test3log.ldf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)GOD.从数据库中删除文件以下示例删除示例B中添加的一个文件。USEmaster;GOALTERDATABASEAdventureWorksREMOVEFILEtest1dat4;GOE.修改文件以下示例增加示例B中添加的一个文件的大小。USEmaster;G