May 4, 2010 at 4:11 am
Hi Team,
We can find out the free space on the disks using xp_fixeddrives, but we cannot find out what is the total space.
Is there anyway to find out total disk space using sql server query or any other command prompt queries.
Thanks in advance
Thank You.
Regards,
Raghavender Chavva
May 4, 2010 at 6:28 am
Some links for you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99653
http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx
-- Gianluca Sartori
May 4, 2010 at 8:22 am
/************************************************************************
*
* Author: Rafal Skotak
* Purpose: Procedure displays amount of disk space used by databases per directories
* Date: 2008-01-14
*
************************************************************************/
if exists(select * from sysobjects where id = object_id('dbo.proc_get_db_files_size') and xtype = 'P')
drop procedure dbo.proc_get_db_files_size
go
create procedure dbo.proc_get_db_files_size
as
begin
set nocount on
create table #temp_result_table
(
[rec_id] int identity(1, 1) primary key,
[db_name] sysname not null,
[files_size] int not null,
[directory] nvarchar(1024) not null
);
create table #temp_dbs_table
(
[db_name]sysname not null primary key,
);
insert into #temp_dbs_table ([db_name]) select [name] from master..sysdatabases
declare @db_name sysname
set @db_name = ''
while @db_name is not NULL
begin
set @db_name = NULL
select @db_name = [db_name] from #temp_dbs_table
if @db_name is NULL
break
declare @n_cmd nvarchar(4000)
set @n_cmd = 'insert into #temp_result_table ([db_name], [files_size], [directory]) select ''' + @db_name + ''', size * 8, reverse(ltrim(rtrim(filename))) from [' + @db_name + ']..sysfiles'
exec sp_executesql @n_cmd
delete from #temp_dbs_table where [db_name] = @db_name
end
drop table #temp_dbs_table
update
#temp_result_table
set
[directory] = reverse
(
substring
(
[directory], charindex('\', [directory], 0), len([directory])
)
)
select
[db_name],
[directory],
sum([files_size]) as [files_size]
from
#temp_result_table
group by
[db_name],
[directory]
order by
[db_name],
[directory]
drop table #temp_result_table
end
go
-- example :
exec dbo.proc_get_db_files_size
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 4, 2010 at 8:35 am
Bhuvnesh (5/4/2010)
/*************************************************************************
* Author: Rafal Skotak
* Purpose: Procedure displays amount of disk space used by databases per directories
* Date: 2008-01-14
*
************************************************************************/
....
It doesn't look this code does what the OP wants.
Are you sure you didn't misread the question?
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply