| Author |
Topic |
|
Zakary
Starting Member
8 Posts |
 Posted - 2007-05-18 : 11:51:44
|
Hi everyone!Recently I've discover that the size of my database is growing fast, and I've reach the limit of 2gig for MSDE. I've been able to free some space to give the system back to the client, then I've perform some research to find out what, lead the database growing that fast. I've used the 'sp_spaceused' store procedure, to find out witch table consume all the space, to find that one specific table was quite large. [CODE]name rows reserved data index_size unused------------------ ----------- ------------------ ------------------ ------------------ ------------------[MyTable] 298411 1368344 KB 285728 KB 21976 KB 1060640 KB [/CODE]If you look i the above table, it show that 1.3 gig was reserve, and 285meg was really used. 1.3 Gig reserved  - Why do SQL reserve that space?- The table have 18 GUID column, and a big Varchar(6900), is it due to this kind of table structure?This database is also the main publisher of a big replication architecture |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-18 : 12:09:24
|
| Maybe fragmented, double check with 'dbcc showcontig'. |
 |
|
|
Zakary
Starting Member
8 Posts |
Posted - 2007-05-18 : 12:38:59
|
Thanks for your replyCan you help me in analyzing the result of the ShowContig command?DBCC SHOWCONTIG scanning '[MyTable]' table...Table: '[MyTable]' (2123154609); index ID: 0, database ID: 7TABLE level scan performed.- Pages Scanned................................: 22747- Extents Scanned..............................: 2846- Extent Switches..............................: 2845- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.93% [2844:2846]- Extent Scan Fragmentation ...................: 12.90%- Avg. Bytes Free per Page.....................: 1327.2- Avg. Page Density (full).....................: 83.60% |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-18 : 12:56:59
|
| Run dbcc updateusage or sp_spaceused @updateusage=true to fix these inaccuracies.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Zakary
Starting Member
8 Posts |
Posted - 2007-05-18 : 13:04:44
|
| Can you explain a bit more ?I just don't want to run procedure for fun, I would like to understand a bit more |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-05-18 : 13:05:40
|
| reserved size doesn't matter. This just reflects the number of extents allocated to a given table. The actual amount of data contained might occupy much less space. It is possible that this table once was very large and has been pruned back or some other maintenance caused this. If you aren't running out of space on disk then this is not something to worry about. btw, you can run the DBCC UPDATEUSAGE command to update the usage stats since they are usually inaccurate.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-05-18 : 13:06:06
|
quote:
Originally posted by Zakary
Can you explain a bit more ?I just don't want to run procedure for fun, I would like to understand a bit more
look it up in BOL.-ec |
|
|
|
Zakary
Starting Member
8 Posts |
Posted - 2007-05-18 : 13:24:52
|
But yes it matter because we are using MSDE, and it database is limited to 2 gig, and actually 1.3 gig is consume for data reservation.But I'll try what you, what you suggest.quote:
Originally posted by eyechart
reserved size doesn't matter. This just reflects the number of extents allocated to a given table. The actual amount of data contained might occupy much less space. It is possible that this table once was very large and has been pruned back or some other maintenance caused this. If you aren't running out of space on disk then this is not something to worry about. btw, you can run the DBCC UPDATEUSAGE command to update the usage stats since they are usually inaccurate.-ec
|
|
|
|
Zakary
Starting Member
8 Posts |
Posted - 2007-05-18 : 16:13:27
|
| OK I've solve it!I've discover that my table didn't have any clustered index, adding the index and executing dbcc dbreindex ('[MyTable]') and DBCC UPDATEUSAGE ('[MyDatabase]') free the space, and i'Ve use exec sp_spaceused and dbcc showcontig to monitor the space used by the table in detail and index fragmentation |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-18 : 16:43:06
|
| Creating index updates statistics, similar to 'dbcc updateusage' as said above. |
|
|
|
Zakary
Starting Member
8 Posts |
Posted - 2007-05-18 : 16:54:56
|
| Thanks to you rmiao and eyechart, for your reply :) |
|
|
|
|
MSDE - table space reserved