Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ccarlin
Starting Member
4 Posts |
 Posted - 2009-07-20 : 20:50:21
|
| I have a fairly large table that has 3 image columns in it. One image is typically fairly large (1-5MB), the other two are fairly small one is around 2-4k the other around 30-60k. So in order to recover space we typically archive off the large image to another location (off of SQL). Well originally I would null out all three columns when the move took place and I recovered the space. I recently modified it to only null out the large image and I am not getting ANY space back. I have tried several DBCCs (Checktable, checkdb, cleantable). Nothing seems to give back the space. I have aproximately 15gig of images but the table size is over 23gig.I am out of ideas in how to recover this space. So any help would be greatly appreciated.Chuck |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ccarlin
Starting Member
4 Posts |
Posted - 2009-07-21 : 23:33:04
|
| Tried a DBCC DBREINDEX then ran an sp_spaceused @tableName, 'true'Still no change. Any other suggestions? |
 |
|
|
ccarlin
Starting Member
4 Posts |
Posted - 2009-07-24 : 21:09:14
|
| So I bcp'd the data out to a file and the file was less than 16GB however the table is over 24gig. Any ideas? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-24 : 21:53:55
|
| any non-clustered indexes? show us the output from sp_spaceusedwhat's fill factor on index(es)?and did u alter index rebuild (like Tara said) or did you DBCC? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-07-24 : 22:06:02
|
| Have you shrunk it ? |
|
|
|
ccarlin
Starting Member
4 Posts |
Posted - 2009-07-27 : 12:04:11
|
| Can't shrink it (well I can recover 100MB but there is around 8Gig of unused space somewhere).Here is the sp_spacedused on the table:rows 3427978 reserved 24698744 KB data 24498672 KB index_size 96920 KB unused 103152 KBI will try to rebuild the clusted index with the alter not the DBCC tonight and see if that helps. Again thanks for all the suggestions. |
|
|
|
|
|
|
Not recovering space after column nulled out