1

I know when you delete data, that data is only "marked" as "deleted' for faster rollback. The only way to really delete this data is to run something that reads the datapage, such as a table scan or to wait for the "ghost cleanup task".

I've deleted a LOT of data and I can see my database has a lot of free space, which is great. I'm not going to shrink the database since I don't need space right now; this question is just about how the delete process works.

Let's say, after this delete, I could run a script that shows me 20 indexes that need to be rebuilt, and some more to reorganize. Well, inside them I'm sure there's a lot of ghost data. If I run a ALTER INDEX ... REBUILD ( organize or rebuild ) will the table consume less space in the database? I mean, can I gain space by running a reindex operation after a delete? Or when data is marked as ghost data, is that data already not consuming space in the table?

2

If by your comment

I mean, can I gain space only runing reindexes after a delete? or when a data is marked as a ghost data, it's already not using space.

you are asking whether the space taken up by a deleted (ghosted) records is immediately available for reuse - the documentation on Ghost cleanup process guide is pretty clear. (highlighting mine)

Ghost records

Records that are deleted from a leaf level of an index page aren't physically removed from the page - instead, the record is marked as 'to be deleted', or ghosted. This means that the row stays on the page but a bit is changed in the row header to indicate that the row is really a ghost.

Further down in the documentation

some action needs to be taken to remove the ghosted records. One option is to execute an index rebuild, which will move data around on pages. Another option is to manually run sp_clean_db_free_space (to clean all database data files) or sp_clean_db_file_free_space (to clean a single database datafile), which will delete ghosted records.

None of these options automatically shrink your database files.

On SQL 2008, ghost cleanup automatically runs every 10 seconds (unless disabled).

  • No No. I think I asked the wrong way. I dont want to shrink the database. I would like to know, if that "ghost" data is taking space. As a non-native english speaker, it's hard to ask what I want haha. I mean, I have a table with 1gb of data. then I delete everything. Not talking about free space, I would like to know if that data is "still there", even in "ghost mode". – Racer SQL Dec 13 '18 at 14:02
  • 1
    @RacerSQL - Until Ghost cleanup (or rebuild index) processes run, the ghost record is definitely taking up space - see the documentation links in my answer. On SQL 2008, ghost cleanup automatically runs every 10 seconds (unless disabled). – Scott Hodgin Dec 13 '18 at 14:04
  • Thank you. Thats wxactly what I would like to know. =D – Racer SQL Dec 13 '18 at 14:05
3

If I run a REINDEX ( organize or rebuild ) is my database going to be smaller?

No, after reindex or rebuild database size may only increase. This is because when you do a rebuild(offline), a new copy of index will be built, and only then the old index will be dropped. This means that at a certain moment your db will accommodate 2 copies of the same index.

I.e., if your database is 1Tb with 10Gb free, and inside this db you have an index of 500Gb, after offline index rebuild the size of your database will be nearly 1,5Tb with 500Gb free.

This is without log growth. Log file will grow in any case, it can grow less if you rebuild offline and database recovery model is simple or bulk logged. The log will grow proportionally of your index size if you rebuild it offline in full recovery model (because every index page will be fully logged). Finally, if you rebuild online or reorganize, the log will grow even more because these operations are fully logged. (Note: if index is not fragmented, reorg will do nothing so the log will not grow).

Rebuilt index will not preserve ghost rows, but this does not mean that database size decrease.

This miens that unused space of your database will increase. But these free pages will still reserved to database.

The only mode to decrease database size is to shrink it. I still did not understand if you want to shrink or not. Shrinking means to release free database pages to OS. It is resource intensive process that is not recommended unless you really need to release some space to OS.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.