怎样压缩MSSQL数据库

分类: 电脑/网络 >>程序设计 >>其他编程语言

问题描述:

怎样压缩MSSQL数据库,我的数据库文件显示占用空间120M,可用空间105M,其实正直有用的也只有几十M,怎样压缩呢?请哪位朋友提供解决方法!

解析:

数据库在使用一段时间后，时常会出现因数据删除而造成数据库中空闲空间太多的情况，这时就需要减少分配给数据库文件和事务日志文件的磁盘空间，以免浪费磁盘空间。当数据库中没有数据时，可以修改数据库文件属性直接改变其占用空间，但当数据库中有数据时，这样做会破坏数据库中的数据，因此需要使用压缩的方式来缩减数据库空间。可以在数据库属性选项中选择“Auto shrink”选项，让系统自动压缩数据库，也可以用人工的方法来压缩。人工压缩数据库有以下两种方式：

1、用Enterprise Manager 压缩数据库

在Enterprise Manager 中在所要压缩的数据库上单击右键，从快捷菜单中的“所有任务（All Tasks）”中选择“Shrink Database（压缩数据库）”选项

、用Transact-SQL 命令压缩数据库

可以使用DBCC SHRINKDATABASE 和DBCC SHRINKFILE 命令来压缩数据库。其中DBCC SHRINKDATABASE 命令对数据库进行压缩，DBCC SHRINKFILE 命令对数据库中指定的文件进行压缩。

（1） DBCC SHRINKDATABASE

DBCC SHRINKDATABASE 命令语法如下：

DBCC SHRINKDATABASE (database_name [, target_percent]

[, {NOTRUNCATE | TRUNCATEONLY}] )

各参数说明如下：

target_percent 指定将数据库压缩后，未使用的空间占数据库大小的百分之几。如果指定的百分比过大，超过了压缩前未使用空间所占的比例，则数据库不会被压缩。并且压缩后的数据库不能比数据库初始设定的容量小。

NOTRUECATE

将数据库缩减后剩余的空间保留在数据库，中不返还给 *** 作系统。如果不选择此选项，则剩余的空间返还给 *** 作系统。

TRUNCATEONLY

将数据库缩减后剩余的空间返还给 *** 作系统。使用此命令时SQL Server 将文件缩减到最后一个文件分配，区域但不移动任何数据文件。选择此项后，target_percent 选项就无效了。

压缩数据库mytest 的未使用空间为数据库大小的20%。

dbcc shrinkdatabase (mytest, 20)

运行结果如下：

DBCC execution pleted. If DBCC printed error messages, contact your system administrator.

(2) DBCC SHRINKFILE

DBCC SHRINKFILE 命令压缩当前数据库中的文件。其语法如下：

DBCC SHRINKFILE ( {file_name | file_id }

{ [, target_size] |

[, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}] } )

各参数说明如下：

file_id

指定要压缩的文件的鉴别号（Identification number， 即ID）。文件的ID 号可以通过 FILE_ID（）函数或如本章前面所讲述的Sp_helpdb 系统存储过程来得到。

target_size

指定文件压缩后的大小。以MB 为单位。如果不指定此选项，SQL Server 就会尽最大可能地缩减文件。

EMPTYFILE

指明此文件不再使用，将移动所有在此文件中的数据到同一文件组中的其它文件中去。执行带此参数的命令后，此文件就可以用ALTER DATABASE 命令来删除了。

其余参数NOTRUNCATE 和TRUNCATEONLY 与DBCC SHRINKDATABASE 命令中的含义相同。

例6-15: 压缩数据库mydb 中的数据库文件mydb_data2 的大小到1MB。 use mydb dbcc shrinkfile (mydb_data2, 1)

具体方法有3种。

方法一:

第一步：

backup log database_name with no_log

或者 backup log database_name with truncate_only

-- no_log和truncate_only是在这里是同义的，随便执行哪一句都可以。

第二步：

1.收缩特定数据库的所有数据和日志文件，执行：

dbcc shrinkdatabase (database_name,[,target_percent])

-- database_name是要收缩的数据库名称；target_percent是数据库收缩后的数据库文件中所要的剩余可用空间百分比。

2.收缩一次一个特定数据库中的数据或日志文件，执行

dbcc shrinkfile(file_id,[,target_size])

