* -- 压缩数据库的通用存储过程
压缩日志及数据库文件大小
因为要对数据库进行分离处理
所以存储过程不能创建在被压缩的数据库中
-- 邹建 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
压缩数据库的通用存储过程
最新推荐文章于 2024-09-09 09:47:53 发布