SQL Server Shrink log empty Delete large log file

SQL2008 's Shrink log
Because SQL2008 is optimized for file and log management, the following statements can be run in SQL2005 but have been canceled in SQL2008:

(SQL2000)

--Prog:xinsoft
--Time:2005-03-26 10:34

SET NOCOUNT on
DECLARE @LogicalFileName sysname, @MaxMinutes int, @NewSize int

Use Dbjiexin--the name of the database to manipulate
SELECT @LogicalFileName = ' Dbjiexin_log ',--log file name
@MaxMinutes = ten,--Limit on time allowed to wrap log.
@NewSize = 10-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 (varch AR (+), (@OriginalSize *8/1024)) + ' MB ' from sysfiles WHERE name = @LogicalFileName
--drop TABLE Dummytrans
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 ()) and @OriginalSize = (SELECT size from sysfiles WHERE name = @l Ogicalfilename) 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 (), Size) + ' 8K pages or ' + CONVERT (varchar (), (size *8/1024)) + ' MB ' from sysfiles WHERE name = @LogicalFileName
DROP TABLE Dummytrans
SET NOCOUNT OFF

-------------------------------

Use Dbjiexin

Backup LOG dbjiexin with NO_LOG

Dump Tran Dbjiexin with NO_LOG

DBCC SHRINKDATABASE (dbjiexin)

--------------------------------------------------------------
(SQL2005)
Backup LogDnnameWithno_log
Go
Dump TransactionDnnameWithno_log
Go
UseDnname
DbccShrinkfile (2)
Go
--------------------------------------------------------------
(SQL2008):
Clearing the log in SQL2008 must be done in simple mode, and then back to full mode when the purge is complete.
Use [Master]
GO
Alter DATABASEDnnameSETRECOVERY SimpleWithno_wait
GO
Alter DATABASEDnnameSETRECOVERY Simple--Simple Mode
GO
UseDnname
GO
DbccShrinkfile (N' Dnname_log ' , One, Truncateonly)
GO
Use [Master]
GO
Alter DATABASEDnnameSETRECOVERYFull Withno_wait
GO
Alter DATABASEDnnameSETRECOVERYFull  --Revert to full mode
GO

Advantages: This cleanup log runs short of time, 90GB of log can be cleared in minutes or so, after doing a full backup in minutes
can be completed.
Cons: But this action is best not to be used frequently, as it will result in system fragmentation. Log and diff backups can be truncated in normal state.
The appropriate environment for this statement is used when the system's log file abnormally increases or the backup log time is too long to affect production.

SQL Server Shrink log empty Delete large log file