To know datafile size

HI all,

can you give me any satement which can give me data file size
as such "dbcc sqlperf(logspace)" log file size.

thanks
shashank


Dont go on my posts iam still a student (*_*)

I don't think there is any command which gives you the similar results as <br />dbcc sqlperf(logspace) about data files...<br />But you can use the following script and modify as needed...<br /><br /><br />set nocount on <br /><br /><br />declare @cmd varchar(500) <br />declare @db varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> <br /><br /><br />create table #results(FileType varchar(4) NOT NULL, <br /> [Name] sysname NOT NULL, <br /> Total numeric(9,2) NOT NULL, <br /> Used numeric(9,2) NOT NULL, <br /> [Free] numeric(9,2) NOT NULL, <br /> dbname sysname NULL) <br /><br /><br />create table #data(Fileid int NOT NULL, <br /> [FileGroup] int NOT NULL, <br /> TotalExtents int NOT NULL, <br /> UsedExtents int NOT NULL, <br /> [Name] sysname NOT NULL, <br /> [FileName] varchar(300) NOT NULL) <br /><br /><br />create table #log(dbname sysname NOT NULL, <br /> LogSize numeric(15,7) NOT NULL, <br /> LogUsed numeric(9,5) NOT NULL, <br /> Status int NOT NULL) <br /><br /><br />begin <br /><br /><br /> /* Get data file(s) size */ <br /> declare dcur cursor local fast_forward <br /> for <br /> select NAME from sysdatabases<br /><br /> open dcur <br /><br /><br /> fetch next from dcur into @db <br /><br /><br /> while @@fetch_status=0 <br /> begin <br /><br /><br /> set @cmd = 'use ' + @db + ' DBCC showfilestats' <br /> insert #data <br /> exec(@cmd) <br /><br /><br /> insert #results(FileType,[Name],Total,Used,[Free],dbname) <br /> select 'Data', <br /><br /><br />left(right([FileName],charindex('',reverse([FileName]))-1), <br /> charindex('.',right([FileName], <br /> charindex('',reverse([FileName]))-1))-1), <br /> CAST(((TotalExtents*64)/1024.00) as numeric(9,2)), <br /> CAST(((UsedExtents*64)/1024.00) as numeric(9,2)), <br /> (CAST(((TotalExtents*64)/1024.00) as numeric(9,2)) <br /> -CAST(((UsedExtents*64)/1024.00) as numeric(9,2))), <br /> @db <br /> from #data <br /><br /><br /> delete #data <br /><br /><br /> fetch next from dcur into @db <br /><br /><br /> end <br /> close dcur <br /> deallocate dcur <br /><br /><br /> /* Get log file(s) size */ <br /> insert #log <br /> exec('dbcc sqlperf(logspace)') <br /><br /><br /> insert #results(FileType,[Name],Total,Used,[Free],dbname) <br /> select 'Log',dbname+'_log',LogSize, <br /> ((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize), <br /> dbname <br /> from #log <br /><br /><br /> select dbname,FileType,[Name],Total,Used,[Free] <br /> from #results order by dbname,FileType <br /><br />select @@servername as servername, dbname, sum(total) Total, sum(used) Used, sum(free) Free<br /> from #results<br />group by dbname<br />order by Total desc<br /><br /> drop table #data <br /> drop table #log <br /> drop table #results <br /><br /><br /> return <br /><br /><br />end <br /><br /><br />/**********************************<br /><br />**********************/<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />

Thanks all

Dont go on my posts iam still a student (*_*)

I have adapted the script and it works great. Thanks. But if your databases contains some "weird characters", eg "-" or "&", it will fail. To fix it, put in a pair of square brackets "[ ]" in front and behind the keyword "use".