Server
method One:
Change the database log file to simple log mode
Method Two:
DUMP TRANSACTION your database name with NO_LOG
BACKUP LOG your database name with NO_LOG
DBCC shrinkdatabase (your database name)
EXEC sp_dboption ' Your database name ', ' autoshrink ', ' TRUE '
Method Three:
Copy the code into the Query Analyzer, and then modify the 3 parameters (database name, log file name, and target log file size) and run it (I've used it many times)
-----
SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
Use Marias-the name of the database to manipulate
SELECT @LogicalFileName = ' Marias_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)
--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 (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 '
DBCC shrinkfile (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
--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
