如何减少日志文件的空间

 

                      如何减少日志文件的空间

 

我们在应用程序开发过程中经常会出现由于要经常插入,删除和更改文件而导致我们的日志文件过大,此时我们就希望能够把减少日志文件所占有的空间.这经常有以下场景:

 

1.我们的日志对我们进行数据的恢复非常重要,如果我们以后要用它进行对数据进行恢复的话,我们就应该首先对它进行备份.备份过之后日志数据所占有的空间是少了,但是在体现在文件系统上还是在备份以前的大小,这时我们就可以对日志进行收缩,以收回日志所占用的过多空间.

 

BACKUP DATABASE PUBS

TO DISK='C:\PUBS.BAK'

 

SP_HELPDB 'PUBS'

 

USE PUBS

 

DBCC SHRINKFILE('PUBS_LOG',1)

 

2,如果我们的日志没有什么用的话,我们一方面可以设计SQL Server的恢复模式为"简单"实现在日志的自动删除

 

ALTER DATABASE RESEARCH

SET RECOVERY SIMPLE

 

3.如果我们以前没有注意,现在日志已经很大, 但是日志对我们又没有什么用的话,此时可以使用两种方法来减少日志文件所占有的空间

   1.直接删除日志文件

 

exec sp_helpdb 'pubs'

 

Name        filename

pubs         d:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf          

pubs_log    d:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf   

 

EXEC SP_DETACH_DB 'PUBS'

 

EXEC MASTER.DBO.XP_CMDSHELL 'DEL "d:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf"'

 

 

EXEC MASTER.DBO.XP_CMDSHELL 'DIR "d:\Program Files\Microsoft SQL Server\MSSQL\data\PUBS*.LDF"'

THE RESULT IS:

File Not Found

 

EXEC SP_ATTACH_SINGLE_FILE_DB N'PUBS',

        @PHYSNAME=N'd:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf'

 

SQL SERVER PRINT THE FOLLOW MESSAGE:

Device activation error. The physical file name 'd:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf' may be incorrect.

New log file 'd:\Program Files\Microsoft SQL Server\MSSQL\data\PUBS_log.LDF' was created.

 

NOW YOU CAN USE PUBS NORMALLY.

 

   2.截断日志,然后收缩日志文件.可以把这引步骤放在一个存储过程中,请先执行下面的存储过程创建语句:

 

CREATE PROC Up_shrinkfile

 @LogicalFileName sysname,---- Use sp_helpfile to identify the logical filename that you want to shrink.

           @MaxMinutes INT,--Limit on time allowed to wrap log.

           @NewSize INT  -- in MB

as

Begin

   DECLARE @OriginalSize int

   SELECT @OriginalSize = size -- in 8K pages

     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)

 

   -- Wrap log and truncate it.

   DECLARE 

           @StartTime DATETIME,

           @TruncLog  VARCHAR(255)

   SELECT  @StartTime = GETDATE(),

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

   -- Try an initial shrink.

   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)  -- the log has not shrunk

         AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in for new size is smaller than the current size.

      BEGIN -- Outer loop.

         WAITFOR DELAY '00:00:30'

         EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.

      END   -- outer loop

   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

   

   PRINT '*** Perform a full database backup ***'

   SET NOCOUNT OFF

End

 

然后执行此存储过程,如:

 

exec sp_helpdb 'pubs'

 

EXEC Up_shrinkfile 'pubs_log',10,1

 

                                          -- 董晓涛