当前位置：首页 > 数据库 > mssql2008 > 详细页面

SQL SERVER 2008 r2 数据压缩的两种方法

时间：2023-12-07来源：系统城装机大师作者：佚名

有时候sql server 2008 数据库日志文件太大，需要收缩释放硬盘内存。如果ldb文件过大会导致数据库运行缓慢，甚至系统都会卡住。

1.登陆项目平台数据库服务器。双击SQL Server Management Studio打开数据库管理。登陆数据库

clip_image004

2.如下图，打开数据库属性窗口

clip_image006

3.如下图，更改数据库恢复模式

clip_image010

4.如下图，收缩数据库日志

clip_image012

clip_image014

到这里已经完成了，数据库的日志收缩

5.如下图，数据库恢复模式修改为完整

clip_image016

经过脚本之家小编测试，完美解决，我们成功的把一个84G的文件，压缩到1M。

下面继续为大家分享一个通过sql语句实现的，每次手工操作麻烦有没有。

sql语句实现步骤如下

首先查找要收缩日志文件的数据库文件名

USE A

GO

SELECT file_id, name FROM sys.database_files;

GO

查询结果得到日志文件的文件名叫J4_log

不过脚本之家小编测试查询比较慢，可以通过下面的方法

数据库属性>文件>右侧日志前面这个名字就是日志文件名了

测试完美没有异常。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 USE[master]
GO
ALTER DATABASE A SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE A SET RECOVERY SIMPLE --简单模式
GO
USE A
GO
DBCC SHRINKFILE (N'J4_Log', 11, TRUNCATEONLY)

GO

USE[master]
GO
ALTER DATABASE A SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE A SET RECOVERY FULL --还原为完全模式
GO

如果感觉比较麻烦，可以设置一个自动任务执行，将上面的文件保存为yasuo.sql

然后通过计划任务结合cmd，执行如下命令即可，目录自定设置好

sqlcmd -i yasuo.sql

SQL Server 2008R2执行大文件SQL脚本命令

cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn
sqlcmd -S . -U sa -P 123 -d test -i data.sql

参数说明：-S 服务器地址 -U 用户名 -P 密码 -d 数据库名称 -i 脚本文件路径

本地服务器地址可以写 . 比较轻松，也可写.或者(local)或者IP地址

这样就可以了，以后新建一个查询，直接运行就可以了。

一般情况下不建议压缩数据，如果压缩数据建议先备份

第一种方法：通过sql server management studio

首先我们要下载能操作 2008的工具 sql server management studio 这个工具在sql server 2008 r2 安装后就会有！

一起安装妥当，我们就可以开始选择了看图！所有的都是单击右键，凡在你需要压缩的表上面，依次选择到数据库就可以了！

然后就是这样的画面！点击确定，就可以了！记住是“收缩”而不是压缩，但是效果都是一样的！

第二种：通过存储过程

SQL Server 2008中的数据压缩

SQL Server 2008中引入了数据压缩的功能，允许在表、索引和分区中执行数据压缩。这样不仅可以大大节省磁盘的占用空间，还允许将更多数据页装入内存中，从而降低磁盘IO，提升查询的性能。当然，凡事有利有弊，在启用数据压缩后，数据库服务器就需要额外的CPU资源来进行压缩处理。一般说来，数据库服务器的CPU占用率不会太高，而磁盘IO容易成为瓶颈，所以在大多数情况下对大数据库特别是数据仓库启用该项功能还是利大于弊。

SQL Server 2008的数据压缩分为行压缩和页压缩两种。行压缩主要是通过将固定长度类型存储为可变长度类型来实现，同时还减少了与记录相关联的元数据开销。页压缩在行压缩的基础上又增加了前缀压缩和字典压缩，能获得更大的压缩率。
要启用数据库压缩只需在建表语句后加入WITH (DATA_COMPRESSION = ROW)或是WITH (DATA_COMPRESSION = PAGE)即可。如需将现有的索引修改为启用压缩，可通过ALTER INDEX index ON Table REBUILD WITH (DATA_C0MPRESSION=ROW)或ALTER INDEX index ON Table REBUILD WITH (DATA_C0MPRESSION=PAGE)实现。
最后提供一段简单的用以判断是否需要压缩数据表的脚本，并自动生成压缩脚本供系统管理员执行。这里用到未公开的存储过程sp_MSforeachtable。在这段脚本中@precommand参数用于执行command指令执行前的SQL命令，建立一张临时表用于保存数据表的信息，@command1参数表示需要执行的SQL命令，对每一张表都利用sp_spaceused存储过程获取表的磁盘占用信息并保存到建立的临时表中，@postcommand参数用于执行command指令后的SQL命令，将之前建立的临时表与系统关联，根据设置的条件（数据表占用空间大于10G）生成数据表压缩脚本。

