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数量的判断就可以了

本文写的不好，还请各位大侠提出宝贵意见，如有好的解决方案欢迎分享，大家共同学习进步。