Lots of unused space
-
Hi everyone,
I have a question I have a simple table in my SQL-2000 database which is used to store documents in the database. The documents are stored in an image field. Now the problem I stored about 3300 records and the documents stored are about 100 mb. But the table uses a lot more space namely about 700 mb so 600 mb of data is unused. So 600 mb is wasted, is there anything that you can do to reduce the unused space?
This shows the data used:
Tablename: tblDocument
Rows: 3351
Reserved: 741520 KB
Data: 106904 KB
Index_size: 16 KB
unused: 634600 KB
This is the table dafinition:
tblDocument (design)
==================================================
DocumentID bigint 8 0
TPID bigint 8 0
Object varchar 255 1
ObjectID bigint 8 1
Type int 4 1
ParentId bigint 8 1
DocumentAlgemeenID bigint 8 1
Name varchar 255 1
Description varchar 2000 1
ContentType varchar 255 1
ContentLength bigint 8 1
DocumentDate datetime 8 1
DocumentName varchar 255 1
ImageSrc varchar 255 1
Document image 16 1
I hope anyone nows because it's going to cause aproblem for me.
--
Best regards,
P. Keukens
-
- Did you define indexes for this table ?
- Lets hope you also defined a (unique if possible) clustering index.
- you may want to schedule maintenance for the database/table
(or write your own dbcc dbreindex,..)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi thank you for your answer!
I have a primary key on the DocumentID (autonumbering) it is clustered with a fill factor of 90%
The database is on a shared server I don't think I have the rights to perform a dbcc reindex but I will have a look.
So any other solutions?
-
Sorry fill factor is 99% istead of 90%
-
Keep in mind dbcc dbreindex will keep a lock on your table, so others may not be able to use it during the reindex !
If you have updates/deletes on your table, rows may get relocated, which can result in unused space because forward pointers will be kept at the initial page of the row.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You can select the "Auto Shrink" option in EM database properties, or ask your DBA to do that. This should get rid of the extra unused space without requiring a DBCC command.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
I'd advise against using autoshrink, this may compact your extents increasing mixed extents and degrading performance, it might also autoshrink at an inconvenient time.
I assume your table has a large number of deletes, updates etc. which leaves gaps in your structure?
There is a possible solution but if you can't rebuild an index I don't think you'll be able to use the solution either - what does a dbcc showcontig display?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi Colin and others,
It looks like the problem was just the sp_spaceused stored proc it didn't show the right figures. I asked the dba to perform a UpdateUsage on the table and then my figures looked like:
Tablename: tblDocument
Rows: 3564
Reserved 798720 KB
Data: 778304 KB
Index_size: 16 KB
unused space: 20400 KB
So it lookes like all the space is just used by data. But still it is strange that the stored proc sp_spaceused didn't return the right figures. Below I also put the showcontig on the table this all looks pretty normal, right?
DBCC SHOWCONTIG scanning 'tblDocument' table...
Table: 'tblDocument' (5575058); index ID: 1, database ID: 42
TABLE level scan performed.
- Pages Scanned................................: 104
- Extents Scanned..............................: 14
- Extent Switches..............................: 13
- Avg. Pages per Extent........................: 7.4
- Scan Density [Best Count:Actual Count].......: 92.86% [13:14]
- Logical Scan Fragmentation ..................: 0.96%
- Extent Scan Fragmentation ...................: 57.14%
- Avg. Bytes Free per Page.....................: 130.7
- Avg. Page Density (full).....................: 98.39%
Best regards Patrick
-
It's a funny one dbcc updateusage , as a matter of course I always issue this command against every database every day ( if i can ) as part of my maint procedures. Technically I'm told I shouldn't need to - but old habits , like old dba's, die hard.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
indeed sp_updatestats and dbcc updateusage should be part of your maintenance job !
and like you experianced, should also be the first step when you have doubts about the statistics
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply