Two Ways to Clear the SQL Server Log

 

by Julie Seymour · February 23, 2011

In the course of using the SQL Server database, we often encounter the case of very large logs from time to time. In this tutorial, we give you about the two methods to resolve.

Method One

Under normal circumstances, SQL database and can not shrink the size of a large extent reduce the database, and its main role is to shrink the log size, should do this periodically to avoid excessive database log

1, set the database mode to simple mode: Open the SQL Enterprise Manager, the Console Root, in turn opening the Microsoft SQL Server -> SQL Server Group -> double click to open your server -> double click to open database directory – > select your database name (such as Forum Database Forum) -> then right click and select Properties -> Options -> reduction in the failure mode, select “Simple”, then press OK to save

2, right click on the current database to see all the tasks in the contraction of the database, generally do not adjust the default settings which directly point to determine

3, the contraction of the database is complete, it is recommended that you reset the database properties as the standard model, methods of operation with the first point, because the log in some exceptional cases are often an important basis for the restoration of the database
Method Two

SET NOCOUNT ON

DECLARE @ LogicalFileName sysname,

@ MaxMinutes INT,

@ NewSize INT

USE tablename – name of the database to operate

SELECT @ LogicalFileName = ‘tablename_log’, – log file name

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

@ NewSize = 1 – you want to set the log file size (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

Leave a Reply