SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Free space


Free space

Author
Message
kumar99ms
kumar99ms
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 550
Hi

HOw can u check free space in MS SQL SERVER SERVER

Using dbcc or system stored procedures could you tel me what are those ?


giving the exact information really appreciated to those guys

Tongue
Thanks
David Benoit
David Benoit
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4342 Visits: 3650
Are you looking for free space in the data file or are you looking for free space on the disk?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Jonathan Kehayias
Jonathan Kehayias
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3646 Visits: 1807
I provided all of the code that I use to monitor both database internal file space and disk drive free space. You can find the links to both sets of code on my blog:

http://jmkehayias.blogspot.com/2008/12/automating-common-dba-tasks.html

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Paresh Prajapati
Paresh Prajapati
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 464
-- To find Database space

sp_msforeachdb 'dbcc showfilestats'
GO

sp_msforeachdb 'sp_spaceused'
GO

sp_databases
GO






-------------------------------------------------------------------------------------------

Row count and space used for all tables in database -------------------------------------------------------------------------------------------
/*****************************************************************************************************************************************

******************************************************************************************************************************************/

if exists(select * from sys.objects where object_id = object_id('dbo.proc_records_per_database') and type = 'P')
drop procedure dbo.proc_records_per_database
go

create procedure dbo.proc_records_per_database
@db_name sysname = NULL
as
begin
set nocount on

if @db_name is not null
begin
if not exists(select * from master.sys.databases where name = @db_name and database_id not in (2, 3) /* skip tempdb and model */)
begin
raiserror('Database does not exist or can not be queried', 16, 1)
return
end
end

declare @dbs_table table(name sysname not null primary key)

--------------------------------------------------------------------------------------
-- prepare databases list

insert into @dbs_table
select
name
from
master.sys.databases
where
(@db_name is null or
(@db_name is not null and name = @db_name)) and
database_id not in (2, 3)

create table #temp_table_list
(
rec_id int identity(1, 1) not null,
cat_name sysname not null,
sch_name sysname not null,
tab_name sysname not null,
row_count bigint not null default 0,
reserved_pages bigint not null default 0,
reserved bigint not null default 0,
pages bigint not null default 0,
data bigint not null default 0,
used_pages bigint not null default 0,
used bigint not null default 0,
index_size bigint not null default 0,
not_used bigint not null default 0,
primary key(rec_id)
)

declare @cmd varchar(max)
declare @n_cmd nvarchar(max)
declare @dbx_name sysname

--------------------------------------------------------------------------------
-- get all tables

while 1 = 1
begin
set @dbx_name = NULL

select top 1 @dbx_name = [name] from @dbs_table

if @dbx_name is NULL
break

set @cmd = 'insert into #temp_table_list (cat_name, sch_name, tab_name) select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME from [' + @dbx_name + '].INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
set @n_cmd = cast(@cmd as nvarchar(max))

exec sp_executesql @n_cmd

delete from @dbs_table where [name] = @dbx_name
end

declare @max_rec_id int
set @max_rec_id = NULL

select @max_rec_id = max(rec_id) from #temp_table_list

if @max_rec_id is NULL
set @max_rec_id = -1

declare @counter int
set @counter = 1

declare @objname varchar(max)

while @counter <= @max_rec_id
begin
--------------------------------------------------------------------------------
-- update table usage statistics

select
@cmd = 'use [' + cat_name + ']; dbcc updateusage(0, ''[' + sch_name + '].[' + tab_name + ']'') with no_infomsgs'
from
#temp_table_list
where
rec_id = @counter

set @n_cmd = cast(@cmd as nvarchar(max))

-- print @n_cmd

exec sp_executesql @n_cmd

-----------------------------------------------------------------------------------------------
-- get table stats (based on the code of the procedure sp_spaceused)

declare @reservedpages_param bigint
declare @usedpages_param bigint
declare @pages_param bigint
declare @index_size_param bigint
declare @unused_param bigint
declare @rows_param bigint

set @reservedpages_param = 0
set @usedpages_param = 0
set @pages_param = 0
set @index_size_param = 0
set @unused_param = 0
set @rows_param = 0

select
@cmd =
' use [' + cat_name + '];

declare @id int

