Shrink SQL Server log files

Server SQL Server 2000 will have log files because of the growing problem of time accumulation: The actual size of the database is 15M, the log file size is 625KB (the exported log file), but the log file actually occupies 200MB (the default setting is that the file log will grow automatically).
If you want to change the storage space of the current log file directly from the database properties, it will not work. Workaround:
Find the following code to shrink the log file to the size you want it to be. Copy the code to Query Analyzer, and then modify the 3 parameters (database name, log file name, and target log file size), run it!

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

Use Gfcms-the name of the database to manipulate
SELECT @LogicalFileName = ' Gfcms_log ',--log file name
@MaxMinutes =--Limit on time allowed to wrap log.
@NewSize = 100-the size of the log file you want to set (M), note that this size must be smaller than the actual file size

--Setup/initialize
--Get the original file size
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 '

EXEC (@TruncLog)--mark the log of the transaction that can be shrink in the log as clear
DBCC shrinkfile (@LogicalFileName, @NewSize)--shrink files

--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

Detailed explanation: The key statements are: ' BACKUP log ' + db_name () + ' with TRUNCATE_ONLY ' and DBCC SHRINKFILE (@LogicalFileName, @NewSize) ' Backup log ' + db_name () + ' with TRUNCATE_ONLY ': Deletes the inactive log section without backing up the log, and truncates the log. However, truncation does not reduce the size of the physical log file, but reduces the size of the logical log file. DBCC Shrinkfile
Shrinks the specified data file or log file size of the related database, reducing the size of the physical log file. Grammar
DBCC Shrinkfile
({file_name | file_id}
{[, target_size]
| [, {emptyfile | Notruncate | TRUNCATEONLY}]
}
The detailed description can refer to MK: @MSITStore: C:\PROGRAM%20FILES\MICROSOFT%20SQL%20SERVER\80\TOOLS\BOOKS\TSQLREF.CHM::/TS_DBCC_ 8b51.htm mk: @MSITStore: c:\program%20files\microsoft%20sql%20server\80\tools\books\architec.chm::/8_ar_da2_ 7vaf.htm