管理数据库

最新推荐文章于 2024-05-10 15:38:55 发布

窝法氦镁烷

最新推荐文章于 2024-05-10 15:38:55 发布

阅读量918

点赞数 2

本文链接：https://blog.csdn.net/AAMahone/article/details/88536647

版权

数据库同时被 3 个专栏收录

13 篇文章 0 订阅

订阅专栏

Microsoft SQL server

13 篇文章 0 订阅

订阅专栏

SQL server

12 篇文章 1 订阅

订阅专栏

概述

数据库是对象和数据的容器,管理数据库就是管理数据库文件.设计数据库时应该考虑以下几个问题:

数据库大小应为多大合理?
数据库后期容量不够需要扩张,如何扩张合适?
相似的,数据库容量需要收缩,如何收缩合适?
数据库运行时应该具有哪些行为特征?
文件读写权限的分配如何进行?
用户权限如何管理?
数据库创建以后,是否应该进行更改?更改针对哪些方面?
如何优化数据库运行性能?

此外还有很多的问题需要考虑,我们下面结合SQL server的特性,来介绍这些方面的知识.

数据库文件和文件组

文件类型

在SQL server中,有两类文件: 数据文件和事务日志文件.

数据文件: 用于存放数据库的数据和各种对象
事务日志文件: 用于存放事务日志.
一个数据库至少有一个数据文件和事务日志文件.

数据文件还可以分为主数据文件和次数据文件.

主数据文件: 是数据库的起点,一个数据库中有且仅有一个主数据文件,扩展名为(mdf).
次数据文件: 是主数据文件的补充,存放不在主数据文件中的数据和对象,有 $\ge$ 0 个,扩展名为(ndf).

什么是事务?

事务是一个单元的工作,一个单元的工作要么全部完成,要么全不完成,事务又称为原子操作.SQL server系统具有事务功能,如: 一系列操作以一个GO结束,上面的操作就构成一个事务,这些操作要么都完成,要么都不完成,一旦有操作失败,则将会恢复到第一条操作执行之前的状态,这个过程称为事务的 "回滚".

事务日志文件在数据库中被称为日志文件,数据库至少有一个日志文件,其扩展名为(ldf).

逻辑文件名

在OS上,数据库是以数据文件和日志文件存放的,每个文件都有对应的路径名和文件名,若要直接在数据库中使用物理文件名不但要记住文件的存放位置,还要记住很长的文件名,就很不方便,也无法做到逻辑特性和物理特性分离.为此数据库为每个文件相应地建立了逻辑文件名,在数据中使用时,直接使用逻辑文件名就可以操作实际文件,虽然不是同名,但是逻辑文件名可以理解为物理文件的一个别名(alias).

文件组

文件组是文件的逻辑集合.将一些文件整合在一个文件组里,方便数据的管理和分配.

例如,同一个文件组中的文件看起来在一起,但实际上极有可能它们各自放在不同磁盘上(这是为了数据库效率的考量,存在不同磁盘中的文件可以并行访问),因此文件组是一个逻辑集合.

数据库对对象的存储是基于文件组的,比如建表时可以指定存放到哪个文件组,但不能指定存放到哪个文件中,若要存放到具体的磁盘文件,应该将该文件单独建立一个文件组.

使用文件和文件组应该考虑的因素:

一个文件或文件组只能用于一个数据库,不可多库共享.
一个文件只能属于一个文件组,不可多组共享.
数据文件和日志文件不能放在同一个文件或文件组中,数据和日志总是分开存放的.
日志文件永远不能是任何文件组中的一部分.

数据库空间管理

SQL server系统中,可管理的最小物理空间是以页为单位的,每一页的大小是8KB,即8192字节.在表中,每一行数据不可跨页存放.在每一页,由于系统占用空间(用于记录该页系统信息),因此表的每一行数据不能超过8060个字节.但是varchar、nvarchar、varbinary等数据类型的表不受限制.

每8个连续的页称为一个区,一个区大小是64K.每1MB的数据有16个区.区用于控制表、索引的存储.SQL server提供了两种类型的区: 统一区和混合区.由单个对象构成的区构成统一区,区中所有内容只能由所属对象使用.由两种及以上对象构成的区称为混合区.

