-
In the process of using SQL Server, we often encounter situations where the database log is very large. When encountering this problem, we introduce two methods to solve it.
Method one
Under normal circumstances, shrinking a SQL database cannot reduce the database size to a great extent. Its main function is to shrink the log size. This operation should be performed regularly to prevent the database log from becoming too large.
1. Set the database mode to simple mode: Open SQL Enterprise Manager, click Microsoft SQL Server in the console root directory-->SQL Server group-->double-click to open your server-->double-click to open the database directory-- >Select your database name (such as Forum database Forum)-->then right-click and select Properties-->Select Options-->Select "Simple" in the failover mode, and then press OK to save
2. Right-click on the current database and view the shrink database in all tasks. Generally, the default settings do not need to be adjusted. Just click OK.
3. After shrinking the database, it is recommended to reset your database properties to the standard mode. The operation method is the same as the first point, because the log is often an important basis for restoring the database under some abnormal circumstances. Method 2
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSizeINT
USE tablename -- the name of the database to be operated on
SELECT @LogicalFileName = 'tablename_log', -- log file name
@MaxMinutes = 10, -- 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 @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@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 of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF