Clean up the SQL Server 2008 database log

The first step, before shrinking to check the size of the log:

SELECT *

FROM sysfiles

WHERE name LIKE ‘% LOG%’

GO

The second step, the database recovery mode is set to “simple”:

ALTER DATABASE database name SET RECOVERY Simple

GO

The third step is to run the checkpoint command, the dirty page into the database:

CHECKPOINT

GO

The fourth step is to truncate the log:

BACKUP The LOG database name WITH NO_LOG

GO

The fifth step, recorded the log name is the next step to prepare:

SELECT NAME

FROM sysfiles

WHERE name LIKE ‘% LOG’

GO

The sixth step, shrink the log file, release the unused space to the operating system:

DBCC SHRINKFILE (file name, size required)

GO

The seventh step is to verify whether the log size to achieve the desired size:

SELECT *

FROM sysfiles

WHERE name LIKE ‘% LOG%’

GO

Next Steps:

1) View your main database, look at the log growth is out of control;

2) See the above code, and make the necessary modifications and testing to fit your database requirements;

3) periodic shrinking database —- (Note: with caution!! Not recommended to use in a production environment.)

4) continue to monitor the amount of free space on the size of the database and the server.