SQL Server method of clearing logs

MS SQL Server logs grow very fast, after a long time, log files will be large, take up a lot of hard disk space, so you need to periodically clear the log, you can use the following four ways:

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!