通过理解数据库空间管理,可估算数据库的设计尺寸.数据库大小 = 表大小 + 索引大小 + 其它对象大小.假设数据库只有一个表,该表的数据行字节是800B,因此,一个数据页最多存放10行数据.若该表有100万行数据,那么该表占用10万个数据页的空间.因此数据库大小为: 10⁵ × 8KB = 781.25MB.再考虑其他因素,就可得到数据库的设计值.

定义数据库

创建数据库

创建数据库,就是要确定数据库名称、文件名称、数据文件大小、数据库字符集、是否自动增长以及如何自动增长等信息. 数据库是由登录名创建的,而要创建数据库,登录名必须具有:

CREATE DATABASE、CREATE ANY DATABASE或ALTER ANY DATABASE的权限.

创建最简单的数据库–使用CREATE DATABASE语句,并不加任何限定:

CREATE DATABASE MyDatabase1;
GO

建立的数据库如图:

此时,建立的数据库只使用MyDatabase1这个名称,并未指定其他选项,于是使用系统默认值,如下:

系统数据和日志放在SQL server系统默认的位置.
数据文件默认大小3MB,日志文件默认大小1MB.
数据文件和日志文件都默认自动增长.

可以看以下的一个样例,手动设定数据库的一些选项:

CREATE DATABASE LCBCom	--新建数据库LCBCom;
ON (
	NAME = LCB_Com_DATA,	--逻辑名称;
	FILENAME = '\\VBOXSVR\VirtualBox_Share\MyDataBase\LCBCom\LCBCom_DATA.mdf', --物理位置;
	SIZE = 6,	--数据文件初始大小;
	MAXSIZE = 20,	--数据文件最大大小;
	FILEGROWTH = 10%)	--按当前容量的10%增长;
LOG ON (
	NAME = LCB_Com_LOG,	--逻辑名称;
	FILENAME = '\\VBOXSVR\VirtualBox_Share\MyDataBase\LCBCom\LCBCom_LOG', --物理位置;
	SIZE = 2MB,	--日志初始大小;
	MAXSIZE = 10MB,	--日志最大大小;
	FILEGROWTH = 1MB)	--日志增长方式,一次1MB;
GO

其中,各参数意义如下:

NAME指定的是数据/日志文件的逻辑名称,也就是在数据库用户眼中数据库文件的名称.
FILE_NAME指定的是数据库数据/日志文件的实际存放路径和名称,使用绝对路径.
SIZE是数据库数据文件/日志文件的初始空间大小,MAX_SIZE是这些文件最大能占用的空间大小.
FILEGROWTH指的是数据库数据/日志文件超过现有体积,无法存放时应采用何种大小的增量扩大文件大小.

建立的数据库如图:

注意:

关于数据库数据/日志文件的大小,默认使用的单位(不指定单位时)是MB,还可以指定KB、GB、TB.
对于文件增量FILEGROWTH,除了使用上面的文件单位以外,还可以指定百分比%来设定增量.

一些其他的技巧:

合理设置增量: 如果数据库大小不断增长,则指定其增长方式,如果数据库体积基本不变,则不指定.
使用逗号分隔多个数据/日志文件,多个数据文件要指定主数据文件,主数据文件是数据库的起点,指向其他数据文件,默认第一个是主数据文件,也可以使用PRIMARY关键字手动指定.
若要使用的字符集和服务器的不同,可以使用COLLATE关键字指定数据库的字符集.
合理使用文件组来管理数据库文件,使用FILEGROUP关键字来指定.

根据以上技巧,我们再来建立一个更为精细的数据库:

CREATE DATABASE LCBSalesDB	--新建数据库LCBSalesDB;
ON PRIMARY (	--指定主数据文件;
	NAME = LCBSFile_dat,
	FILENAME = '\\VBOXSVR\VirtualBox_Share\MyDataBase\LCBSalesDB\LCBSFileDat.mdf', --物理位置;
	SIZE = 5,MAXSIZE = 50,FILEGROWTH = 10%
),
FILEGROUP SG (	--指定文件组;
	NAME = SGF1_dat,	--第一个数据文件;
	FILENAME = '\\VBOXSVR\VirtualBox_Share\MyDataBase\LCBSalesDB\SGF1Dat.ndf',
	SIZE = 5,MAXSIZE = 50,FILEGROWTH = 5
),(	
	NAME = SGF2_dat,	--第二个数据文件;
	FILENAME = '\\VBOXSVR\VirtualBox_Share\MyDataBase\LCBSalesDB\SGF2Dat.ndf',
	SIZE = 5,MAXSIZE = 50,FILEGROWTH = 5
)
LOG ON (	--日志文件;
	NAME = LCBSFile_log,
	FILENAME = '\\VBOXSVR\VirtualBox_Share\MyDataBase\LCBSalesDB\LCBSFile_log',
	SIZE = 3,MAXSIZE = 25,FILEGROWTH = 1
);
GO

