[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

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

0
James Murrell
Asked:
James Murrell
  • 7
  • 6
2 Solutions
 
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
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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]Billing EngineerCommented:
tables without clustered indexes are heaps... badly managed.
tables with clustered indexes are managed as b-tree structures ... much better.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
James MurrellProduct SpecialistAuthor Commented:
Thanks once again angeleyes.......
0
 
James MurrellProduct SpecialistAuthor Commented:
where would we be without you angeleyes
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now