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


    有时候sql server 2008 数据库日志文件太大，需要收缩释放硬盘内存。如果ldb文件过大会导致数据库运行缓慢，甚至系统都会卡住。
    1.登陆项目平台数据库服务器。双击SQL Server Management Studio打开数据库管理。登陆数据库
    
    2.如下图，打开数据库属性窗口
    
    3.如下图，更改数据库恢复模式
    
    4.如下图，收缩数据库日志
    
    
    到这里已经完成了，数据库的日志收缩
    5.如下图，数据库恢复模式修改为完整
    
    经过电脑手机教程网小编测试，完美解决，我们成功的把一个84G的文件，压缩到1M。
    下面继续为大家分享一个通过sql语句实现的，每次手工操作麻烦有没有。
    sql语句实现步骤如下
    首先查找要收缩日志文件的数据库文件名
    USE A
    GO
    SELECT file_id, name FROM sys.database_files;
    GO
    
    查询结果得到日志文件的文件名叫J4_log
    不过电脑手机教程网小编测试查询比较慢，可以通过下面的方法
    数据库属性>文件>右侧日志前面这个名字就是日志文件名了
    
    测试完美没有异常。
    
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地址
    这样就可以了，以后新建一个查询，直接运行就可以了。
    
    
    下面的每经过测试，而且明显因为版本不同，不一定能使用。
    在压缩数据之前建议大家看下这篇文章：//www.jb51.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 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 文件。
    方法六： (尘尘提供)
    先设置恢复模式为“简单恢复”模式，再收缩：
    
USE BigData ;GOALTER DATABASE BigDataSET RECOVERY SIMPLE;--设置简单恢复模式GODBCC SHRINKFILE (BigData_Log, 1);GOALTER DATABASE BigDataSET RECOVERY FULL;--恢复为原模式GO

    方法七： (尘尘提供)
    
USE BigData;GOBACKUP 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真的没有压缩的必要了，具体原因可以参考这篇文章。
    
    前言，这几天查看了很多关于SQL SERVER收缩数据文件方面的文章，准备写一篇关于收缩日志方面的文章，但是突然有种冲动将看过经典的文章翻译出来，下面这篇文章是翻译的是Paul Randal – “Why You Should Not Shrink Your Data Files”。有些比较难以翻译、清晰的地方，我会贴上原文。好了，不啰嗦了，直接看下面的翻译吧。 
      我最大的一个热点问题是关于收缩数据文件，虽然在微软的时候，我自己写了相关收缩数据文件代码，我再也没有机会去重写它，让它操作起来更方便。我真的不喜欢收缩。
      现在，不要混淆了收缩事务日志文件和收缩数据文件，当事务日志文件的增长失控或为了移除过多的VLF碎片（这里和这里看到金佰利的优秀文章），然而，收缩事务日志数据文件不要频繁使用（罕见的操作）并且不应是你执行定期维护计划的一部分。
      收缩数据文件应该执行得甚至更少。这就是为什么——数据文件收缩导致产生了大量索引碎片，让我用一个简单并且你可以运行的脚步来演示。下面的脚本将会创建一个数据文件，创建一个10MB大小的“filler”表，一个10MB大小的“production”聚簇索引，然后分析新建的聚集索引的碎片情况。 
    
USE [master];
GO
 
IF DATABASEPROPERTYEX(N'DBMaint2008', N'Version') IS NOT NULL
  DROP DATABASE [DBMaint2008];
GO
 
CREATE DATABASE DBMaint2008;
GO
USE [DBMaint2008];
GO
 
SET NOCOUNT ON;
GO
 
-- Create the 10MB filler table at the 'front' of the data file
CREATE TABLE [FillerTable](
  [c1] INT IDENTITY,
  [c2] CHAR (8000) DEFAULT 'filler');
GO
 
-- Fill up the filler table
INSERT INTO [FillerTable] DEFAULT VALUES;
GO 1280
 
