Qfyilyi

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?

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).

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.