To shrink all log files in server at one shot
create table #dbfiles (
[Database] sysname
, Name sysname
, [Type] sysname
, [Filename] nvarchar(1024)
, Allocated int
, Used int
, Available int
)
exec sp_msforeachdb 'use [?];insert into #dbfiles
select
''?'' as [Database]
, a.Name
, dbf.type_desc as [Type]
, a.Filename
, convert(int,round(a.Size/128.000,0)) as Allocated
, convert(int,round(fileproperty(a.Name,''SpaceUsed'')/128.000,0)) as Used
, convert(int,round((a.Size-fileproperty(a.Name,''SpaceUsed''))/128.000,0)) as Available
from
dbo.sysfiles a (nolock)
inner join sys.database_files dbf (nolock)
on a.fileid = dbf.file_id
where
db_id(''?'') not in (1,2,3,4)';
select
*
, 'use [' + [Database] + ']; dbcc shrinkfile (''' + [Name] + ''', ' + cast((Used + 1) as nvarchar(16)) + ');'
from
#dbfiles where [type]='LOG'
order by
Available desc
drop table #dbfiles
Post a Comment