need the size off all data and log files individua | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

need the size off all data and log files individua

Hi!Folks
I need some help in determining the size of all data and log files individually for all my databases on server.If some one has a readymade script or sp …pls post here as I need this in a matter of urgency.
Check this script and try..
———————————–
create proc sp_filesize
as
set nocount on — Database Size
print ‘*** Database Size ***’ create table #db_size (dbno int identity,
dbdate datetime,
dbname nvarchar(50),
datasize float,
logsize float(53),
indexsize float(53),
actual float) declare @datasize float
declare @logsize float
declare @indexsize float
declare @usedspace float
declare @dbsize float
declare @datacmd nvarchar (100)
declare @logcmd nvarchar (100)
declare @indexcmd nvarchar (100)
declare @usedspacecmd nvarchar (1000)
declare @dbsizecmd nvarchar (1000)
declare @actual nvarchar (100)
declare @dbname char (25) declare c1 cursor for select name from master..sysdatabases
open c1
fetch c1 into @dbname while @@fetch_status = 0
begin
set @datacmd=’select @datasize=size from [‘[email protected]+’].dbo.sysfiles where fileid=1′
exec sp_executesql @datacmd, N’@datasize float output’, @datasize output
set @datasize=round(@datasize/128,0) set @logcmd=’select @logsize=size from [‘[email protected]+’].dbo.sysfiles where fileid=2′
exec sp_executesql @logcmd, N’@logsize float output’, @logsize output
set @logsize=round(@logsize/128,0) set @indexcmd=’select @indexsize=size from [‘[email protected]+’].dbo.sysfiles where fileid=3′
exec sp_executesql @indexcmd, N’@indexsize float output’, @indexsize output
set @indexsize=round(@indexsize/128,0) insert into #db_size (dbdate,dbname,datasize,logsize,indexsize,actual)
values (getdate(),@dbname,@datasize,@logsize,@indexsize,@usedspace)
fetch next from c1 into @dbname
end
select
cast(dbno as varchar(5)) "DBno",
cast(dbdate as varchar(20)) "DBDate",
cast(dbname as varchar(20)) "DBName",
cast(datasize as varchar(10)) "datasize",
cast(logsize as varchar(10)) "logsize",
cast(indexsize as varchar(10)) "indexsize"
from #db_size
drop table #db_size
close c1
deallocate c1
GO
——————————–
exec sp_filesize
======================== I hope this will give you the required info.
Regards
-Johnson

]]>