What does Reserved space mean

What does Reserved space mean when running sp_spaceused ?

reason for asking a 4 year old db has grown 6gb in 30 days... normally only grows 100mb per month max

this is in sql 2000 sp4

LVL 31
James MurrellProduct SpecialistAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
tables without clustered indexes are heaps... badly managed.
tables with clustered indexes are managed as b-tree structures ... much better.
0
 
RiteshShahCommented:
reserved space mean actual space hold by the sql engine, suppose 1GB is reserved space and used & indexed space is 500MB than you can truncate the file and can reclaim remaining space.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the sp_spaceused return, exactly?
do you have tables without clustered indexes?
what is the fragmentation of the tables?

and the "guess into the blue":
you are not eventually hit by sql injection, which appends some <script> into all the text/varchar fields, blowing up the size ?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
James MurrellProduct SpecialistAuthor Commented:
ok run script below

results

name                          rows                     reserved               data             index_size       unused  

Audit_Booking_Plan       8365403        6574168 KB       1222632 KB       24 KB          5351512 KB

Have another 200 db same none ever had this
create table #t
(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
 
declare @id nvarchar(128)
declare c cursor for
select name from sysobjects where xtype='U'
 
open c
fetch c into @id
 
while @@fetch_status = 0 begin
 
insert into #t
exec sp_spaceused @id
 
fetch c into @id
end
 
close c
deallocate c
 
select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc
 
drop table #t

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please answer all my questions :)
0
 
James MurrellProduct SpecialistAuthor Commented:
sorry Angeleyes, was not able to remote access machine last night..

unsure about Clustered indexes

DBCC SHOWCONTIG scanning 'Audit_Booking_Plan' table...
Table: 'Audit_Booking_Plan' (209435820); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 161283
- Extents Scanned..............................: 110618
- Extent Switches..............................: 110617
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 18.23% [20161:110618]
- Extent Scan Fragmentation ...................: 12.60%
- Avg. Bytes Free per Page.....................: 4917.9
- Avg. Page Density (full).....................: 39.24%
0
 
James MurrellProduct SpecialistAuthor Commented:
apologies major problems kept me away

do you have tables without clustered indexes? only one one that table no other clustered on any table
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
confused.... do your tables have clustered indexes or not?
all?
0
 
James MurrellProduct SpecialistAuthor Commented:
only table called Audit_Booking_Plan  has clustered index

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see.
please get a clustered index on all the tables.
0
 
James MurrellProduct SpecialistAuthor Commented:
how would that help? sorry to ask something i should already know
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
0
 
James MurrellProduct SpecialistAuthor Commented:
Thanks once again angeleyes.......
0
 
James MurrellProduct SpecialistAuthor Commented:
where would we be without you angeleyes
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.