前几天也碰到日志文件过大的问题,数据库实际大小为600M, 日志文件实际大小为33M, 但日志文件占用空间为2.8G!!!
试了多种方式,SHIRNK DATABASE, TRUNCATE LOG FILE, 都没办法将文件缩小。无论如何,这应该算SQL SERVER的一个BUG吧。
后来找到下面的代码,就可以将日志文件缩小到自己想要的大小了。把代码COPY到查询分析器里,,然后修改其中的3个参数(数据库名,日志文件名,和目标日志文件的大小),运行即可(我已经用过多次了)
-----
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE Marias -- 要操作的数据库名
SELECT @LogicalFileName = 'Marias_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- 你想设定的日志文件的大小(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
-----------------------------------------
--以下是2008及之后的版本 源自msdn
A. 将数据文件收缩到指定的目标大小
以下示例将 UserDB 用户数据库中名为 DataFile1 的数据文件的大小收缩到 7 MB。
USE UserDB; GO DBCC SHRINKFILE (DataFile1, 7); GO
B. 将日志文件收缩到指定的目标大小
以下示例将 AdventureWorks2008R2 数据库中的日志文件收缩到 1 MB。若要允许 DBCC SHRINKFILE 命令收缩文件,首先需要通过将数据库恢复模式设置为 SIMPLE 来截断该文件。
USE AdventureWorks2008R2; GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2008R2 SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1); GO -- Reset the database recovery model. ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL; GO
C. 截断数据文件
以下示例将截断 AdventureWorks2008R2 数据库中的主数据文件。需要查询 sys.database_files 目录视图以获得数据文件的 file_id。
USE AdventureWorks2008R2; GO SELECT file_id, name FROM sys.database_files; GO DBCC SHRINKFILE (1, TRUNCATEONLY);
D. 清空文件
以下示例演示了清空文件以便从数据库中将其删除的步骤。针对此示例,首先创建一个数据文件,并假设该文件包含数据。
USE AdventureWorks2008R2; GO -- Create a data file and assume it contains data. ALTER DATABASE AdventureWorks2008R2 ADD FILE ( NAME = Test1data, FILENAME = 'C:\t1data.ndf', SIZE = 5MB ); GO -- Empty the data file. DBCC SHRINKFILE (Test1data, EMPTYFILE); GO -- Remove the data file from the database. ALTER DATABASE AdventureWorks2008R2 REMOVE FILE Test1data; GO