Managing the Storage
-
Hello!
I need help?
I have one database with 7 GB size, and my HDD is 9 GB, and I see that soon the database will grow how to find the solution without changing hardware equipments!
plz advice, much appreciate!
Thnx!
:crying:
-
Use the procedure below to get the table size, used table and free table in KB. This will allow you to identify bloated tables and reclaim disk space by issuing an sp_spacused 'tableName',@updateusage=true
CREATE PROCEDURE usp_table_space_general
@major int=1,
@minor int=0,
@db sysname,
@filegroup sysname = '',
@table_owner sysname = ''
AS
declare @total_db_space_kb decimal(28,0),
@sql varchar(2000)
BEGIN
set nocount on
/*
*********************************************************************
* create worktable
*********************************************************************
*/
create table #table_info
(owner sysname NULL,
tablename sysname NULL,
partition_number int NULL,
file_group nchar(128) NULL,
table_rows int NULL,
table_reserved decimal(28,0) NULL,
table_used decimal(28,0) NULL)
create table #db_space
(dbspace decimal(28,0) NULL)
/*
*********************************************************************
* gather base table space info
*********************************************************************
*/
select @sql = 'use [' + @db + '] SELECT SCHEMA_NAME(a.schema_id),
a.name,
b.partition_number,
FILEGROUP_NAME(c.data_space_id),
max(b.rows),
table_reserved = SUM(c.total_pages),
table_used = SUM(case c.type when 2 then c.used_pages else c.data_pages end)
FROM sys.tables a,
sys.partitions b,
sys.allocation_units c
WHERE a.object_id = b.object_id and
b.partition_id = c.container_id and
a.type in (''U'',''S'') and
b.index_id in (0,1,255) '
if @filegroup = ''
begin
end
else
begin
select @sql = @sql + ' and filegroup_name(c.data_space_id) = ''' + @filegroup + ''''
end
if @table_owner = ''
begin
end
else
begin
select @sql = @sql + ' and SCHEMA_NAME(a.schema_id) = ''' + @table_owner + ''''
end
select @sql = @sql + ' group by SCHEMA_NAME(a.schema_id),a.name,partition_number,filegroup_name(c.data_space_id)'
insert into #table_info exec (@sql)
/*
*********************************************************************
* get total space (in kb) of database
*********************************************************************
*/
insert into #db_space exec ('use [' + @db + '] select total_space = 1024 * (select sum(convert(decimal(35,2),size)) / convert( float, (1048576 / (select low from master.dbo.spt_values where number = 1 and type = ''E''))) from dbo.sysfiles) - (1024 * (select sum(convert(decimal(35,2),size)) / convert( float, (1048576 / (select low from master.dbo.spt_values where number = 1 and type = ''E''))) from dbo.sysfiles where (status & 0x40)=0x40))' )
select @total_db_space_kb = dbspace from #db_space
/*
*********************************************************************
* present table space info
*********************************************************************
*/
select owner = isnull(owner,'Unknown'),
tablename,
partition_number,
file_group = rtrim(case
when file_group IS NULL then 'LOG'
else file_group
end),
table_rows,
table_reserved_kb = sum(convert(decimal(35,2),table_reserved * 8)),
table_used_kb = sum(convert(decimal(35,2),table_used * 8)),
table_free_kb = sum(convert(decimal(35,2),table_reserved * 8)) - sum(convert(decimal(35,2),table_used * 8)),
pct_table_used = convert(decimal(35,2),100 * (sum(convert(decimal(35,2),table_used * 8)))/
(sum(case(table_reserved) when 0 then 1 else convert(decimal(35,2),table_reserved) end * 8))),
pct_of_database = convert(decimal(35,2),100 * (sum(convert(decimal(35,2),table_reserved * 8)) / @total_db_space_kb))
from #table_info
group by owner, tablename, partition_number, file_group,table_rows
order by 1,2,3
drop table #table_info
drop table #db_space
RETURN(0)
END
Tommy
Follow @sqlscribe -
What's the solution you want? this makes no sense. If you need the data, you need more disk space. If you can delete data, do that.
-
I agree with you Steve ....hmmm I know that if I delete data I will win more space but these data is important!
and no possible to change the HDD! Do we have any solution to export data in txt files and if I need them backing up on DB!
also for me is stupid situation but I'm asking to solve it! And I don't know how can I do!?
ok if we dont have idea I will accept as impossible ( and I will sugest to add more HDD)!
Tommy I will try you script but little bit confused! :ermm:
thnx very much Steve!
Dugi
-
Do you have only one HDD? If you have more HDD you could try creating more filegroups.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply