Two ways to clear the SQL Server log

Method One

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 Forum Database Forum)--> 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 Two

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