How go query Database Free Space or Used Space via T-SQL
Marc Eggenberger
I need to get some values out of a MSSQL 7 server
with
select sum(size*8) from sysfiles where sysfiles.groupid = 1
I get the size of all the Datafiles of a specific database .. this works
...
but now I need either the used space or free space of that database.
I havent found an entry in a sys table which holds this information.
Anyone has a hint for me how to get this value via T-SQL?
Thanks for any help.
--
mfg
Marc Eggenberger
Aaron Bertrand [MVP]
Maybe this will help: see the "Replicating 'Taskpad / Table Info' view"
section of http://www.aspfaq.com/2428
--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/
Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.
"Marc Eggenberger" <marc.egg...@power.alstom.com> wrote in message
news:MPG.188cecafe578f5c4989682@localhost...
Narayana Vyas Kondreddi
Look at the code of sp_spaceused for more information.
USE master
GO
EXEC sp_helptext 'sp_spaceused'
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server FAQ, articles, code samples, interview questions and more @
http://vyaskn.tripod.com/
"Marc Eggenberger" <marc.egg...@power.alstom.com> wrote in message
news:MPG.188cecafe578f5c4989682@localhost...
Jasper Smith
You can use DBCC SHOWFILESTATS to get the total
and used extents for a database and thus calculate the space.
An example below ( it cheats a bit on the logfile because I
have never seen the need for more than 1 log file - no benefit)
Results are in MB
use master
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_dbspaceall]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_dbspaceall]
GO
CREATE procedure sp_dbspaceall(@all char(1)='N')
as
set nocount on
declare @cmd varchar(500)
declare @db varchar(128)
create table #results(FileType varchar(4) NOT NULL,
[Name] sysname NOT NULL,
Total numeric(9,2) NOT NULL,
Used numeric(9,2) NOT NULL,
[Free] numeric(9,2) NOT NULL,
dbname sysname NULL)
create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)
create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)
If @all='N' /* just the current database */
begin
/* Get data file(s) size */
insert #data
exec('DBCC showfilestats')
/* Get log file(s) size */
insert #log
exec('dbcc sqlperf(logspace)')
insert #results(FileType,[Name],Total,Used,[Free])
select 'Data',
left(right([FileName],charindex('\',reverse([FileName]))-1),
charindex('.',right([FileName],
charindex('\',reverse([FileName]))-1))-1),
CAST(((TotalExtents*64)/1024.00) as numeric(9,2)),
CAST(((UsedExtents*64)/1024.00) as numeric(9,2)),
(CAST(((TotalExtents*64)/1024.00) as numeric(9,2))
-CAST(((UsedExtents*64)/1024.00) as numeric(9,2)))
from #data
union all
select 'Log',dbname+'_log',LogSize,
((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize)
from #log
where dbname = db_name()
select FileType,[Name],Total,Used,[Free]
from #results order by FileType
drop table #data
drop table #log
drop table #results
return
end
else
begin
/* Get data file(s) size */
declare dcur cursor local fast_forward
for
select CATALOG_NAME
from INFORMATION_SCHEMA.SCHEMATA
open dcur
fetch next from dcur into @db
while @@fetch_status=0
begin
set @cmd = 'use ' + @db + ' DBCC showfilestats'
insert #data
exec(@cmd)
insert #results(FileType,[Name],Total,Used,[Free],dbname)
select 'Data',
left(right([FileName],charindex('\',reverse([FileName]))-1),
charindex('.',right([FileName],
charindex('\',reverse([FileName]))-1))-1),
CAST(((TotalExtents*64)/1024.00) as numeric(9,2)),
CAST(((UsedExtents*64)/1024.00) as numeric(9,2)),
(CAST(((TotalExtents*64)/1024.00) as numeric(9,2))
-CAST(((UsedExtents*64)/1024.00) as numeric(9,2))),
@db
from #data
delete #data
fetch next from dcur into @db
end
close dcur
deallocate dcur
/* Get log file(s) size */
insert #log
exec('dbcc sqlperf(logspace)')
insert #results(FileType,[Name],Total,Used,[Free],dbname)
select 'Log',dbname+'_log',LogSize,
((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize),
dbname
from #log
select dbname,FileType,[Name],Total,Used,[Free]
from #results order by dbname,FileType
drop table #data
drop table #log
drop table #results
return
end
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marc Eggenberger" <marc.egg...@power.alstom.com> wrote in message
news:MPG.188cecafe578f5c4989682@localhost...