Techniques for compressing and removing MSSQL database logs

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)--> 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 to reset your database properties to standard mode, with the 1th, because the log is often the important basis for restoring the database in some unusual cases.

Method Two

SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes int,
@NewSize int
Use Tablen Ame--The database name to be manipulated
SELECT @LogicalFileName = ' Tablename_log ',--log file name
@MaxMinutes = ten,--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 @Original size = 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 nul L)
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 = (Sele CT size from sysfiles WHERE name = @LogicalFileName)
and (@OriginalSize * 8/1024) > @NewSize
BEGIN – out ER loop.
SELECT @Counter = 0
while (@Counter < @OriginalSize/16) and (@Counter < 50000)
BEGIN-UPDA　　　　Te
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, (size*8/1024)) + ' MB '
from sysfiles
WHERE name = @Logical FileName
DROP TABLE dummytrans
SET NOCOUNT off