Below is the super script to use
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