June 10, 2009 at 6:04 am
i have droped all index+Primary key.
why after i deleted all the indexe's i still see that it holds a space of ~1GB?
can i clean that too?
June 10, 2009 at 6:43 am
Deleting data does not free up the space on the hard-disk.
You need to shrink the files
see DBCC SHRINKFILE in BoL
June 10, 2009 at 7:05 am
Tom Brown (6/10/2009)
Deleting data does not free up the space on the hard-disk.You need to shrink the files
see DBCC SHRINKFILE in BoL
Don't shrink the files unless you're out of disk space and have no other recourse. Search this site for "shrinkfile" and read the many comments already posted about why you do NOT want to shrink files and what you will need to do after the process if you do.
-- You can't be late until you show up.
June 10, 2009 at 8:26 am
tosscrosby (6/10/2009)
Tom Brown (6/10/2009)
Deleting data does not free up the space on the hard-disk.You need to shrink the files
see DBCC SHRINKFILE in BoL
Don't shrink the files unless you're out of disk space and have no other recourse. Search this site for "shrinkfile" and read the many comments already posted about why you do NOT want to shrink files and what you will need to do after the process if you do.
Its a debatable point, but after an extrordinry operation - such as dropping an large index, then I'd say it was OK. But not as a regular operation.
June 10, 2009 at 10:05 am
I'm not saying never do it as one-off events do occur. The point is, and I should've stated it in my previous post, if you shrink the data file (not the log - that's a different story), you should rebuild all your indexes as it going to ruin their usefulness. If you shrink the data file, don't make it so small that it's going to grow soon thereafter, especially due to the rebuild. And lead to file level fragmentation....yada, yada, yada. 😉
-- You can't be late until you show up.
June 10, 2009 at 12:41 pm
i want to mention that the certin tables that there index were removed, wont be updated ever again!
the question if i will want in the future to put an index, will it be ok to do DBCC SHRINKFILE ?
and is there a way to make the shrink only on a certin table?
June 11, 2009 at 1:47 am
No you can't just shrink a table on its own. - SHRINKFILE acts on a database filegroup. (data or log)
The table would have to reside on its own different filegroup to shrink it independently.
If you are only keeping the data for an unlikely possible future reference, and you don't need the data to be available immediately, then you could move it to a completely new database and archive it, even detach it and compress the files. I guess if you are primarily concerned with disk space then this could be the way to go.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply