How SQL Server database clears logs _mssql2005

Method One:
1, open Query Analyzer, input command
BACKUP LOG database_name with NO_LOG
2, then open Enterprise Manager-right key to compress the database-all tasks-shrink the database-shrink the file-Select the log file-in the contraction method to choose to shrink to XXM, here will give a permit to shrink to the minimum m number, directly enter this number, OK.

Method Two:
Set checkpoints, automatically truncate logs

In general, the contraction of the SQL database does not greatly reduce the size of the database, its primary role is to shrink the log size, you should do this periodically to avoid the database log too large
1, set the database mode to Simple mode: Open SQL Enterprise Manager, in the console root in turn, click the Microsoft SQL Server-->sql Server group--> double hit Open your server--> double-click to open the database directory--> Select your database name (such as user database Cwbase1)--> and then right-click to select Properties--> Select option--> in the failover mode select "Simple" and then press OK to save
2, in the current database point right, look at all tasks in the shrinking database, the general inside the default settings do not need to adjust, direct point to determine
3. After the database is finished, it is recommended that you reset your database properties to standard mode, with the 1th, because the log is often an important basis for restoring the database in some unusual cases.

method Three: Shrink the log through SQL

Copy the code into the Query Analyzer, and then modify the 3 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 manipulate
SELECT @LogicalFileName = ' Tablename_log ',--log file name
@MaxMinutes =--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, @OriginalSize) + ' 8K pages or ' +
CONVERT (VARCHAR (@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 ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR (), size) + ' 8K pages or ' +
CONVERT (VARCHAR (), (size*8/1024)) + ' MB '
From Sysfiles
WHERE name = @LogicalFileName
DROP TABLE Dummytrans
SET NOCOUNT off

method Four: Delete the log file.

This approach is risky because SQL Server log files are not written to the database master file on time, and improper handling can result in loss of data. 1. Disconnect all database connections before operation.
2, separate the database
Detach Database: Enterprise Manager-> Server-> Database->cwbase1-> right key-> Detach database
After separation, the CWBASE1 database is deleted, but the data files and log files are preserved
3, delete log physical files
Delete Log physical file, then attach database: Enterprise Manager-> Server-> Database-> Right key-> attached database
This method generates a new log with a size of more than 500 K.

Note: The first method is recommended. Before you do so, make sure that all operators have rolled out the system and disconnected the database.

Before the above operation, please do a good job of data backup!

1.sql Server 2005 Clear Log statement

Dump TRANSACTION database name with NO_LOG
Backup log database name with NO_LOG
DBCC SHRINKDATABASE (database name)


2.sql Server 2008 Clear Log statement

sp_dboption database name, "trunc." Log on chkpt. ", True
Checkpoint
sp_dboption database name, "AutoShrink", True

How to purge SQL Server database log files:

1. Uninstall this database first:
EXEC sp_detach_db ' database_name ', ' true '
Then delete the log file corresponding to the database;
Finally, register the database inside the system:

EXEC sp_attach_db @dbname = N ' database_name ',
@filename1 = N ' E:\mssql7\data\database_name_data.mdf '

2. Right-click on database-All Tasks-shrink database-Select Shrink file as log.

3. How to clear SQL Server database log:

Here is the forwarded message * * *

The shrinking of log files are 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
The 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 target_percent as possible. Because a log
File can is shrunk only to a virtual log file boundary.
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 would help 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

Forwarding Content End * * *

SQL Server database log cleanup purge sqlserver2005 log

Sometimes when the system is running for a long time, we restore the backup database when we found that the database data files and log files become very large, especially log files. Now I'm going to show you how to clean up SQL Server database logs, and there are two ways to do this:

Method One: Manually clear the sqlserver2005 log

1. Right-click in the database to clear the log, such as "TestDB", clicks [New Query (Q)]
2. Enter the following SQL statement, where "TestDB" is the name of the database
DUMP TRANSACTION TestDB with no_log
3. Execute the SQL, and continue with the following operations successfully
4. Right key to the database node, click [Task (T)]-> [Shrink (S)]-> [File (F)]
5. In the Shrink file dialog box that pops up, select file Type (T) as log, and the Shrink action to select the Rearrange page (O) before freeing unused space
6. In the "Shrink file to (K)" text box, enter the minimum size of the following prompts, click [OK].

Method Two: Use the tool software SQL Server log to clear Expert 3.0, the various versions of SQL Server 6.5 to SQL Server 2005 of the database log cleanup, its use is very simple; SQL Server log cleanup expert green version V3.5 download address:

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

Method One is relatively troublesome, but can be customized to the size of the log, after cleaning up the log of its corresponding database data files will become smaller, the data will not be lost; The method is convenient, the log files in the database can be cleaned to 1M size;