select @id = object_id(''[' + sch_name + '].[' + tab_name + ']'')

SELECT
@reservedpages = sum(reserved_page_count),
@usedpages = sum(used_page_count),
@pages = sum(
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
),
@rowCount = sum(
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id;

IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204)) > 0
BEGIN

SELECT
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204) AND p.object_id = it.object_id;
END

SET @reservedpages = @reservedpages
SET @index_size = (CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8
SET @unused = (CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8'
from
#temp_table_list
where
rec_id = @counter

set @n_cmd = cast(@cmd as nvarchar(max))

exec sp_executesql
@n_cmd,
@parameters = N'@reservedpages bigint OUTPUT, @usedpages bigint OUTPUT, @pages bigint OUTPUT, @index_size bigint OUTPUT, @unused bigint OUTPUT, @rowCount bigint OUTPUT',
@reservedpages = @reservedpages_param OUTPUT,
@usedpages = @usedpages_param OUTPUT,
@pages = @pages_param OUTPUT,
@index_size = @index_size_param OUTPUT,
@unused = @unused_param OUTPUT,
@rowCount = @rows_param OUTPUT

update
#temp_table_list
set
row_count = @rows_param,
reserved_pages = @reservedpages_param,
reserved = @reservedpages_param * 8,
data = @pages_param * 8,
index_size = @index_size_param,
not_used = @unused_param,
pages = @pages_param,
used_pages = @usedpages_param,
used = @usedpages_param * 8
where
rec_id = @counter

set @counter = @counter + 1
end

select
cat_name,
sch_name,
tab_name,
row_count,
reserved_pages,
used_pages,
pages,
reserved,
used,
data,
index_size,
not_used
from
#temp_table_list
order by
cat_name,
sch_name,
tab_name

drop table #temp_table_list
end
go

-- example A:

exec dbo.proc_records_per_database 'AdventureWorks'

-- example A:

exec dbo.proc_records_per_database


/******************************************************************************************************************************************/


if exists(select * from sysobjects where id = object_id('dbo.proc_records_per_database') and type = 'P')
drop procedure dbo.proc_records_per_database
go

create procedure dbo.proc_records_per_database
@db_name sysname = NULL
as
begin
set nocount on

if @db_name is not null
begin
if not exists(select * from master.dbo.sysdatabases where name = @db_name and dbid not in (2, 3) /* skip tempdb and model */)
begin
raiserror('Database does not exist or can not be queried', 16, 1)
return
end
end

declare @dbs_table table(name sysname not null primary key)

--------------------------------------------------------------------------------------
-- prepare databases list

insert into @dbs_table
select
name
from
master.dbo.sysdatabases
where
(@db_name is null or
(@db_name is not null and name = @db_name)) and
dbid not in (2, 3)

create table #temp_table_list
(
rec_id int identity(1, 1) not null,
cat_name sysname not null,
sch_name sysname not null,
tab_name sysname not null,
row_count bigint not null default 0,
reserved_pages bigint not null default 0,
reserved bigint not null default 0,
pages bigint not null default 0,
data bigint not null default 0,
used_pages bigint not null default 0,????
used bigint not null default 0,
index_size bigint not null default 0,
not_used bigint not null default 0,
primary key(rec_id)
)

declare @cmd varchar(4000)
declare @n_cmd nvarchar(4000)
declare @dbx_name sysname

--------------------------------------------------------------------------------
-- get all tables

while 1 = 1
begin
set @dbx_name = NULL

select top 1 @dbx_name = [name] from @dbs_table

if @dbx_name is NULL
break

set @cmd = 'insert into #temp_table_list (cat_name, sch_name, tab_name) select ''' + @dbx_name + ''', '''', name from [' + @dbx_name + '].dbo.sysobjects where type = ''U'''

set @n_cmd = cast(@cmd as nvarchar(4000))

exec sp_executesql @n_cmd

delete from @dbs_table where [name] = @dbx_name
end

declare @max_rec_id int
set @max_rec_id = NULL

select @max_rec_id = max(rec_id) from #temp_table_list

if @max_rec_id is NULL
set @max_rec_id = -1

declare @counter int
set @counter = 1

declare @objname varchar(4000)

while @counter <= @max_rec_id
begin
--------------------------------------------------------------------------------
-- update table usage statistics

select
@cmd = 'use [' + cat_name + ']; dbcc updateusage(0, ''[' + tab_name + ']'') with no_infomsgs'
from
#temp_table_list
where
rec_id = @counter

set @n_cmd = cast(@cmd as nvarchar(4000))

-- print @n_cmd

exec sp_executesql @n_cmd

-----------------------------------------------------------------------------------------------
-- get table stats (based on the code of the procedure sp_spaceused)

declare @reservedpages_param bigint
declare @usedpages_param bigint
declare @pages_param bigint
declare @index_size_param bigint
declare @unused_param bigint
declare @rows_param bigint

set @reservedpages_param = 0
set @usedpages_param = 0
set @pages_param = 0
set @index_size_param = 0
set @unused_param = 0
set @rows_param = 0

select
@cmd =
'use [' + cat_name + '];
declare @id int
select @id = object_id(''[' + tab_name + ']'')
select @reservedpages = sum(reserved) from sysindexes where indid in (0, 1, 255) and id = @id
select @pages = sum(dpages) from sysindexes where indid < 2 and id = @id
select @pages = @pages + isnull(sum(used), 0) from sysindexes where indid = 255 and id = @id
select @index_size = (sum(used) - @pages) from sysindexes where indid in (0, 1, 255) and id = @id
select @usedpages = @index_size + @pages
set @index_size = @index_size * 8
select @unused = (@reservedpages - (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id)) * 8
select @rowCount = rows from sysindexes where indid < 2 and id = @id'
from
#temp_table_list
where
rec_id = @counter

set @n_cmd = cast(@cmd as nvarchar(4000))

-- print @n_cmd

exec sp_executesql
@n_cmd,
@parameters = N'@reservedpages bigint OUTPUT, @usedpages bigint OUTPUT, @pages bigint OUTPUT, @index_size bigint OUTPUT, @unused bigint OUTPUT, @rowCount bigint OUTPUT',
@reservedpages = @reservedpages_param OUTPUT,
@usedpages = @usedpages_param OUTPUT,
@pages = @pages_param OUTPUT,
@index_size = @index_size_param OUTPUT,
@unused = @unused_param OUTPUT,
@rowCount = @rows_param OUTPUT

update
#temp_table_list
set
row_count = @rows_param,
reserved_pages = @reservedpages_param,
reserved = @reservedpages_param * 8,
data = @pages_param * 8,
index_size = @index_size_param,
not_used = @unused_param,
pages = @pages_param,
used_pages = @usedpages_param,
used = @usedpages_param * 8
where
rec_id = @counter

set @counter = @counter + 1
end

select
cat_name,
tab_name,
row_count,
reserved_pages,
used_pages,
pages as data_pages,
reserved,
used,
data,
index_size,
not_used
from
#temp_table_list
order by
cat_name,
tab_name

drop table #temp_table_list
end
go

-- example:

exec dbo.proc_records_per_database 'AdventureWorks2000'

exec dbo.proc_records_per_database 'Northwind'

_____________________________________________________________________________________________________________
Paresh Prajapati
Arrow +919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Senthilkumar-331557
Senthilkumar-331557
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 121
If you want simply to check the Free space in the hard disks, then you can
use master..xp_fixeddrives.

It will give the amount of free space in the server.Smile
Paresh Prajapati
Paresh Prajapati
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 464
kumar99ms (12/24/2008)
Hi

HOw can u check free space in MS SQL SERVER SERVER

Using dbcc or system stored procedures could you tel me what are those ?


giving the exact information really appreciated to those guys

Tongue
Thanks


Are u about to get?

_____________________________________________________________________________________________________________
Paresh Prajapati
Arrow +919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36881 Visits: 9671
I know I'm 2 years behind, but on sql 2008 I need to do this to use sp_msforeachdb :

sp_msforeachdb 'use [?]; dbcc showfilestats'
poldi.rijke
poldi.rijke
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 476
you mean sql 2005 ;-)
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36881 Visits: 9671
poldi.rijke (9/2/2010)
you mean sql 2005 ;-)


Same difference.... it's the same core engine, different service pack. w00t
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search