游标的使用之压缩数据库Log文件

declare @databasename nvarchar(100)
--定义游标以及赋值 获取所有Online的Database Name
declare getDataBaseCursor  cursor for select name from  master.sys.databases where state_desc='online'
--在使用游标之前必须打开它，打开游标
open getDataBaseCursor
--使用fetcth语句取得数据，一条FETCH语句一次可以将一条记录放入指定的变量中,达到Shrink每个DB的效果
fetch next from getDataBaseCursor into @databasename
while @@FETCH_STATUS = 0
begin
 declare @RecoveryModel nvarchar(20)
 declare @sql nvarchar(100)
 declare @logfileid int
 select @RecoveryModel= cast(DATABASEPROPERTYEX(name,'RECOVERY') as varchar(20)) from master..sysdatabases where name =@databasename
 IF @RecoveryModel<>'SIMPLE'
  begin
                EXEC ('ALTER DATABASE '+@databasename +' SET RECOVERY SIMPLE')
 end
 declare @dsql nvarchar(200)
 set @dsql=N'select @logfileid = file_id from ' + @databasename+ N'.sys.database_files where type_desc = ''LOG'''
 exec sp_executesql @dsql,N'@logfileid int output',@logfileid output
 EXEC('use '+@databasename+ ' DBCC SHRINKFILE( '+@logfileid+')')
fetch next from getDataBaseCursor into @databasename
end
--在游标操作的最后请不要忘记关闭游标,以使系统释放游标占用的资源
CLOSE getDataBaseCursor
--删除游标
DEALLOCATE getDataBaseCursor

转载于:https://www.cnblogs.com/tutuyforever/p/4797146.html