SQL2000仅数据文件就197G，如何释放表未占用空间？急

Question

各位大侠：本公司SQL2000仅数据文件就197G，日志采用简单模式，
例：表名 记录数 存储空间   数据占用 索引占用空间 未使用空间
IMAGES 2181686 24464.00mb 1331.00mb  4592.00mb  18542.00mb
 
如何释放这个表的未使用空间 数据库里共有2500张数据表，如何可以批量处理？SQL2000里有什么命令没有？？

Answer 1

197G,2500张?向你学习.参考:*--压缩数据库的通用存储过程 
  
 压缩日志及数据库文件大小 
 因为要对数据库进行分离处理 
 所以存储过程不能创建在被压缩的数据库中 --邹建 2004.03(引用请保留此信息)--*/ /*--调用示例 
 exec p_compdb  'test ' 
--*/ use master  --注意,此存储过程要建在master数据库中 
go if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[p_compdb] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1) 
drop procedure [dbo].[p_compdb] 
GO create proc p_compdb 
@dbname sysname,   --要压缩的数据库名 
@bkdatabase bit=1,   --因为分离日志的步骤中,可能会损坏数据库,所以你可以选择是否自动数据库 
@bkfname nvarchar(260)= ' ' --备份的文件名,如果不指定,自动备份到默认备份目录,备份文件名为:数据库名+日期时间 
as 
--1.清空日志 
exec( 'DUMP TRANSACTION [ '+@dbname+ '] WITH  NO_LOG ') --2.截断事务日志： 
exec( 'BACKUP LOG [ '+@dbname+ '] WITH NO_LOG ') --3.收缩数据库文件(如果不压缩,数据库的文件不会减小 
exec( 'DBCC SHRINKDATABASE([ '+@dbname+ ']) ') --4.设置自动收缩 
exec( 'EXEC sp_dboption  ' ' '+@dbname+ ' ' ', ' 'autoshrink ' ', ' 'TRUE ' ' ') --后面的步骤有一定危险,你可以可以选择是否应该这些步骤 
--5.分离数据库 
if @bkdatabase=1 
begin 
 if isnull(@bkfname, ' ')= ' '  
  set @bkfname=@dbname+ '_ '+convert(varchar,getdate(),112) 
   +replace(convert(varchar,getdate(),108), ': ', ' ') 
 select 提示信息= '备份数据库到SQL 默认备份目录,备份文件名: '+@bkfname 
 exec( 'backup database [ '+@dbname+ '] to disk= ' ' '+@bkfname+ ' ' ' ') 
end --进行分离处理 
create table #t(fname nvarchar(260),type int) 
exec( 'insert into #t select filename,type=status&0x40 from [ '+@dbname+ ']..sysfiles ') 
exec( 'sp_detach_db  ' ' '+@dbname+ ' ' ' ') --删除日志文件 
declare @fname nvarchar(260),@s varchar(8000) 
declare tb cursor local for select fname from #t where type=64 
open tb  
fetch next from tb into @fname 
while @@fetch_status=0 
begin 
 set @s= 'del " '+rtrim(@fname)+ '" ' 
 exec master..xp_cmdshell @s,no_output 
 fetch next from tb into @fname 
end 
close tb 
deallocate tb --附加数据库 
set @s= ' ' 
declare tb cursor local for select fname from #t where type=0 
open tb  
fetch next from tb into @fname 
while @@fetch_status=0 
begin 
 set @s=@s+ ', ' ' '+rtrim(@fname)+ ' ' ' ' 
 fetch next from tb into @fname 
end 
close tb 
deallocate tb 
exec( 'sp_attach_single_file_db  ' ' '+@dbname+ ' ' ' '+@s) 
go 
------------------------------------------------------------------------------------也可在企业管理里收缩日志： 
--收缩数据库 
dbcc shrinkdatabase( '数据库名 ',0,notruncate) 
DUMP TRANSACTION [库名] WITH NO_LOG   
下例将 UserDB 用户数据库中名为 DataFil1 的文件收缩到 7 MB。 USE UserDB 
GO 
DBCC SHRINKFILE (DataFil1, 7) 
下例将 UserDB 用户数据库中的文件减小，以使 UserDB 中的文件有 10% 的可用空间。 DBCC SHRINKDATABASE (UserDB, 10) 
GO 

Answer 2

--最好备份日志，以后可通过日志恢复数据
以下为日志处理方法
一般不建议做第4,6两步
第4步不安全,有可能损坏数据库或丢失数据
第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.
--*/--下面的所有库名都指你要处理的数据库的库名1.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG2.截断事务日志：
BACKUP LOG 库名 WITH NO_LOG3.收缩数据库文件(如果不压缩,数据库的文件不会减小
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了也可以用SQL语句来完成 
--收缩数据库
DBCC SHRINKDATABASE(库名)--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles
DBCC SHRINKFILE(1)4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)
a.分离数据库:
企业管理器--服务器--数据库--右键--分离数据库b.在我的电脑中删除LOG文件c.附加数据库:
企业管理器--服务器--数据库--右键--附加数据库此法将生成新的LOG，大小只有500多K或用代码： 
下面的示例分离 pubs，然后将 pubs 中的一个文件附加到当前服务器。a.分离
EXEC sp_detach_db @dbname = '库名'b.删除日志文件c.再附加
EXEC sp_attach_single_file_db @dbname = '库名', 
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\库名.mdf'5.为了以后能自动收缩,做如下设置:
企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"--SQL语句设置方式:
EXEC sp_dboption '库名', 'autoshrink', 'TRUE'6.如果想以后不让它日志增长得太大
企业管理器--服务器--右键数据库--属性--事务日志
--将文件增长限制为xM(x是你允许的最大数据文件大小)--SQL语句的设置方式:
alter database 库名 modify file(name=逻辑文件名,maxsize=20)--首先,用下面的语句,检查/修复数据库/并整理索引碎片(需要比较长的时间,请在系统空闲时进行此工作)   
    
  USE   MASTER   
  GO   
    
  sp_dboption   '你的数据库名',   'single   user',   'true'   
  Go   
    
  DBCC   CHECKDB('你的数据库名',   REPAIR_REBUILD)     
  Go   
    
  USE   你的数据库名   
  go   
    
  exec   sp_msforeachtable   'DBCC   CHECKTABLE(''?'',REPAIR_REBUILD)'   
  exec   sp_msforeachtable   'DBCC   DBREINDEX(''?'')'   
  go   
    
  sp_dboption   '你的数据库名',   'single   user',   'false'   
  Go  
--然后用这个压缩数据库日志文件.
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT--Select * From Sysfiles
USE YSERP -- 要操作的数据库名
SELECT @LogicalFileName =  'YSERP_Log' , -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 20 -- 你想设定的日志文件的大小(M)-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT  ' Original Size of  '  + db_name() +  '  LOG is  '  +
CONVERT(VARCHAR(30),@OriginalSize) +  '  8K pages or  '  +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) +  ' MB ' 
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog =  ' BACKUP LOG  '  + db_name() +  '  WITH TRUNCATE_ONLY ' DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not
/*expired*/ AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =@LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ( ' Fill Log ' )
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT  ' Final Size of  '  + db_name() +  '  LOG is  '  +
CONVERT(VARCHAR(30),size) +  '  8K pages or  '  +
CONVERT(VARCHAR(30),(size*8/1024)) +  ' MB ' 
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF--其次是压缩主数据文件企业管理器->所有任务->压缩数据库->压缩文件->文件->压缩到最终大小->输入最小值即可 --最好备份日志，以后可通过日志恢复数据
以下为日志处理方法
一般不建议做第4,6两步
第4步不安全,有可能损坏数据库或丢失数据
第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.
--*/--下面的所有库名都指你要处理的数据库的库名1.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG2.截断事务日志：
BACKUP LOG 库名 WITH NO_LOG3.收缩数据库文件(如果不压缩,数据库的文件不会减小
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了也可以用SQL语句来完成 
--收缩数据库
DBCC SHRINKDATABASE(库名)--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles
DBCC SHRINKFILE(1)4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)
a.分离数据库:
企业管理器--服务器--数据库--右键--分离数据库b.在我的电脑中删除LOG文件c.附加数据库:
企业管理器--服务器--数据库--右键--附加数据库此法将生成新的LOG，大小只有500多K或用代码： 
下面的示例分离 pubs，然后将 pubs 中的一个文件附加到当前服务器。a.分离
EXEC sp_detach_db @dbname = '库名'b.删除日志文件c.再附加
EXEC sp_attach_single_file_db @dbname = '库名', 
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\库名.mdf'5.为了以后能自动收缩,做如下设置:
企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"--SQL语句设置方式:
EXEC sp_dboption '库名', 'autoshrink', 'TRUE'6.如果想以后不让它日志增长得太大
企业管理器--服务器--右键数据库--属性--事务日志
--将文件增长限制为xM(x是你允许的最大数据文件大小)--SQL语句的设置方式:
alter database 库名 modify file(name=逻辑文件名,maxsize=20)

Answer 3

收缩数据文件。DBCC SHRINKFILE (文件逻辑名称, 指定文件大小);
数据库太大，估计收缩的时间需要很长。

Answer 4

如果已经收缩了MDF,NDF还那么大
数据文件本身收缩的空间并不大了
能收缩的空间也就是日志文件了
这样可以通过转移历史数据
或者在不同的磁盘建立数据文件
放到不同的文件组将大容量的表转移到这些文件组
以提高文件的读写效率
PS：仅为个人观点

Answer 5

Images 这个表有 clustered index 吗? 有就重建它，没就建一个。然後执行收缩数据库，这个表应该会减掉18G左右的空间

Answer 6

十分感谢各位大侠的回答，但是因为是在线实时使用的数据库，业务量非常繁忙，不敢轻易实施分离，附加这种步骤的。呵呵，
针对单表的释放空间是不是只有 Garnett_KG这位大侠的方法呢？Images 这个表有 clustered index 吗? 有就重建它，没就建一个。然後执行收缩数据库，这个表应该会减掉18G左右的空间还有没有其他方法。

Answer 7

重建数据库，然后附加MDF，然后把某个时间段的数据全部删除。