declare @tb table(sqlstr varchar(2000))
--查询需要压缩的数据库
declare cur cursor for select name from sys.databases where name like 'hb%' or name in ('hz028','hz029')
declare @dbname varchar(50),@log_fileName varchar(50),@sql varchar(1000)
open cur
fetch next from cur into @dbname
while @@fetch_status =0
begin
set @sql = 'USE ['+@dbname+']'
exec(@sql)
set @log_fileName=null
select top 1 @log_fileName=[name] from sys.database_files f where [type]=1
if @log_fileName is not null
begin
set @sql='USE ['+@dbname+']'+CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+'ALTER DATABASE ['+@dbname+'] SET RECOVERY SIMPLE;DBCC SHRINKFILE (N'''+@log_fileName+''', 1, TRUNCATEONLY);ALTER DATABASE ['+@dbname+'] SET RECOVERY FULL WITH NO_WAIT'+CHAR(13)+CHAR(10)+'GO'
insert into @tb(sqlstr) values(@sql)
end
else
begin
print @dbname+'日志文件未找到'
end
fetch next from cur into @dbname
end
close cur
deallocate cur
select * from @tb
SELECT a.name [文件名称]
,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件设置大小(MB)]
,CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [文件所占空间(MB)]
,CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0 AS DECIMAL(12,1)) AS [所占空间率%]
,CASE WHEN A.growth =0 THEN '文件大小固定,不会增长' ELSE '文件将自动增长' end [增长模式]
,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN '增量为固定大小'
WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量将用整数百分比表示'
ELSE '文件大小固定,不会增长' END AS [增量模式]
,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB'
WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'%'
ELSE '文件大小固定,不会增长' end AS [增长值(%或MB)]
,a.physical_name AS [文件所在目录]
,a.type_desc AS [文件类型]
FROM sys.database_files a
INNER JOIN sys.sysfiles AS s ON a.[file_id]=s.fileid
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id]