| Author |
Topic |
|
a_k_
Starting Member
23 Posts |
 Posted - 2004-05-13 : 12:25:33
|
| We store a big amount of images in our SQL Server DB in several tables, in column of type 'image' . Now we try to make space used by DB smaller by shrinking the images stored in the image fields. The average size of images before shrink - 100Kb, after shrink 65Kb. After completion of the shrinking process I run 'sp_spaceused' with updateusage option and found that the space occupied by table is close to be the same and most of the new free space is specified as unused. We will never add any new information to this table. Will SQL Server use the unused space of this table for the new data added to the other tables or we have to make SQL Server to reallocate? How we can reorganize the unused space? Thank you very much for any help,a_k_ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
a_k_
Starting Member
23 Posts |
Posted - 2004-05-13 : 13:32:56
|
| The answer of crazyjoe is:>> each image can occupy no LESS than one page (8KB) of data. shrinking your images down to >> less than that won't help you any at all.But the size of images in my table is much bigger then 8Kb. The average size of images before shrink - 100Kb, after shrink 65Kb. So, shrink suppose to help making space allocated by table smaller. But it does not. I run 'sp_spaceused' with updateusage option and receive the following result: data size becomes smaller but reserved size is the same, all the difference is in the unused space.What can I do in order to shrink unused space of the table? Thank you,a_k_ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-13 : 13:35:47
|
| You can't.Have you considered storing the images on disk and a pointer in the database instead as is highly recommended?Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-13 : 13:41:25
|
| ??? Have you ran a DBCC SHRINKDB and done a reindex since you did this? If not and you don't have autoshrink on (which I wouldn't recommend turning on if you don't) then how is it supposed to free up the space?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
a_k_
Starting Member
23 Posts |
Posted - 2004-05-13 : 17:05:05
|
| Should I use DBCC SHRINKDATABASE with NOTRUNCATE option in order to reuse the unused space of this table for the new data in the other tables? If I do not run SHRINKDATABASE, will SQL Server reuse this unused space automatically or not?Why is it highly recommended storing the images on disk instead of database?Thank you,a_k_ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-13 : 18:23:10
|
quote:
Originally posted by a_k_
Why is it highly recommended storing the images on disk instead of database?
Just search the forums for the reason, you'll find it discussed here a lot. Here's a start:http://www.sqlteam.com/item.asp?ItemID=986http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6014Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-13 : 22:39:26
|
| Should I use DBCC SHRINKDATABASE with NOTRUNCATE option in order to reuse the unused space of this table for the new data in the other tables? If I do not run SHRINKDATABASE, will SQL Server reuse this unused space automatically or not?You need to run it with TRUNCATE. It will not reuse the unused space for one table for the space of another table.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
a_k_
Starting Member
23 Posts |
Posted - 2004-05-14 : 18:19:45
|
| Thank you for your help. I tried DBCC SHRINKDATABASE with TRUNCATE on test DB. It does not remove unused space created by fragmentation of the image fields. I run 'sp_spaceused' with 'updateusage' option for this table and all the values are the same (the same size of unused data for this table) . It seems like this method does not move data within space where image data stored.Is there any method to reorganize image data space? The only method that I found is to copy all the contents of the table to a new table. I hope it suppose to be some easier way. The table size is more then 100GB.Thank you,a_k_ |
|
|
|
ibtrump79
Starting Member
1 Post |
Posted - 2004-05-25 : 20:57:55
|
quote:
Originally posted by a_k_
Thank you for your help. I tried DBCC SHRINKDATABASE with TRUNCATE on test DB. It does not remove unused space created by fragmentation of the image fields. I run 'sp_spaceused' with 'updateusage' option for this table and all the values are the same (the same size of unused data for this table) . It seems like this method does not move data within space where image data stored.Is there any method to reorganize image data space? The only method that I found is to copy all the contents of the table to a new table. I hope it suppose to be some easier way. The table size is more then 100GB.Thank you,a_k_
Do you have a clustered index on the table with all the unused space in it? If not, add a clustered index then shrink the database. This will physically reorder the data and allow the unused space to be regained by shrinking the database. If you will not be using this table very much, you can then drop the clustered index. However, if you are still going to be updating the table a lot, then dropping the clustered index may cause the unused space to get large again so you may want to leave the clustered index on the table. |
|
|
|
|
Too big amount of unused table space, continue