ghost cleanup, delete and reindex
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
add a comment |
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
add a comment |
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
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
sql-server sql-server-2008
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
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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
add a comment |
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.
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
add a comment |
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
add a comment |
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 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).
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
add a comment |
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
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
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
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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