SQL Server Performance Forum – Threads Archive
need the size off all data and log files individua
Hi!FolksI 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
]]>