2012年5月21日星期一

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

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

没有评论:

发表评论