July 20, 2006 at 6:02 am
Is there a dbcc command like DBCC SQLPERF(LOGSPACE) for calculating the free space at the end of the data file and the % used? If not, can someone please tell me how to calculate this information. I know I can use sysfiles to get the total size of the data file, but I also want to know the amout of the free space in my data file. Any help would be greatly appreciated. Thanks!
JWA
July 20, 2006 at 11:55 am
dbcc showfilestats
Total and Used extents are displayed in number of 64kb pages, so (extents * 64 / 1024) will give approximate size in MB.
Here's a script I wrote to summarize database usage:
set nocount on;
create table #bbf (f1 int,f2 int,xtt int,xtu int,nm sysname,fnm sysname,db sysname null);
create table #bbs (db sysname,mb real,pct real,st int);
insert into #bbf (f1,f2,xtt,xtu,nm,fnm) exec ('dbcc showfilestats with no_infomsgs');
update #bbf set db=db_name();
insert into #bbs exec ('dbcc sqlperf(logspace) with no_infomsgs');
select
sum(convert(decimal(9,2),a.xtt*64.0/1024.0)) as data_size_mb,
sum(convert(decimal(9,2),a.xtu*64.0/1024.0)) as data_used_mb,
max(convert(decimal(9,2),b.mb)) as log_size_mb,
max(convert(decimal(9,2),b.mb*b.pct/100)) as log_used_mb,
a.db
from #bbf a
join #bbs b
on a.db = b.db
where b.db = db_name()
group by a.db
drop table #bbf;
drop table #bbs;
set nocount off;
July 20, 2006 at 5:35 pm
Thank you so much! I appreciate the information.
JWA
July 21, 2006 at 3:59 am
To visualise this, use Enterprise Manager. Click on a database to get it in focus, then right-click, and select view taskpad. The taskpad display will show space used and space free.
The free space in a database file will typically be spread throughout the file, and will not be located just at the end of the file.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply