SQL Server 2005 数据压缩 表保留空间的问题

Question

大虾们好，本人某小公司兼职DBA，公司有个数据库SQL Server 2005已经运行6个月左右，数据库大小飙升至200G，请大虾们建议下如何对其进行压缩 . 我查看了下所有表大小 ，create table #t(name varchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))
exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'" 
select * from #t order by data desc
drop table #t
--运行结果
name      rows       reserved      data        index_size   unused
tablename 105656928  256008704 KB  71470696 KB 182230512 KB 2307496 KB发现reserved表保留空间很大， 请问大虾们 这个应当如何处理? 可以释放次空间吗？在线等待，谢谢！

Answer 1

请问是否备份后，直接使用
DBCC SHRINKDATABASE 
    ( database_name [ , target_percent ] 
        [ , { NOTRUNCATE | TRUNCATEONLY } ] 
    ) 
收缩就行了？ 我已经在属性设置好了自动收缩=true，可能以前的同事当初没有选这个。谢谢

Answer 2

--11.收缩数据库--1.自动收缩数据库：将AUTO_SHRINK 数据库选项设置为ON 后，数据库引擎将自动收缩具有可用空间的数据库。作用相当于DBCC SHRINKDATABASE(dbname,25).默认情况下，此选项设置为OFF。--2.手动收缩数据库：可以使用DBCC SHRINKDATABASE 语句或DBCC SHRINKFILE 语句来手动收缩数据库或数据库中的文件。/*1.在该过程中任意时间都可停止DBCC SHRINKDATABASE 和DBCC SHRINKFILE 操作，所有已完成工作都将保留。  2.在使用DBCC SHRINKDATABASE 语句时，您无法将整个数据库收缩得比其初始大小更小。  3.使用DBCC SHRINKFILE 语句时，可以将各个数据库文件收缩得比其初始大小更小。必须对每个文件分别进行收缩，而不能尝试收缩整个数据库。*/DBCC SHRINKDATABASE ( database_name | database_id | 0  [ , target_percent ]  [ , { NOTRUNCATE | TRUNCATEONLY } ] )[ WITH NO_INFOMSGS ]会收缩一个数据库的所有文件.数据库不能收缩到比model数据库还小,并且DBCC SHRINKDATABASE 也不能将一个文件收缩到比其最小的大小还小.其中target_percent参数是数据库中每个文件所保留的自由空间的百分比.比如:如果我用 DBCC SHRINKDATABASE(dbname,25) 其中一个总大小为MB文件的其中MB的数据库文件,可以保留%的可用空间,那么新的文件 大小为MB。怎么算出来的呢？x*1.0/(x+60)=25% x=20 所以总的大小为用掉的MB 加上空闲的MB 一共为MB。 如果文件是用掉了MB,那收缩还剩多少呢？x*1.0/(x+80)=25% x>20 注意这里x+80>100 这样超过了总大小,所以这个时候改文件不发生收缩,保持原来的大小.    所以整个数据库就是以这个方式逐个收缩数据库.        DBCC SHRINKFILE ({ file_name | file_id } { [ , EMPTYFILE ] | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]})[ WITH NO_INFOMSGS ]其中的target_size 用兆字节表示的文件大小（用整数表示）。如果未指定，则 DBCC SHRINKFILE 将文件大小减少到默认文件大小。默认大小为创建文件时指定的大小。这里只要注意不能将文件收缩到比存储数据所需空间还小.例如：你的数据库文件一共MB，用了MB，那么你只能收缩到>=8MB的大小,即使你target_size=7mb,最后文件大小还是MB 你也许会发现这里有几个选项可选：1.NOTRUNCATE:在指定或不指定 target_percent 的情况下，将已分配的页从数据文件的末尾移动到该文件前面的未分配页。文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。因此，指定 NOTRUNCATE 时，文件看起来未收缩。NOTRUNCATE 只适用于数据文件。日志文件不受影响。2.TRUNCATEONLY:将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。数据文件只收缩到最后分配的区。如果随 TRUNCATEONLY 指定了 target_size，则会忽略该参数。TRUNCATEONLY 只适用于数据文件。3.EMPTYFILE:将指定文件中的所有数据迁移到同一文件组中的其他文件。只适合DBCC SHRINKDATABASE --例子:截断数据库DBCC SHRINKDATABASE (dbname, TRUNCATEONLY);--例子:将数据文件收缩到指定的目标大小DBCC SHRINKFILE (dbname, 7);--例子:将日志文件收缩到指定的目标大小-- 将数据库的恢复模式设置为SIMPLEALTER DATABASE dbnameSET RECOVERY SIMPLE;GO-- 收缩你的日志文件到MBDBCC SHRINKFILE (dbname_log,3);GO--重新设置回你的恢复模式ALTER DATABASE AdventureWorksSET RECOVERY FULL;GO--例子:清空文件-- 首先创建一个文件(举例而已)ALTER DATABASE AdventureWorks ADD FILE (NAME = Test1data,FILENAME = 'C:\t1data.ndf',SIZE = 5MB);GO-- 转移你的数据库文件到该文件组的其他文件DBCC SHRINKFILE (Test1data, EMPTYFILE);GO-- 移除你的数据库文件ALTER DATABASE AdventureWorksREMOVE FILE Test1data;GO

Answer 3

使用shrikfile shrikdatabase 收缩数据库
如果是日志文件收缩,先日志备份，然后进行文件收缩.

Answer 4

http://www.hackhome.com/InfoView/Article_202334.html这个文章可能对你有帮助 

Answer 5

收缩不掉的，你的数据+索引占去了250G左右的空间。考虑将一些历史数据保存到别处吧，好好规划做一下data archive.

Answer 6

谢谢上面大虾们， 那个表已经做了archive ，仅仅保存半年的数据，其他都移到其他表去了，那个表的确是需要存储那么大的数据...  我得认真需要检查下索引，的确占据了很多空间。 请问假如迁移到SQL Server 2008是否 可以对其进行压缩？压缩的效果？ 谢谢！

Answer 7

看你的表的结构如何了。你可以用sp_estimate_data_compression_savings看看预估能节省多少空间。