MS SQL收縮資料庫

/*
注意,此存儲過程在建在master數據庫中
*/
Create proc shrinkdb
(
    
@db_name varchar(100)----數據庫名稱

)
as

begin
    
----1.清空日志
    dump transaction @db_name  with  no_log
    
--2.截断事务日志

    backup log @db_name with no_log
    
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小

    dbcc shrinkdatabase(@db_name)
    
--4.设置自动收缩

    exec sp_dboption @db_name,autoshrink,true
end

go

上面過程是改自:
http://www.cnblogs.com/athrun/archive/2007/12/17/1003807.html

通过日志恢复MS SQL数据案例
http://www.cnblogs.com/athrun/archive/2007/10/15/925312.html
http://www.cnblogs.com/athrun/archive/2007/08/01/838383.html

 

1、用BACKUP LOG database WITH NO_LOG清除日志
把数据库属性中的故障还原模型改为“简单”可以大大减慢日志增长的速度。
如果把还原模型调到简单,这样就不支持时间点还原了,但是日志文件会很小,如果数据比较重要推荐还是把数据库的还原模型调为完全.
用BACKUP LOG database WITH NO_LOG命名后,会截断不活动日志,不减小物理日志文件的大小,但逻辑日志会减小,收缩数据库后会把不活动虚拟日志删除来释放空间,不会损坏数据。
如果日志被截断并收缩数据库后,就不能直接用最近的一个全库备份做时间点还原,建议立即备份数据库,以防万一。
2、sql server运行中,是否能删除主数据库事务日志文件
步骤如下:
(1)、分离数据库企业管理器--数据库--右击你要删除日志的数据库--所有任务--分离数据库
(2)、然后删除日志文件
(3)、然后再附加数据库
企业管理器--数据库--右击数据库--所有任务--附加数据库这时候只附加。mdf就可以了。
這個不一定管用,有時間會有問題.
3、压缩SQL数据库及日志的详细方法
SQL Server 2000基础教程——压缩数据库数据库在使用一段时间后,时常会出现因数据删除而造成数据库中空闲空间太多的情况,这时就需要减少分配给数据库文件和事务日志文件的磁盘空间,以免浪费磁盘空间。当数据库中没有数据时,可以修改数据库文件属性直接改变其占用空间,但当数据库中有数据时,这样做会破坏数据库中的数据,因此需要使用压缩的方式来缩减数据库空间。可以在数据库属性选项中选择“Auto shrink”选项,让系统自动压缩数据库,也可以用人工的方法来压缩。人工压缩数据库有以下两种方式:
1、用Enterprise Manager 压缩数据库
在Enterprise Manager 中在所要压缩的数据库上单击右键,从快捷菜单中的“所有任务(All Tasks)”中选择“Shrink Database(压缩数据库)”选项,可以针对每个数据库文件进行不同的压缩设置。並且可以用循环工作计划來執行压缩。设置完成后单击“OK” 按钮就开始压缩数据库,在压缩结束后会显示一个压缩情况信息框。
2、用Transact-SQL 命令压缩数据库
可以使用DBCC SHRINKDATABASE 和DBCC SHRINKFILE 命令来压缩数据库。其中DBCC SHRINKDATABASE 命令对数据库进行压缩,DBCC SHRINKFILE 命令对数据库中指定的文件进行压缩。
(1) DBCC SHRINKDATABASE
DBCC SHRINKDATABASE 命令语法如下:
DBCC SHRINKDATABASE (database_name [, target_percent]
[, {NOTRUNCATE | TRUNCATEONLY}] )
各参数说明如下:
·target_percent 指定将数据库压缩后,未使用的空间占数据库大小的百分之几。如果指定的百分比过大,超过了压缩前未使用空间所占的比例,则数据库不会被压缩。并且压缩后的数据库不能比数据库初始设定的容量小。
·NOTRUECATE
将数据库缩减后剩余的空间保留在数据库,中不返还给操作系统
。如果不选择此选项,则剩余的空间返还给操作系统。
·TRUNCATEONLY
将数据库缩减后剩余的空间返还给操作系统。使用此命令时SQL Server 将文件缩减到最后一个文件分配,区域但不移动任何数据文件。选择此项后,target_percent 选项就无效了。例6-14: 压缩数据库mytest 的未使用空间为数据库大小的20%

dbcc shrinkdatabase (mytest, 20)
运行结果如下:
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.
(2) DBCC SHRINKFILE
DBCC SHRINKFILE 命令压缩当前数据库中的文件。其语法如下:
DBCC SHRINKFILE ( {file_name | file_id }
{ [, target_size] |
[, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}] } )
各参数说明如下:
·file_id
指定要压缩的文件的鉴别号(Identification number, 即ID)
。文件的ID 号可以通过 FILE_ID()函数或如本章前面所讲述
的Sp_helpdb 系统存储过程来得到。
·target_size
指定文件压缩后的大小。以MB 为单位。如果不指定此选项,SQL Server 就会尽最大可能地缩减文件。
·EMPTYFILE
指明此文件不再使用,将移动所有在此文件中的数据到同一文件组中的其它文件中去。执行带此参数的命令后,此文件就可以用ALTER DATABASE 命令来删除了。 其余参数NOTRUNCATE 和TRUNCATEONLY 与DBCC SHRINKDATABASE
命令中的含义相同。

企业管理器里面的方法:
1、打开企业管理器
2、打开要处理的数据库
3、点击最上面菜单>工具>SQL查询分析器,打开SQL查询分析器
4、在输入窗口里面输入:
Code:
DUMP TRANSACTION [数据库名] WITH  NO_LOG
BACKUP LOG [数据库名] WITH NO_LOG
DBCC SHRINKDATABASE([数据库名])

点击绿色的小三角(或按F5)执行查询,等状态栏提示处理完成
即可!

程序里面的方法:
压缩数据库日志
--1.清空日志
exec('DUMP TRANSACTION ['+@dbname+'] WITH  NO_LOG')
--2.截断事务日志:
exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
exec('DBCC SHRINKDATABASE(['+@dbname+'])')

4、减小日志的方法:
一、用如下步做了:
1、DUMP TRANSACTION 庫名 WITH no_log
2、dbcc shrinkfile(logfilename)
3、收縮數據庫
4、設定自動收縮。
二、分离数据库,删除日志文件,再附加,OK!右击数据库--所有任务--分离or 附加
三、1、backup log 庫名 WITH no_log,2、dbcc shrinkfile(logfilename),3、收縮數據庫

 

--压缩日志及数据库文件大小   
    
  
/*
--特别注意   
    
  请按步骤进行,未进行前面的步骤,请不要做后面的步骤   
  否则可能损坏你的数据库.   
    
    
  一般不建议做第4,6两步   
  第4步不安全,有可能损坏数据库或丢失数据   
  第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.   
  --
*/
   
    
  
--下面的所有库名都指你要处理的数据库的库名   

    
  
1
.清空日志   
  
DUMP     TRANSACTION     库名     WITH
     NO_LOG           
    
  
2
.截断事务日志:   
  
BACKUP   LOG   库名   WITH
   NO_LOG   
    
  
3
.收缩数据库文件(如果不压缩,数据库的文件不会减小   
  企业管理器
--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件   

  --选择日志文件--在收缩方式里选择收缩至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)
posted @ 2008-12-31 16:15  Athrun  阅读(614)  评论(0编辑  收藏  举报