首页 | 服务器 | 数据库 | MsSql | MsSql2008

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

2017年11月30日 作者：mdxy-dxy

在压缩数据之前建议大家看下这篇文章：//www.laike.net/article/136522.htm

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

第一种方法：通过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）生成数据表压缩脚本。

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 A
JOIN
(SELECT C.name + ''.'' + A.name AS TABLENAME, object_id FROM ## A
JOIN sys.objects B
ON A.name = B.name
JOIN sys.schemas C
ON B.schema_id = C.schema_id
WHERE CAST(data AS int) > 10000000 AND object_id IN (SELECT object_id FROM sys.tables)) B
ON 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 文件。

方法六： (尘尘提供)

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

USE BigData ;
GO
ALTER DATABASE BigData
SET RECOVERY SIMPLE;--设置简单恢复模式
GO
DBCC SHRINKFILE (BigData_Log, 1);
GO
ALTER DATABASE BigData
SET RECOVERY FULL;--恢复为原模式
GO

方法七： (尘尘提供)

USE BigData;
GO
BACKUP LOG DATABASENAME TO DISK='d:test.bak'
-- Shrink the trun cated log file to 1 MB.
DBCC SHRINKFILE (Bigdata_Log, 1);
GO

到这关于sqlserver 2008 数据压缩的方法就介绍到这了，据小编多年经验来看，2000、2005确实需要压缩，但2008真的没有压缩的必要了，具体原因可以参考这篇文章。

相关链接