Why SQL Server cannot shrink log files and how to resolve it _mssql

The most recent server execution job that shrinks the log file size is always an error

A batch shrink log script that I used

Use the [master] go/****** object:storedprocedure [dbo]. [Shrinkuser_databaseslogfile] Script date:01/05/2016 09:52:39 ******/set ANSI_NULLS on Go Set QUOTED_IDENTIFIER in Go ALTER PROC [dbo].
[Shrinkuser_databaseslogfile] As BEGIN DECLARE @DBNAME NVARCHAR (max) DECLARE @SQL NVARCHAR (max)-temporary table save data CREATE table #DataBaseServerData (ID INT ID 
ENTITY (1, 1), dbname NVARCHAR (MAX), LOG_TOTAL_MB decimal (1) Not NULL, LOG_FREE_SPACE_MB decimal (1) is not NULL --Cursor 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 to @DBNAME while @ @FETCH_STATUS = 0 BEGIN SET @SQL =n ' use [' + @DBNAME + ']; ' +char + ' DECLARE @TotalLogSpace decimal (1) DECLARE @FreeLogSpace Decimal (1) DECLARE @filename NVARCHAR ( Max) DECLARE @CanshrinkSize BIGINT DECLARE @SQL1 nvarchar (max) SELECT @TotalLogSpaCe= (CONVERT (Dec (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 @filen Ame=name from Sys.database_files WHERE [type]=1 set @CanshrinkSize =cast ((@TotalLogSpace-@FreeLogSpace) as BIGINT) SET @S QL1 = ' Use [' + @DBNAME + '] ' SET @SQL1 = @SQL1 + ' DBCC shrinkfile ([' + @filename + '], ' + CAST (@CanshrinkSize +1 as NVA Rchar (MAX)) + ') ' EXEC (@SQL1) ' EXEC (@SQL) FETCH NEXT from @itemCur to @DBNAME end close @itemCur deallocate @i  Temcur SELECT * FROM [#DataBaseServerData] DROP TABLE [#DataBaseServerData] End

Thanks to the error information is still very comprehensive, according to the error information to find the relevant database, the implementation of DBCC LOGINFO

DBCC LOGINFO (N ' cdb ')

Found that there are really only two VLF files, can not be shrunk, because it is a batch script, when one of the libraries failed, subsequent libraries will not shrink operations

Here, just add the VLF number of the database to the judge.

This article is not good, but also ask you to make valuable suggestions, such as a good solution welcome to share, we learn to progress together.