文章出處

SQL Server無法收縮日志文件 2 因為邏輯日志文件的總數不能少于 2問題

最近服務器執行收縮日志文件大小的job老是報錯

我所用的一個批量收縮日志腳本

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[ShrinkUser_DATABASESLogFile]    Script Date: 01/05/2016 09:52:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[ShrinkUser_DATABASESLogFile]
AS
BEGIN
    DECLARE @DBNAME NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)



--臨時表保存數據
CREATE TABLE #DataBaseServerData
(
  ID INT IDENTITY(1, 1) ,
  DBNAME NVARCHAR(MAX) ,
  Log_Total_MB DECIMAL(18, 1) NOT NULL ,
  Log_FREE_SPACE_MB DECIMAL(18, 1) NOT NULL 
)



--游標
DECLARE @itemCur CURSOR
SET 
@itemCur = CURSOR FOR 
SELECT name from   SYS.[databases] WHERE [name] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ReportServer','ReportServerTempDB','distribution')
and state=0

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL=N'USE ['+@DBNAME+'];'+CHAR(10)
    +'
     DECLARE @TotalLogSpace DECIMAL(18, 1)
     DECLARE @FreeLogSpace DECIMAL(18, 1)
     DECLARE @filename NVARCHAR(MAX)
     DECLARE @CanshrinkSize BIGINT
     DECLARE @SQL1 nvarchar(MAX)

SELECT  @TotalLogSpace=(SUM(CONVERT(dec(17, 2), sysfiles.size)) / 128) 
     FROM    dbo.sysfiles AS sysfiles  WHERE [groupid]=0

SELECT  @FreeLogSpace = ( SUM(( size - FILEPROPERTY(name, ''SpaceUsed'') )) )/ 128.0
     FROM    sys.database_files
     WHERE   [type] = 1

SELECT @filename=name  FROM sys.database_files WHERE [type]=1
SET @CanshrinkSize=CAST((@TotalLogSpace-@FreeLogSpace) AS BIGINT)



 SET @SQL1 = ''USE ['+@DBNAME+']''
SET @SQL1 = @SQL1+
 ''DBCC SHRINKFILE (['' + @filename + ''],'' + CAST(@CanshrinkSize+1 AS NVARCHAR(MAX)) + '')''
  EXEC (@SQL1)'
   EXEC (@SQL)
        FETCH NEXT FROM @itemCur INTO @DBNAME
    END 

CLOSE @itemCur
DEALLOCATE @itemCur

SELECT  *  FROM    [#DataBaseServerData]
DROP TABLE [#DataBaseServerData]

END

幸虧報錯信息還是很全面，根據報錯信息找到相關的數據庫，執行一下DBCC LOGINFO

dbcc loginfo(N'cdb')

發現確實只有兩個VLF文件，不能再收縮了，因為是批量腳本，當其中有一個庫失敗之后，后續的庫就不會再進行收縮操作

這里只要加上數據庫的VLF數量的判斷就可以了

附上TIPS

VLF的5種狀態
0、從未使用過
1、active。表示VLF中存在活動的事務（即未完成的事務）。
2、recoverable。表示VLF中的事務全部已經完成，但是某些操作（例如數據庫鏡像、復制等）還需要用到這些數據，因此不可以被覆蓋。
3、reusable。表示VLF中的數據已經不需要了，可以被覆蓋。
4、unused。表示VLF從未被使用。

創建數據庫的時候，指定LDF文件可以大一點，比如指定大于1G，LDF文件自動增長指定一次增長200MB

這樣就有足夠的VLF給你收縮了