SQL Server沒法壓縮日記文件的緣由剖析及處理方法

SQL Server沒法壓縮日記文件的緣由剖析及處理方法。本站提示廣大學習愛好者：（SQL Server沒法壓縮日記文件的緣由剖析及處理方法）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server沒法壓縮日記文件的緣由剖析及處理方法正文

比來辦事器履行壓縮日記文件年夜小的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數目的斷定便可以了

本文寫的欠好，還請列位年夜俠提出名貴看法，若有好的處理計劃迎接分享，年夜家配合進修提高。