SQL Server Performance

To know datafile size

Discussion in 'General DBA Questions' started by shashank sharma, May 26, 2007.

  1. shashank sharma New Member

    HI all,

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


    Dont go on my posts iam still a student (*_*)
  2. MohammedU New Member

    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 />
  3. dineshasanka Moderator

  4. satya Moderator

  5. shashank sharma New Member

    Thanks all

    Dont go on my posts iam still a student (*_*)
  6. akkha New Member

    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".
  7. satya Moderator

    True it is one of best practice to avoid any mismatch - quoted identifiers.
  8. Luis Martin Moderator

    That post is 4 years old.
  9. FrankKalis Moderator

    ...and to be fair, the formatting after forum migration could be better :)

Share This Page