-- Create the production table, which will be 'after' the filler table in the data file
CREATE TABLE [ProdTable](
  [c1] INT IDENTITY,
  [c2] CHAR (8000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable]([c1]);
GO
 
INSERT INTO [ProdTable] DEFAULT VALUES;
GO 1280
 
-- Check the fragmentation of the production table
SELECT
  [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats(
  DB_ID(N'DBMaint2008'), OBJECT_ID(N'ProdTable'), 1, NULL, 'LIMITED');
GO

    执行结果如下
    
    聚集索引的逻辑碎片在收缩数据文件前大约接近0.4%。[但是我测试结果是0.54%，如上图所示,不过也算是接近0.4%]
    现在我删除filter表，运行收缩数据文件命令后，重新分析聚集索引的碎片化。
    
-- Drop the filler table, creating 10MB of free space at the 'front' of the data file
DROP TABLE [FillerTable];
GO
 
-- Shrink the database
DBCC SHRINKDATABASE([DBMaint2008]);
GO
 
-- Check the index fragmentation again
SELECT
  [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats(
  DB_ID(N'DBMaint2008'), OBJECT_ID(N'ProdTable'), 1, NULL, 'LIMITED');
GO

    下面是我的执行结果，作者执行结果，请看原文：
    
    原文：
    Wow! After the shrink, the logical fragmentation is almost 100%. The shrink operation *completely* fragmented the index, removing any chance of efficient range scans on it by ensuring the all range-scan readahead I/Os will be single-page I/Os.
    译文：
    哇，真是恐怖！数据文件收缩后，索引的逻辑碎片几乎接近100%，收缩数据文件导致了索引的完全碎片化。消除了任何关于它的有效范围扫描的机会，确保所有执行提前读范围扫描的 I/O 在单页的 I/O操作
    为什么会这样呢？ 当单个数据文件收缩操作一次后，它会用GAM位图索引找出数据文件中分配最高的页，然后尽可能的向前移动到文件能够移动的地方，就这样子，在上面的例子中，它完全反转了聚集索引，让它从非碎片化到完全碎片化。
    同样的代码用于DBCC SHRINKFILE， DBCC SHRINKDATABASE，以及自动收缩，他们同样糟糕，就像索引的碎片化，数据文件的收缩同样产生了大量的I/O操作，耗费大量的CPU资源，并且生成了*load*事务日志，因为任何操作都会全部记录下来。
    数据文件收缩决不能作为定期维护的一部分，你决不能启用“自动收缩”属性，我尝试把它从SQL 2005和SQL 2008产品中移除，它还存在的唯一原因是为了更好的向前兼容，不要掉入这样的陷阱：创建一个维护计划，重新生成所有索引，然后尝试回收重建索引耗费的空间采取收缩数据文件 — — 这就是你做的生成了大量事务日志，但实质没有提高性能的零和游戏。
    所以，你为什么要运行一个收缩呢，？举例来说，如果你把一个相当大的数据库删除了相当大的比例，该数据库不太可能增长，或者你需要转移一个数据库文件前先清空数据文件？
    译文：
    我很想推荐的方法如下：
    创建一个新的文件组
    将所有受影响的表和索引移动到一个新的文件组用CREATE INDEX ... WITH (DROP_EXISTING=ON)的脚本，在移动表的同时，删除表中的碎片。
    删掉那些你准备收缩的旧文件组，你反正要收缩（或缩小它的方式下来，如果它的主文件组）。
    基本上你需要提供一些更多的空间，才可以收缩的旧文件，但它是一个更清晰的设置。
    原文：
    The method I like to recommend is as follows:
    Create a new filegroup
    Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
    Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
    Basically you need to provision some more space before you can shrink the old files, but it's a much cleaner mechanism.
    如果你完全没有选择需要收缩日志文件，请注意这个操作会导致索引的碎片化，你应该在收缩数据文件采取一些步骤消除它可能导致的性能问题，唯一的方式是用DBCC INDEXDEFPAGE或 ALTER INDEX ...REORGANIZE消除索引的碎片不要引起数据文件的增长,这些命令要求扩展空间8KB的页代替重建一个新的索引在索引重建操作中。
    底线 — — 尽量避免不惜一切代价运行数据文件收缩
    所以，还在用作业定期收缩数据文件或数据库开启了“自动收缩”属性的朋友们，请及时纠正你们的错误认识吧！
    支持原著，也希望大家支持我辛苦的翻译劳动，请加上链接潇湘隐者博客。
    
相关文章！