清除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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值