SQL Server Performance Forum – Threads Archive

Finding database actual filled size ??

Hi all, How I can find the actual filled size of data/log (.mdf/.ldf) files, the same kind of report i need to prepare as we find in Enterprises manager’s taskpad view.. Thanks in advance…. –DeepakK

SP_HELPDB or SP_HELPFILE for basic information about data/log files.
SP_SPACEUSED @UPDATEUSAGE=’true’
for updated sizes on the database Linkhttp://www.databasejournal.com/features/mssql/article.php/3339681 for reference on monitoring the database growth. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

DBCC SHOWFILESTATS will give you individual DB file size space usage in terms of extents used (do some calculation to find space used in KB) and DBCC SQLPERF(‘LOGSPACE’) will give you space used by log file. HTH
-Rajeev Lahoty

Can we store output of DBCC SHOWFILESTATS into any table…? create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, Used_Space int, name1 varchar(25),NameofFile Varchar(200) )
insert into #DBSpace sp_execute ‘DBCC SHOWFILESTATS’
Drop table #DBSpace I am getting errors while doing so

Try: declare @SQLstring as nvarchar(500)
set @SQLstring = N’DBCC SHOWFILESTATS’ create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, Used_Space int, name1 varchar(25),NameofFile Varchar(200) )
insert into #DBSpace execute sp_executesql @SQLstring
Drop table #DBSpace Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks Martin… I have got solution.

]]>