sqlserver 数据库文件

hercules_123 2006-02-16 04:18:57
我发现数据库的.mdf文件有800mb
可是.ldf就有4.6GB
请问为何LDF文件会有这么大,会影响用户访问的速度吗?
以及怎样将这个文件的容量减少呢?请赐教,谢谢!
...全文
66 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
云中客 2006-02-16
在查询分析器里执行下面的代码,执行前请先备份数据库
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 )
  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
回复
chb5210 2006-02-16
dbcc shrinkdatabase(数据库名)

把日志截掉吧
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-02-16 04:18
社区公告
暂无公告