How to shrink all database log files in single shot

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


Labels: ,

Post a Comment

Post a Comment

Emoticon
:) :)) ;(( :-) =)) ;( ;-( :d :-d @-) :p :o :>) (o) [-( :-? (p) :-s (m) 8-) :-t :-b b-( :-# =p~ $-) (b) (f) x-) (k) (h) (c) cheer
Click to see the code!
To insert emoticon you must added at least one space before the code.

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.