数据库属性如下:

数据库的状态和选项

为了理解和维护数据库,我们需要清楚数据库的当前状态,还需知道针对数据库操作的选项,按需查阅即可.

数据库状态清单:

State	定义
ONLINE	可以对数据库进行访问.即使可能尚未完成恢复的撤消阶段,主文件组仍处于在线状态
OFFLINE	数据库无法使用.数据库由于显式的用户操作而处于离线状态,并保持离线状态直至执行了其他的用户操作.例如,可能会让数据库离线以便将文件移至新的磁盘.然后,在完成移动操作后.使数据库恢复到在线状态.
RESTORING	正在还原主文件组的一个或多个文件,或正在脱机还原一个或多个辅助文件.数据库不可用.
RECOVERING	正在恢复数据库.恢复进程是一个暂时性状态,恢复成功后数据库将自动处于在线状态.如果恢复失败,数据库将处于可疑状态.数据库不可用.
RECOVERY PENDING	SQL Server在恢复期间遇到了与资源相关的错误.数据库未损坏,但是可能缺少文件,或系统资源限制可能导致无法启动数据库.数据库不可用.需要用户另外执行操作来解决问题.
SUSPECT	至少主文件组可疑或可能已损坏.在SQL Server启动过程中无法恢复数据库.数据库不可用. 需要用户另外执行操作来解决问题.
EMERGENCY	用户更改了数据库,并将其状态设置为EMERGENCY.数据库处于单用户模式,可以修复或还原. 数据库标记为READ_ONLY,禁用日志记录,并仅限sysadmin固定服务器角色的成员访问. EMERGENCY主要用于故障排除.例如,可以将标记为“可疑”的数据库设置为EMERGENCY状态.这样可以允许系统管理员对数据库进行只读访问.只有sysadmin固定服务器角色的成员才可以将数据库设置为EMERGENCY状态.

除了状态,数据库系统提供了一些数据库选项,可以通过这些选项来将数据置为某个状态或者执行某个操作.

可以使用ALTER DATABASE中使用SET子句来设置这些数据库选项,下表列出了常用的数据库选项:

选项	描述
ONLINE	在线或联机状态,服务器可正常使用
OFFLINE	离线或脱机状态,服务器无法正常使用
EMERGENCY	紧急状态,服务器不可使用且只读,只有sysadmin可访问
SINGLE_USER	单用户模式,一次仅一个用户可访问数据库
RESTRICTED_USER	限制用户模式,只有sysadmin、db_owner、dbcreator的用户可访问数据库
MULTI_USER	正常模式,所有用户可同时访问数据库
READ_ONLY	只读模式,数据库处于只读状态
READ_WRITE	读写模式,正常状态,所有用户可读写
AUTO_CLOSE	ON选项表示最后用户退出数据库后关闭数据库,OFF选项则不关闭
AUTO_CREATE_STATISTICS	ON选项表示自动创建优化查询需要的统计数据,OFF则不自动创建
AUTO_UPDATE_STATISTICS	ON选项表示自动更新优化查询需要的/已过期的统计数据,OFF则不自动更新
AUTO_SHRINK	ON选项表示开启数据库文件自动收缩(因其它因素可能不生效),OFF则不开启
RECOVERY	FULL表示完全记录事务日志,可执行事务日志备份和恢复;BULK_LOGGED表示按最小方式记录大量数据操作;SIMPLE表示不记录事务日志

将数据库设为只读的示例如下:

USE master;

ALTER DATABASE AdventureWorks2017 
	SET AUTO_CLOSE ON,READ_ONLY;	--开启自动关闭和只读;
GO

注意:

设置数据库的选项应在master数据库中执行.
sp_configure设置服务器级别的选项,ALTER DATABASE设置数据库级别的选项,SET只影响当前用户会话.

查看数据库信息

