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

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

::: {#article_dig style="text-align: right"} []{#note85}顶一下 ▲}([]{#sdig85

) 踩一下 ▼}([]{#sdown85

) :::

::: {.previous_content} « 上一篇控制面板的"添加删除程序"的错误 :::

::: {.next_content} 下一篇 » Windows自动登陆的实现

Category: 月光博客2005