SQL Server 的日志是一个可怕的家伙，会不停的增长，如果限制大小，又会出现问题！那么如何减少SQL Server日志的大小呢？用如下的命令即可：

backup log DatabaseName with no_log

dbcc shrinkdatabase(DatabaseName)

或者接下来，用企业管理器收缩数据库即可！

代码如下：

 with TADOQuery.Create(Self) do

 try

   Connection := FrmDm.dbKS;

   ///清除日志，并且收缩数据库，减少物理文件大小

   SQL.Add('backup log KS with no_log');

   SQL.Add('dbcc shrinkdatabase(KS)');

   ExecSQL;

 finally

   Free;

 end;

---------------------------------------

nzfsoft

下面这段sql代码是改自Microsoft的， 你运行一下生成的stored proc， 可以把你的log文件缩小到10M, ^_^

注意把里面注释的数据库名改成你自己的库名啊

if exists (select * from sysobjects where id = object_id(N'[dbo].[Truncate_Log_File]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[Truncate_Log_File]

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO

CREATE PROCEDURE [Truncate_Log_File] AS

SET NOCOUNT ON

  DECLARE @LogicalFileName sysname,

          @MaxMinutes INT,

          @NewSize INT

  SELECT  @LogicalFileName = 'XXXX_LOG',   -- 这里的XXXX_LOG替换成泥自己数据库的log名

          @MaxMinutes = 10,

          @NewSize    = 10

  DECLARE @OriginalSize int

  SELECT @OriginalSize = size

    FROM sysfiles

    WHERE name = @LogicalFileName

  DECLARE @Counter   INT,

          @StartTime DATETIME,

          @TruncLog  VARCHAR(255)

WHILE @OriginalSize*8/1024>@Newsize

BEGIN

  CREATE TABLE DummyTrans

    (DummyColumn char (8000) not null)

  SELECT  @StartTime = GETDATE(),

          @TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'

  DBCC SHRINKFILE (@LogicalFileName, @NewSize)

  EXEC (@TruncLog)

  WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE())

        AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)

        AND (@OriginalSize * 8 /1024) > @NewSize

    BEGIN

      SELECT @Counter = 0

      WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 5000))

        BEGIN

          INSERT DummyTrans VALUES ('Fill Log')

          DELETE DummyTrans

          SELECT @Counter = @Counter + 1

        END

      EXEC (@TruncLog)

    END

    SELECT @OriginalSize=size

    FROM sysfiles

    WHERE name = @LogicalFileName

  DROP TABLE DummyTrans

END

SET NOCOUNT OFF

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON

GO