在SQL server系统中,使用一些存储过程、函数、目录视图可以查看数据库的基本信息,常用的如下:

存储过程/函数/目录视图	用途
sys.databases	查看所有数据库信息、选项和状态
sys.database_files	查看当前数据库文件信息
sys.filegroups	查看当前数据库文件组信息
sys.master_files	查看所有数据库的文件信息、选项和状态
sp_spaceused	查看当前数据库空间使用情况
sp_helpdb ‘db_name’	查看指定数据库的数据库信息、选项和状态
DATABASEPROPERTYEX(‘db_name’,‘property’)	查看指定数据库的指定选项设置

下面示例展示几个用法:

USE AdventureWorks2017;
SELECT * FROM sys.database_files;	--查看AdventureWorks的数据库文件;
GO

USE master;
SELECT * FROM sys.database_files;	--查看master的数据库文件;
GO

USE LCBSalesDB;
SELECT * FROM sys.database_files;	--查看LCBSalesDB的数据库文件;
GO

执行结果如下:

SELECT * FROM sys.master_files;	--查看所有数据库的文件信息和状态信息;
GO

USE LCBSalesDB;
SELECT * FROM sys.filegroups;		--查看LCBSalesDB的文件组信息;
GO

执行结果如下:

EXEC sp_helpdb 'AdventureWorks2017';		--查看AdventureWorks2017的信息;
GO

USE AdventureWorks2017;	--查看AdventureWorks2017的空间使用情况;
EXEC sp_spaceused;
GO

执行结果如下:

SELECT * FROM sys.databases;	--查看所有数据库信息、选项和状态;
GO

SELECT DATABASEPROPERTYEX('AdventureWorks2017','RECOVERY');	--查看AdventureWorks2017的RECOVERY选项设置;
GO

执行结果如下:

修改数据库

数据库创建以后,就可以对其进行管理,最重要的就是对数据库的修改.修改数据库包括: 修改数据库名称、修改数据库文件、扩大/收缩数据库、管理数据库文件组以及修改字符排列规则等.

修改数据库使用ALTER DATABASE进行,其后会跟一些关键字,下面让我们具体情况具体分析.

修改数据库名称

使用MODIFY NAME关键字可指定数据库新名称,如下示例:

ALTER DATABASE Temp MODIFY NAME = T;	--将数据库Temp更名为T;
GO

可见数据库名称的变化:

注意两点:

更名只是改变了数据库逻辑名称,对数据文件和日志文件没有丝毫影响.
更名会极大地影响相关应用程序,不应该对数据库名称轻易改动,创建数据库时命名一定要严谨.

扩大数据库

如果数据量不断增大,将会导致数据库空间不足的情况,此时我们需要扩大数据库.扩大数据库有三种方式:

设置数据库为自动增长,可在创建数据库时设置;
直接修改数据库数据文件和日志文件的大小;
在数据库中新增次要数据文件或日志文件.

方案1之前已经讲过了,对于方案2,我们可以修改数据库文件:

使用MODIFY FILE来修改文件,下面列出了支持修改的一些属性:

属性名	描述
NAME	原有逻辑文件名
FILENAME	物理文件名
NEWNAME	新的逻辑文件名
SIZE	文件的新大小,必须 $\gt$ 文件的当前大小
MAXSIZE	文件最大大小
FILEGROWTH	文件增量大小,不能超过最大大小

如果不指定FILEGROWTH,则使用系统默认值,如下:

版本	默认值
自 SQL Server 2016 (13.x) 起	数据 64 MB。日志文件 64 MB
自 SQL Server 2005 起	数据 1 MB。日志文件 10%
SQL Server 2005 之前	数据 10%。日志文件 10%

注意: 修改文件时,必须加上NAME以标识修改对应的逻辑文件.

以下是几个实例:

修改数据库文件位置:

ALTER DATABASE LCBCom MODIFY FILE (
	NAME = 'LCB_Com_DATA',FILENAME = 'F:\MyDataBase\LCBCom\LCBCom.mdf'
)
GO

ALTER DATABASE LCBCom MODIFY FILE (
	NAME = 'LCB_Com_LOG',FILENAME = 'F:\MyDataBase\LCBCom\LCBCom_log.ldf'
)
GO

