Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the methods of excessive cleaning of database log files". In daily operations, I believe that many people have doubts about the methods of excessive cleaning of database log files. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for everyone to answer the doubts about "what are the methods of cleaning database log files too large?" Next, please follow the editor to study!

Method 1:

Backup Log DBname with no_log

Go

Dump transaction DBname with no_log

Go

USE DBname

DBCC SHRINKFILE (2)

Go

Method 2:

Set checkpoints and automatically truncate logs

In general, the shrinking of the SQL database can not greatly reduce the size of the database. Its main function is to reduce the size of the log. This operation should be done regularly to prevent the database log from being too large.

1. Set the database mode to simple mode: open SQL Enterprise Manager and click Microsoft SQL Server-- > SQL Server group in the console root directory-- > double-click to open your server-- > double-click to open the database directory-- > Select your database name (such as user database cwbase1)-- > then right-click to select Properties-- > Select options-- > Select "simple" in the failure recovery mode. Then press OK to save

2. Right-click on the current database to see the shrinking database in all tasks. Generally, the default settings do not need to be adjusted. Click OK directly.

3. After shrinking the database, it is recommended to reset your database properties to standard mode. The operation method is the same as the first point, because logs are often an important basis for restoring the database in some abnormal cases.

Method 3: shrink the log through SQL

Copy the code into the query analyzer, and then modify three parameters (database name, log file name, and target log file size) to run.

SET NOCOUNT ON

DECLARE @ LogicalFileName sysname

@ MaxMinutes INT

@ NewSize INT

USE tablename-the name of the database to be operated on

SELECT @ LogicalFileName = 'tablename_log',-- log file name

MaxMinutes = 10,-- Limit on time allowed to wrap log.

@ NewSize = 1-the size of the log file you want to set (M)

-- Setup / initialize

DECLARE @ OriginalSize int

SELECT @ OriginalSize = size

FROM sysfiles

WHERE name = @ LogicalFileName

SELECT 'Original Size of' + db_name () + 'LOG is' +

CONVERT (VARCHAR (30), @ OriginalSize) +'8K pages or'+

CONVERT (VARCHAR (30), (@ OriginalSize*8/1024)) + 'MB'

FROM sysfiles

WHERE name = @ LogicalFileName

CREATE TABLE DummyTrans

(DummyColumn char (8000) not null)

DECLARE @ Counter INT

@ StartTime DATETIME

@ TruncLog VARCHAR (255)

SELECT @ StartTime = GETDATE ()

@ TruncLog = 'BACKUP LOG' + db_name () + 'WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@ LogicalFileName, @ NewSize)

EXEC (@ TruncLog)

-- Wrap the log if necessary.

WHILE @ MaxMinutes > DATEDIFF (mi, @ StartTime, GETDATE ())-time has not expired

AND @ OriginalSize = (SELECT size FROM sysfiles WHERE name = @ LogicalFileName)

AND (@ OriginalSize * 8 / 1024) > @ NewSize

BEGIN-- Outer loop.

SELECT @ Counter = 0

WHILE ((@ Counter)

< @OriginalSize / 16) AND (@Counter < 50000)) BEGIN -- update INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans SELECT @Counter = @Counter + 1 END EXEC (@TruncLog) END SELECT 'Final Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),size) + ' 8K pages or ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF 方法四：删除日志文件。 此方法有一定的风险性，因为sql server的日志文件不是即时写入数据库主文件的，如处理不当，会造成数据的损失。1、操作前请断开所有数据库连接。 2、分离数据库 分离数据库：企业管理器－＞服务器－＞数据库－＞cwbase1－＞右键－＞分离数据库 分离后，cwbase1数据库被删除，但保留了数据文件和日志文件 3、删除log物理文件 删除LOG物理文件，然后附加数据库： 企业管理器－＞服务器－＞数据库－＞右键－＞附加数据库 此法生成新的log，大小只有500多k。 注意：建议使用第一种方法。操作前请确保所有操作员都已经推出系统，断开数据库的连接。 以上操作前，请务必做好数据备份！ 1.sql server 2005 清除日志语句 dump transaction 数据库名称 with no_log backup log 数据库名称 with no_log dbcc shrinkdatabase(数据库名称) 2.sql server 2008 清除日志语句 sp_dboption 数据库名称, "trunc. log on chkpt.", true checkpoint sp_dboption 数据库名称, "autoshrink", true 清除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 Belowscript 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 *****转发内容结束***** SQLServer数据库日志清理 清除sqlserver2005日志 有时候当系统运行时间比较长的时候，我们把备份的数据库还原的时候发现，数据库中数据文件和日志文件变的好大，特别是日志文件。现在给大家介绍如何清理SQLServer数据库日志；有两种方法如下： 方法一：手动清除sqlserver2005日志 １.右键在清除日志的数据库，如"TestDB"，点击[新建查询（Q）] ２.输入以下SQL语句，其中"TestDB"是数据库名称 DUMP TRANSACTION TestDB WITH NO_LOG ３.执行该SQL，成功后继续以下操作 ４.右键该数据库节点，点击[任务（T）] ->

[shrink (S)]-> [file (F)]

5. In the pop-up shrink File dialog box, select Files of Type (T) as Log and shrink Operations to select reorganize pages before releasing unused space.

6. Enter the value of the minimum size prompted later in the "shrink the file to (K)" text box, and click OK.

Method 2: use the tool software SqlServer log cleanup expert 3.0to clear database logs of various versions from SqlServer 6.5 to SqlServer 2005; the method is very simple; SqlServer log cleanup expert green version V3.5 download address:

Download address http://www.jb51.net/softs/21840.html

Method 1 is relatively troublesome to operate, but the size of the log can be customized. after cleaning the log, the corresponding database data files will become smaller and the data will not be lost; method 2 is more convenient to operate. You can clean up the log files in the database to 1m size

At this point, the study of "what are the methods of cleaning up database log files too large" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.