压缩数据库的通用存储过程

*
--
压缩数据库的通用存储过程 



  

 压缩日志及数据库文件大小 

 因为要对数据库进行分离处理 

 所以存储过程不能创建在被压缩的数据库中 




--
邹建 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