青青子衿, 悠悠我心, 但为君故, 沉吟至今
« 控制面板的“添加删除程序”的错误WINDOWS NT 自动登陆的实现 »

清除SQLSERVER数据库日志的方法

SQLSERVER的数据库日志占用很大的空间,下面提供三种方法用于清除无用的数据库日志文件。

清除SQLSERVER数据库日志文件的方法:

1、先将这个数据库卸载:
EXEC sp_detach_db 'database_name', 'true'
然后将该数据库所对应的Log文件删掉;
最后,再将这个数据库注册到系统里面:
EXEC sp_attach_db @dbname = N'database_name',
@filename1 = N'e:\mssql7\data\database_name_data.mdf'

2、数据库上点右键-所有任务-收缩数据库-选择收缩文件为LOG 。


3、清除SQLSERVER数据库日志的方法:

*******下面是转发的邮件*****

The shrinking of log files is not immediate in SQL Server 7.0. The
shrinking of log files does not occur until the active portion of the
log moves. As updates are performed on the database, the shrink
operation occurs at checkpoints or transaction log backups. Each log
file is marked with the target_percent for the shrink operation. Each
subsequent log backup or log truncation attempts to shrink the file to
bring its size as close to the target_percent as possible. Because a log
file can be shrunk only to a virtual log file boundary, it may not be
possible to shrink a log file to a size smaller than the size of a
virtual log file even if it is not being used. Please refer to SQL Book
Online for the details.

RESOLUTION

Below script will help to shrink the log file immediately, pls keep it
running for 3~4 minutes and then stop it manually.

\* Run "select fileid, name,filename from ..sysfiles" to get
the fileid which you want to shrink *\

use
go
dbcc shrinkfile(fileid,notruncate)
dbcc shrinkfile(fileid,truncateonly)
create table t1 (char1 char(4000))
go
declare @i int
select @i = 0
while (1 = 1)
begin
 while (@i < 100)
 begin
   insert into t1 values ('a') select @i = @i +1
 end
 truncate table t1
 backup log with truncate_only
end
go

*****转发内容结束*****



  除非注明,月光博客文章均为原创,转载请以链接形式标明本文地址

  本文地址:http://www.williamlong.info/archives/85.html
  • 相关文章:
    • 文章排行:
    • 1.KEVIN
    • 其实用MSSQL语句就能实现 .
      dump transction "database" with no_log
      然后再收缩数据库
    • 2009/9/4 15:16:40   支持(5)反对(14) 回复

    发表评论:

     请勿发送垃圾信息、广告、推广信息或链接,这样的信息将会被直接删除。

    订阅博客

    • 订阅我的博客:订阅我的博客
    • 关注新浪微博:关注新浪微博
    • 关注腾讯微博:关注腾讯微博
    • 关注认证空间:关注QQ空间
    • 通过电子邮件订阅
    • 通过QQ邮件订阅

    站内搜索

    相关文章

    热文排行


    月度排行

    本站采用创作共用版权协议, 要求署名、非商业用途和相同方式共享. 转载本站内容必须也遵循“署名-非商业用途-相同方式共享”的创作共用协议.
    This site is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License.