ALTER DATABASE LCBSalesDB MODIFY NAME = LCBSales;	--先改一下名;
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'LCBSFile_dat',FILENAME = 'F:\MyDataBase\LCBSales\LCBSales.mdf'
)
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'LCBSFile_log',FILENAME = 'F:\MyDataBase\LCBSales\LCBSales_log.ldf'
)
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'SGF1_dat',FILENAME = 'F:\MyDataBase\LCBSales\SGFile1.ndf'
)
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'SGF2_dat',FILENAME = 'F:\MyDataBase\LCBSales\SGFile2.ndf'
)
GO

以上实例是迁移数据库的文件位置,因虚拟机的共享文件夹有时会被SQL server当成不可访问的路径,因此这次将上次创建的数据库迁移到新增的虚拟硬盘上去(先复制文件,再更改对应逻辑关系),另:刚刚发现两个数据库逻辑文件建立时都没有加扩展名,这次迁移文件就顺便加上去了,完成结果如图:

修改逻辑文件名:

ALTER DATABASE LCBCom MODIFY FILE (
	NAME = 'LCB_Com_DATA',NEWNAME = 'LCBCom'
)
GO

ALTER DATABASE LCBCom MODIFY FILE (
	NAME = 'LCB_Com_LOG',NEWNAME = 'LCBCom_log'
)
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'LCBSFile_dat',NEWNAME = 'LCBSales'
)
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'LCBSFile_log',NEWNAME = 'LCBSales_log'
)
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'SGF1_dat',NEWNAME = 'SGFile1'
)
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'SGF2_dat',NEWNAME = 'SGFile2'
)
GO

运行结果如下:

修改文件大小:

ALTER DATABASE LCBCom MODIFY FILE (
	NAME = 'LCBCom',SIZE = 15,FILEGROWTH = 10,MAXSIZE = 200
)
GO

ALTER DATABASE LCBCom MODIFY FILE (
	NAME = 'LCBCom_log',SIZE = 10,FILEGROWTH = 1,MAXSIZE = 64
)
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'LCBSales',SIZE = 15,FILEGROWTH = 10%,MAXSIZE = 100
)
GO

ALTER DATABASE LCBSales MODIFY FILE (
	NAME = 'LCBSales_log',SIZE = 10,FILEGROWTH = 1,MAXSIZE = 64
)
GO

运行结果如下:

方案3,增加次数据文件/日志文件:

通过ADD FILE来增加新文件,ADD FILE能使用除了NEWNAME之外的所有文件选项,此外还可以使用ADD FILEGROUP向数据库添加文件组,具体实例如下:

ALTER DATABASE LCBCom ADD FILEGROUP FG1;	--新增文件组;
GO 
ALTER DATABASE LCBCom ADD FILE (
	NAME = 'FG1File1',FILENAME = 'F:\MyDataBase\LCBCom\FG1File1.ndf',SIZE = 5,FILEGROWTH = 10%,MAXSIZE = 50
),(
	NAME = 'FG1File2',FILENAME = 'F:\MyDataBase\LCBCom\FG1File2.ndf',SIZE = 5,FILEGROWTH = 10%,MAXSIZE = 50
) TO FILEGROUP FG1;	--新建文件,并加入文件组;
GO

运行结果如下:

当然也可以通过ADD LOG FILE新增日志文件,操作和上面一模一样,就不再赘述.

收缩数据库

有时候数据库的设计容量过大,且使用频率并非很高,数据库大小基本固定不变的话,就可以考虑释放一些空间了,这就是常说的收缩数据库.同样的,收缩数据库也有三种方式:

设置数据库自动收缩,可通过AUTO_SHRINK数据库选项来实现;
收缩整个数据库的大小,可通过DBCC SHRINKDATABASE来实现;
收缩指定的数据文件,可通过DBCC SHRINKFILE来实现.

注意: 不能使用ALTER DATABASE的MODIFY FILE来收缩数据库文件,因为目标大小不能小于当前文件大小.

方案1: AUTO_SHRINK的使用

请看如下示例:

ALTER DATABASE LCBCom SET AUTO_SHRINK ON;	--将数据库自动收缩开启;
GO

数据库自动收缩就是这么简单,说一下关于这个选项的一些要点:

默认AUTO_SHRINK处于关闭(OFF)状态.
仅当RECOVERY选项设置为SIMPLE或日志文件已经备份时,该选项才减小日志文件大小.
当文件中空闲空间大于25%时,开始执行收缩,以MAX((FREESPACE == MAXSIZE*25%),SIZE)为结束.
数据库大小无法收缩至其创建时的大小(即SIZE),无法收缩只读数据库.