-- file_id是要收缩的文件的标识 (ID) 号,若要获得文件 ID，请使用 FILE_ID 函数或在当前数据库中搜索 sysfiles；target_size是用兆字节表示的所要的文件大小（用整数表示）。如果没有指定，dbcc shrinkfile 将文件大小减少到默认文件大小。两个dbcc都可以带上参数notruncate或truncateonly，具体意思查看联机帮助.

方法二：

第一步：

先备份整个数据库以备不测 。

第二步：

备份结束后，在Query Analyzer中执行如下的语句：

exec sp_detach_db yourDBName,true

--卸除这个DB在MSSQL中的注册信息

第三步：

到日志的物理文件所在的目录中去删除该日志文件或者将该日志文件移出该目录

第四步：

在Query Analyzer中执行如下的语句：

exec sp_attach_single_file_db yourDBName,'

d:\mssql\data\yourDBName_data.mdf '

--以单文件的方式注册该DB，如果成功则MSSQL将自动为这个DB生成一个500K的日志文件。

方法三:

1. 进入企业管理器，选中数据库，比如demo

2. 所有任务->分离数据库

3. 到数据库文件的存放目录，将MuOnline_log.LDF文件删除，以防万一，你可以拷出去

4. 企业管理器->附加数据库，选muonline，这个时候你会看见日志文件这项是一个叉，不要紧，继续，此时数据库就会提示你该数据库无日志是否创建一个新的，确定就是了。

5. 记得数据库重新附加后用户要重新设置一下。

如果以后,不想要它变大:

SQL2000下使用：

在数据库上点右键->属性->选项->故障恢复-模型-选择-简单模型。

或用SQL语句：

alter database 数据库名 set recovery simple

本主题介绍如何使用

SQL

Server

Management

Studio

中的对象资源管理器收缩数据库。收缩后的数据库不能小于数据库的最小大小。最小大小是在数据库最初创建时指定的大小，或是上一次使用文件大小更改 *** 作（如

DBCC

SHRINKFILE）设置的显式大小。例如，如果数据库最初创建时的大小为

10

MB，后来增长到

100

MB，则该数据库最小只能收缩到

10

MB，即使已经删除数据库的所有数据也是如此。

收缩数据库

在对象资源管理器中，连接到

SQL

Server

数据库引擎实例，然后展开该实例。

展开“数据库”，再右键单击要收缩的数据库。

指向“任务”，指向“收缩”，然后单击“数据库”。

根据需要，可以选中“在释放未使用的空间前重新组织文件”复选框。如果选中该复选框，必须为“收缩后文件中的最大可用空间”指定值。

选中该选项的作用与执行

DBCC

SHRINKDATABASE

时指定

target_percent

值相同。清除该选项的作用与使用

TRUNCATEONLY

选项执行

DBCC

SHRINKDATABASE

相同。TRUNCATEONLY

将文件收缩到最后分配的区。这将减小文件的大小，但不移动任何数据。默认情况下，该选项为清除状态。

输入收缩数据库后数据库文件中剩下的最大可用空间百分比。允许的值介于

0

和

99

之间。该选项仅在选中“在释放未使用的空间前重新组织文件”时可用。

单击“确定”。

收缩数据库

当

数据库中的数据库文件过于庞大或者存在未使用页时，可以使用收缩数据库方法来实现对数据库的收缩，同样，事务日志文件也可以收缩。数据库文件可以作为组或

单独地进行手工收缩，也可设置为按指定的时间间隔自动收缩。收缩数据库的活动是在后台进行，并不影响数据库内的用户活动。

*** 作步骤如下：

（1）打开SQL

Server企业管理器。双击打开“数据库”节点，在要收缩的数据库名称上，单击鼠标右键，在d出的快捷菜单中选择“所有任务”→“收缩数据库”命令，d出“收缩数据库”对话框，如图1所示。

图1

收缩数据库

（2）指定数据库的收缩量，可以从以下选项中选择。

在“收缩后文件中的最大可用空间”文本框中输入收缩后数据库中剩余的可用空间量。以“数据库大小，可用空间”值作为依据。如最大可用空间是“25%”，则在该选项中设置小于该数值的百分比，如“12%”。

选择“在收缩前将页移到文件起始位置”复选框，使释放的文件空间保留在数据库文件中，并使包含数据的页移到数据库文件的起始位置。

（3）在“调度”选项框中，选择是否自动定期执行数据库压缩 *** 作。选中“根据本调度来收缩数据库”复选框，单击“更改”按钮，创建或更改自动收缩数据库的频率和时间。

（4）最后单击“确定”按钮完成 *** 作。

---