ghost cleanup, delete and reindex












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?










share|improve this question





























    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?










    share|improve this question



























      1












      1








      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?










      share|improve this question















      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?







      sql-server sql-server-2008






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Dec 13 '18 at 15:35









      Tom V

      13.8k74676




      13.8k74676










      asked Dec 13 '18 at 13:14









      Racer SQL

      2,94942358




      2,94942358






















          2 Answers
          2






          active

          oldest

          votes


















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






          share|improve this answer























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






          share|improve this answer























            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "182"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224877%2fghost-cleanup-delete-and-reindex%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









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






            share|improve this answer























            • 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


















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






            share|improve this answer























            • 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
















            2












            2








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






            share|improve this answer














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







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Dec 13 '18 at 14:05

























            answered Dec 13 '18 at 14:00









            Scott Hodgin

            16.8k21534




            16.8k21534












            • 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




















            • 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


















            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






            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




            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




            @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






            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.






            share|improve this answer




























              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.






              share|improve this answer


























                3












                3








                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.






                share|improve this answer















                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.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Dec 13 '18 at 15:36









                Tom V

                13.8k74676




                13.8k74676










                answered Dec 13 '18 at 13:51









                sepupic

                6,756817




                6,756817






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.





                    Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                    Please pay close attention to the following guidance:


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224877%2fghost-cleanup-delete-and-reindex%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Biblatex bibliography style without URLs when DOI exists (in Overleaf with Zotero bibliography)

                    ComboBox Display Member on multiple fields

                    Is it possible to collect Nectar points via Trainline?