1 exec sp_MSforeachtable@precommand=N'create table ##(id int identity,name sysname,rows int,reserved Nvarchar(50),data varchar(50),indexdata varchar(50),unused varchar(50))',@command1=N'insert into ##(name,rows,reserved,data,indexdata,unused) exec sp_spaceused ''?''update ## set data=SUBSTRING(data, 1, LEN(data) - 2) where id=scope_identity() AND LEN(data) >=2',@postcommand=N'SELECT ''ALTER TABLE '' + TABLENAME + '' REBUILD WITH ( DATA_COMPRESSION = PAGE )'' FROM sys.tables AJOIN(SELECT C.name + ''.'' + A.name AS TABLENAME, object_id FROM ## AJOIN sys.objects BON A.name = B.nameJOIN sys.schemas CON B.schema_id = C.schema_idWHERE CAST(data AS int) > 10000000 AND object_id IN (SELECT object_id FROM sys.tables)) BON A.object_id = B.object_id AND type = ''U'';drop table ##'

下面是一些比较好的补充：

sql2008r2如何进行日志文件压缩？

为何日志文件会过大？

最常见的问题是数据库为FULL Recovery Model但是从没有做过LOG BACKUP。因为只有Log Backup才可以Truncate Log导致之前的日志文件重用，所以先看一下这个。

如果是上面的问题，你不需要备份将数据库恢复模式修改为Simple，然后Shrink Log File就解决了。

Good Luck。

SQL Server 2008如何压缩日志(log)文件？

在SQL Server 2000/2005中可以快速压缩日志log文件，通过SQL,

方法一：

--BigData为数据库名
DUMP TRANSACTION BigData WITH NO_LOG
BACKUP LOG BigData WITH NO_LOG
DBCC SHRINKDATABASE(BigData )执行以上语句可以快速压缩日志文件到1M。

但是以上语句中前两行在SQL Server 2008下无法执行，

第一行提示“Incorrect syntax near the keyword 'TRANSACTION'.”

第二行提示“One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options. ”

第三行可以执行。但日志log文件没有任何变化。

原来SQL Server 2008　已经不再支持 DUMP TRANSACTION和BACKUP LOG WITH NO_LOG，详情请看

http://msdn.microsoft.com/zh-cn/library/ms187315%28SQL.90%29.aspx
http://msdn.microsoft.com/zh-cn/library/ms186865.aspx　

sql Server 2005说明中明确：包含 DUMP 语句是为了向后兼容。而后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。使用 BACKUP。

SQL Server 2008说明：BACKUP LOG WITH NO_LOG 和 WITH TRUNCATE_ONLY 选项已废止。使用完整恢复模式或大容量日志恢复模式时，如果必须删除数据库中的日志备份链，请切换至简单恢复模式。有关详细信息，请参阅有关从完整恢复模式或大容量日志恢复模式切换的注意事项。

尝试方法二：　　

----Logical Files :
--CMS1.5_Data
--CMS1.5_Log
DBCC SHRINKFILE (N'CMS1.5_Log' , 1)
GO无效。

尝试方法三：　　

use DB_NAME
sp_dboption DB_NAME, "trunc. log on chkpt.", true
checkpoint
sp_dboption DB_NAME, "autoshrink", true

每一行指令请单独执行。其中的DB_NAME是指Database Name，在执行完语法后的数小时至数十小时，该LOG档会逐渐释放空间，最后大约都会维持在数1024KB左右。

有没有更快的方法呢？

尝试方法四：（请提前备份文件！！）

1. Detach数据库。
2.删除log文件。
3. 附加数据库，选移除log文件，此时SQL Server 会自动重新建立一个512K 的Log 文件。

方法五（没有试过，请提前备份文件！！）：

1. 停止 SQL Server 的服务
2. 使用删除 Log文件
3. 重新启动SQL Server 服务，此时SQL Server 会自动重新建立一个1MB 的Log 文件。

方法六： (尘尘提供)

先设置恢复模式为“简单恢复”模式，再收缩：

1	`USE BigData ;GOALTER` `DATABASE` `BigDataSET RECOVERY SIMPLE;--设置简单恢复模式GODBCC SHRINKFILE (BigData_Log, 1);GOALTER DATABASE BigDataSET RECOVERY FULL;--恢复为原模式GO`

方法七： (尘尘提供)