方案2: DBCC SHRINKDATABASE的使用

首先搞清楚,这个函数的参数信息:DBCC SHRINKDATABASE(‘db_name’,target_percent,(property));其中:

'db_name’是数据库名称,字符串类型.
target_percent是一个百分数,表示收缩后文件未使用空间占总分配空间的百分比.
property是一个可选参数,有如下两个参数,这两个参数不可同时指定:

可选参数	作用
NOTRUNCATEONLY	将文件数据移动到前面的数据页,但不将未使用空间归还给OS,文件大小不变
TRUNCATEONLY	将文件末尾未分配的空间全部归还OS,此时指定收缩百分比没有实际意义

示例如下:

DBCC SHRINKDATABASE('T',10);	--控制空闲空间到10%;
GO

DBCC SHRINKDATABASE('LCBCom',20,NOTRUNCATE);	--不删节处理;
GO

DBCC SHRINKDATABASE('LCBSales',20,TRUNCATEONLY);	--文件删节处理;
GO

运行结果如下:

注意: DBCC SHRINKDATABASE也不能收缩到低于初始创建数据库的大小.

方案3: DBCC SHRINKFILE的使用

先贴一下微软官方文档对该函数的说明:

收缩当前数据库的指定数据或日志文件大小.可以使用它将一个文件中的数据移到同一文件组中的其他文件,这会清空文件,从而允许删除数据库.可以将文件收缩到小于创建大小,同时将最小文件大小重置为新值.

可以看出,这个函数是收缩数据库可使用的最强大的函数,收缩数据库几乎不受数据库原有条件的限制.

那么,这个函数的参数:DBCC SHRINKFILE(‘file_name’,EMPTYFILE | target_size,(property));说明如下:

'file_name’是数据库逻辑文件名称,字符串类型.
EMPTYFILE/target_size二选一,其中:

可选参数	作用
EMPTYFILE	将当前文件内容移动到其他文件,再删除当前文件
target_size	指定文件收缩到多大的体积

property同DBCC SHRINKDATABASE函数.

注意: DBCC SHRINKFILE只能收缩空闲空间,不能收缩正在使用的空间.

请看实例:

先查看两个数据库的情况:

尝试运行以下代码:

USE LCBCom;
DBCC SHRINKFILE('LCBCom',5);	--试图将文件收缩到5MB;
GO

USE MyDatabase1;
DBCC SHRINKFILE('MyDatabase1',EMPTYFILE);	--清空文件,试图删除;
GO

USE LCBCom;
DBCC SHRINKFILE('LCBCom_log',5,NOTRUNCATE);	--试图将文件收缩到5MB,不归还空间;
GO

USE LCBSales;
DBCC SHRINKFILE('LCBSales_log',5,TRUNCATEONLY);	--试图将文件收缩到5MB,并归还空间;
GO

错误信息如下:

我们来进行分析:

红色部分的错误是因为MyDatabase1只有一个数据文件,清空文件将无法往其他数据文件上附加,因此报错.
其他部分都是因为没有注意到原文件的大小,收缩后比原文件还要大,因此出错.

进行改动的代码如下:

USE LCBCom;
DBCC SHRINKFILE('LCBCom',5);	--试图将文件收缩到5MB;
GO

USE LCBCom;
DBCC SHRINKFILE('LCBCom_log',2,NOTRUNCATE);	--试图将文件收缩到2MB,不归还空间;
GO

USE LCBSales;
DBCC SHRINKFILE('LCBSales_log',4,TRUNCATEONLY);	--试图将文件收缩到4MB,并归还空间;
GO

结果如下:

最后说一点总结,顺便说一下没有提到的操作:

无论是DBCC SHRINKDATABASE的’db_name’还是DBCC SHRINKFILE的file_name,都可以用id代替,如:

DBCC SHRINKDATABASE(0,10%);

在数据库中操作时,0表示的是当前数据库的id,我们可以查看每个数据库的id,称为database_id.相应的,如:

USE LCBCom;
DBCC SHRINKFILE(1,5);	--试图将文件收缩到5MB;
GO

表示LCBCom数据库中,id为1的逻辑文件(即LCBCom),文件id称为file_id.

