SQL Server 2000 will have a log file due to the time accumulation of time: the actual size of the database is 15m, the log file actually size is 625KB (exported log file), but the actual space of the log file is 200MB (the default setting is file The log will grow automatically). If you want to change the storage space of the current log file directly to the database properties, it is not possible.
Solution:
Find the code below, you can narrow the log file to the size you want.
Put the code COPY to the query analyzer, then modify the three parameters (the data name, log file name, and the size of the target log file), run! Set NoCount Ondeclare @LogicalFileName Sysname, @ maxminutes int, @ Newsize INTUSE GFCMS - Database name to be operated SELECT @LogicalFileName = 'gfcms_log', - log file name @maxminutes = 10, - limit on time allowed to wrap log. @ Newsize = 100 - You want to set the log file you want to set size (M), note that this size must be less than the actual file size - Setup / initialize-- obtain the original file size DECLARE @OriginalSize intSELECT @OriginalSize = size FROM sysfilesWHERE name = @LogicalFileNameSELECT 'original size of' db_name () ' LOG is ' CONVERT (VARCHAR (30), @ OriginalSize) ' 8K pages or ' CONVERT (VARCHAR (30), (@ OriginalSize * 8/1024)) ' MB'FROM sysfilesWHERE name = @LogicalFileNameCREATE 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) - Tagging the LOG of the transaction that can shrink in the log as clear DBCC ShrinkFile (@LogicalFileName, @newsize) - Shrink file - Wrap the log if Necessary.While @maxminutes > DATEDIFF (mi, @StartTime, GETDATE ()) - time has not expiredAND @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 = @LogicalFileNamedrop Table DummyTransset NoCount Off Detailed explanation:
The key statement is:
'Backup log' db_name () 'with truncate_only' and
DBCC ShrinkFile (@LogicalFileName, @newsize)
'Backup log' DB_NAME () 'with truncate_only':
In the case where the log is not backed up, delete the inactive log part and truncate the log. However, the truncation does not reduce the size of the physical log file, but reducing the size of the logical log file.
DBCC SHRINKFILE contraction related database specified data files or log file size, that is, reduce the size of the physical log file.
Syntax dbcc shrinkfile ({file_name | file_id} {[, target_size] | [, {emptyfile | notruncate | TruncateOnly}]})
Detailed description can be referenced
MK: @msitstore: C: /program files/MICROSOFT SQL Server/80/toLS/Books/tsqlref.chm :: / ts_dbcc_8b51.htm
MK: @MsitStore: C: /Program Files/Microsoft SQL Server/80/toLS/books/architec.chm :: / 8_ar_da2_7vaf.htm