注意id是一个数字,不需要加上单引号.

其他数据库修改操作

删除文件/文件组

使用ALTER DATABASE的子句REMOVE FILE和REMOVE FILEGROUP来删除文件和文件组(删除包括逻辑文件和物理文件),注意:

无法删除非空文件/非空文件组;
先删除文件,再删除文件组.

我们可以配合DBCC SHRINKFILE的EMPTY选项先清空文件,再删除:

USE LCBSales;
DBCC SHRINKFILE('SGFile2',EMPTYFILE);	--将文件清空;
GO

ALTER DATABASE LCBSales REMOVE FILE SGFile2;	--删除空文件;
GO

执行结果如下:

删除文件组不多说,将组内所有文件删除/移至其他文件组,再删除即可,语法和删除文件一致.

管理文件组

除了上面说到的新增/删除文件组,还可以重命名文件组,设置默认文件组.均使用MODIFY FILEGROUP子句:

默认文件组: 默认文件组的作用是,新建文件时若没有指定其纳入的文件组,将放在默认文件组中.每个数据库都有一个默认文件组PRIMARY,该文件组不可被删除.用户可以指定其他文件组为默认文件组,但是系统表等信息总是放在PRIMARY文件组中.如下示例设置默认文件组:

ALTER DATABASE LCBSales MODIFY FILEGROUP SG DEFAULT;	--设置默认文件组
GO

注意: 仅能将现有的文件组设为默认文件组,无法在新建文件组时指定.

重命名文件组

非常简单,不多说,参照如下格式:

ALTER DATABASE db_name
MODIFY FILEGROUP fg_name NAME new_fg_name;
GO

文件组读写属性

当然只有两种: READ_ONLY和READ_WRITE.实例如下:

ALTER DATABASE LCBSales MODIFY FILEGROUP SG READ_ONLY;	--设置文件组读写属性;
GO

结果如下:

管理数据库快照

快照,即snapshot,进行过OS/虚拟机OS备份的人都知道,这是对某个系统某个时刻的状态完全以静态文件的形式保存起来.因此,数据库快照看成是数据库的一种静态形式就好了,它好像给数据库照个相,记录数据库某时刻的模样.

新建数据库快照

使用CREATE DATABASE的AS SNAPSHOT OF关键字可以建立数据库快照.示例如下:

CREATE DATABASE AdventureWorks2017_snapshot 
ON (
	NAME = 'AdventureWorks2017',
	FILENAME = 'F:\MyDataBase\AdventureWorks2017_snapshot\AdventureWorks_snapshot.mdf'
) AS SNAPSHOT OF AdventureWorks2017;
GO

结果如下:

看似是建立数据库的语法,但是数据库快照可不是正常的数据库,它有如下限制:

必须与源数据库在相同的服务器上才可创建快照
快照仅包含开始创建时间点的数据,不包含未提交的事务
快照是只读的,不能修改文件
不能创建基于model、master、tempdb等系统数据库的快照
不能对快照进行备份/还原操作
不可附加/分离快照
快照不支持全文索引,因此源数据库中的全文目录无法传过来
快照继承源数据库的安全约束,但由于其只读,因此无法将数据库对权限的修改映射到快照
快照始终反应创建时的文件组状态

删除数据库快照

使用DROP DATABASE语句可以删除数据库快照,如:

DROP DATABASE AdventureWorks2017_snapshot;
GO

其他操作

分离数据库

分离数据库指的是将数据库从SQL server的实例中移除,但是数据库仍然存在且内部逻辑文件关系不变.只是不在SQL server中显示,相当于隐藏数据库.使用sp_detach_db存储过程来分离数据库,如:

EXEC sp_detach_db T;
GO

这样SQL server系统就看不到T数据库了:

附加数据库

附加数据库是分离数据库的逆操作,可以将分离的数据库添加到SQL server系统中,并恢复成分离时的样子.

使用CREATE DATABASE的FOR ATTACH子句来附加数据库,务必确保附加时的文件可用,若不指定日志文件,则将会自动创建一个日志文件.下面是一个实例:

CREATE DATABASE Temp
ON (
	NAME = 'Temp',
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Temp.mdf'
)
LOG ON (
	NAME = 'Temp_log',
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Temp_log.ldf'
) FOR ATTACH;
GO