Index deletion and Backup size
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
If I delete some huge non-clustered indexes will the backup size of that database also decrease?
I am facing issue with huge backup files and don't have enough disk space to hold those backups.
I have some unused indexes of size 40 GB and above; if I delete this index will the backup file size reduce?
sql-server backup
edited Jan 11 at 20:04
Max Vernon
52.3k13115232
asked Jan 11 at 18:39
Aditya SawantAditya Sawant
232
add a comment |
If I delete some huge non-clustered indexes will the backup size of that database also decrease?
I am facing issue with huge backup files and don't have enough disk space to hold those backups.
I have some unused indexes of size 40 GB and above; if I delete this index will the backup file size reduce?
sql-server backup
edited Jan 11 at 20:04
Max Vernon
52.3k13115232
asked Jan 11 at 18:39
Aditya SawantAditya Sawant
232
add a comment |
If I delete some huge non-clustered indexes will the backup size of that database also decrease?
I am facing issue with huge backup files and don't have enough disk space to hold those backups.
I have some unused indexes of size 40 GB and above; if I delete this index will the backup file size reduce?
sql-server backup
edited Jan 11 at 20:04
Max Vernon
52.3k13115232
asked Jan 11 at 18:39
Aditya SawantAditya Sawant
232
If I delete some huge non-clustered indexes will the backup size of that database also decrease?
I am facing issue with huge backup files and don't have enough disk space to hold those backups.
I have some unused indexes of size 40 GB and above; if I delete this index will the backup file size reduce?
sql-server backup
sql-server backup
edited Jan 11 at 20:04
Max Vernon
52.3k13115232
asked Jan 11 at 18:39
Aditya SawantAditya Sawant
232
edited Jan 11 at 20:04
Max Vernon
52.3k13115232
asked Jan 11 at 18:39
Aditya SawantAditya Sawant
232
edited Jan 11 at 20:04
Max Vernon
52.3k13115232
edited Jan 11 at 20:04
Max Vernon
52.3k13115232
edited Jan 11 at 20:04
Max Vernon
52.3k13115232
52.3k13115232
asked Jan 11 at 18:39
Aditya SawantAditya Sawant
232
asked Jan 11 at 18:39
Aditya SawantAditya Sawant
232
asked Jan 11 at 18:39
Aditya SawantAditya Sawant
232
232
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If you delete 40GB of indexes, then certain backups will be smaller, such as:
- Full
- File
- Filegroup
Some backups won't generally be smaller, such as:
- Log
Differential backups (Full, File, Filegroup) may or may not be much smaller as it depends if any objects those non-clustered indexes are created on are actually having data modifications happening.
Some backup sizes might not go down by 40GB, if for example compression is already being used or those indexes are on a read_only filegroup that isn't generally backed up via filegroup backups.
However, overall, the answer is "Yes, it should be smaller".
answered Jan 11 at 18:51
Sean GallardySean Gallardy
17k22654
A log backup can also be smaller, if lots of modifications were performed against those tables that had the removed indexes. Since index modifications are also logged, then the presence of indexes can affect log backup size. By how much or if it is at all noticeable, yes, it depends. (Which is probably what Sean meant by "in general" :-) ).
– Tibor Karaszi
Jan 14 at 12:40
add a comment |
YES - Deleting an index will cause your backup to be smaller. The log/Diff may be larger than normal, but the actual backup will be smaller. The drop of the index is a meta-data only operation and thus minimally logged, but there is still some logging.
However - If you need to re-create that index then you aren't really buying yourself anything.
Have you considered trying to compress your backups? This has been available since 2005 (Enterprise) and 2008R2 (all editions) but is not typically set by default. Are you backing up to a new file each time or are you adding a new backup to the same file?
answered Jan 11 at 18:51
Jonathan FiteJonathan Fite
4,103818
I am already using compression and the indexes which I need to drop are unused. Also, I am creating new file for backup with some retention period. Guess I will delete those unused indexes and see how much backup size is reduced. Thank you for the response..
– Aditya Sawant
Jan 11 at 19:16
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%2f226937%2findex-deletion-and-backup-size%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 you delete 40GB of indexes, then certain backups will be smaller, such as:
- Full
- File
- Filegroup
Some backups won't generally be smaller, such as:
- Log
Differential backups (Full, File, Filegroup) may or may not be much smaller as it depends if any objects those non-clustered indexes are created on are actually having data modifications happening.
Some backup sizes might not go down by 40GB, if for example compression is already being used or those indexes are on a read_only filegroup that isn't generally backed up via filegroup backups.
However, overall, the answer is "Yes, it should be smaller".
answered Jan 11 at 18:51
Sean GallardySean Gallardy
17k22654
A log backup can also be smaller, if lots of modifications were performed against those tables that had the removed indexes. Since index modifications are also logged, then the presence of indexes can affect log backup size. By how much or if it is at all noticeable, yes, it depends. (Which is probably what Sean meant by "in general" :-) ).
– Tibor Karaszi
Jan 14 at 12:40
add a comment |
If you delete 40GB of indexes, then certain backups will be smaller, such as:
- Full
- File
- Filegroup
Some backups won't generally be smaller, such as:
- Log
Differential backups (Full, File, Filegroup) may or may not be much smaller as it depends if any objects those non-clustered indexes are created on are actually having data modifications happening.
Some backup sizes might not go down by 40GB, if for example compression is already being used or those indexes are on a read_only filegroup that isn't generally backed up via filegroup backups.
However, overall, the answer is "Yes, it should be smaller".
answered Jan 11 at 18:51
Sean GallardySean Gallardy
17k22654
A log backup can also be smaller, if lots of modifications were performed against those tables that had the removed indexes. Since index modifications are also logged, then the presence of indexes can affect log backup size. By how much or if it is at all noticeable, yes, it depends. (Which is probably what Sean meant by "in general" :-) ).
– Tibor Karaszi
Jan 14 at 12:40
add a comment |
If you delete 40GB of indexes, then certain backups will be smaller, such as:
- Full
- File
- Filegroup
Some backups won't generally be smaller, such as:
- Log
Differential backups (Full, File, Filegroup) may or may not be much smaller as it depends if any objects those non-clustered indexes are created on are actually having data modifications happening.
Some backup sizes might not go down by 40GB, if for example compression is already being used or those indexes are on a read_only filegroup that isn't generally backed up via filegroup backups.
However, overall, the answer is "Yes, it should be smaller".
answered Jan 11 at 18:51
Sean GallardySean Gallardy
17k22654
If you delete 40GB of indexes, then certain backups will be smaller, such as:
- Full
- File
- Filegroup
Some backups won't generally be smaller, such as:
- Log
Differential backups (Full, File, Filegroup) may or may not be much smaller as it depends if any objects those non-clustered indexes are created on are actually having data modifications happening.
Some backup sizes might not go down by 40GB, if for example compression is already being used or those indexes are on a read_only filegroup that isn't generally backed up via filegroup backups.
However, overall, the answer is "Yes, it should be smaller".
answered Jan 11 at 18:51
Sean GallardySean Gallardy
17k22654
answered Jan 11 at 18:51
Sean GallardySean Gallardy
17k22654
answered Jan 11 at 18:51
Sean GallardySean Gallardy
17k22654
answered Jan 11 at 18:51
Sean GallardySean Gallardy
17k22654
17k22654
A log backup can also be smaller, if lots of modifications were performed against those tables that had the removed indexes. Since index modifications are also logged, then the presence of indexes can affect log backup size. By how much or if it is at all noticeable, yes, it depends. (Which is probably what Sean meant by "in general" :-) ).
– Tibor Karaszi
Jan 14 at 12:40
add a comment |
A log backup can also be smaller, if lots of modifications were performed against those tables that had the removed indexes. Since index modifications are also logged, then the presence of indexes can affect log backup size. By how much or if it is at all noticeable, yes, it depends. (Which is probably what Sean meant by "in general" :-) ).
– Tibor Karaszi
Jan 14 at 12:40
A log backup can also be smaller, if lots of modifications were performed against those tables that had the removed indexes. Since index modifications are also logged, then the presence of indexes can affect log backup size. By how much or if it is at all noticeable, yes, it depends. (Which is probably what Sean meant by "in general" :-) ).
– Tibor Karaszi
Jan 14 at 12:40
A log backup can also be smaller, if lots of modifications were performed against those tables that had the removed indexes. Since index modifications are also logged, then the presence of indexes can affect log backup size. By how much or if it is at all noticeable, yes, it depends. (Which is probably what Sean meant by "in general" :-) ).
– Tibor Karaszi
Jan 14 at 12:40
add a comment |
YES - Deleting an index will cause your backup to be smaller. The log/Diff may be larger than normal, but the actual backup will be smaller. The drop of the index is a meta-data only operation and thus minimally logged, but there is still some logging.
However - If you need to re-create that index then you aren't really buying yourself anything.
Have you considered trying to compress your backups? This has been available since 2005 (Enterprise) and 2008R2 (all editions) but is not typically set by default. Are you backing up to a new file each time or are you adding a new backup to the same file?
answered Jan 11 at 18:51
Jonathan FiteJonathan Fite
4,103818
I am already using compression and the indexes which I need to drop are unused. Also, I am creating new file for backup with some retention period. Guess I will delete those unused indexes and see how much backup size is reduced. Thank you for the response..
– Aditya Sawant
Jan 11 at 19:16
add a comment |
YES - Deleting an index will cause your backup to be smaller. The log/Diff may be larger than normal, but the actual backup will be smaller. The drop of the index is a meta-data only operation and thus minimally logged, but there is still some logging.
However - If you need to re-create that index then you aren't really buying yourself anything.
Have you considered trying to compress your backups? This has been available since 2005 (Enterprise) and 2008R2 (all editions) but is not typically set by default. Are you backing up to a new file each time or are you adding a new backup to the same file?
answered Jan 11 at 18:51
Jonathan FiteJonathan Fite
4,103818
I am already using compression and the indexes which I need to drop are unused. Also, I am creating new file for backup with some retention period. Guess I will delete those unused indexes and see how much backup size is reduced. Thank you for the response..
– Aditya Sawant
Jan 11 at 19:16
add a comment |
YES - Deleting an index will cause your backup to be smaller. The log/Diff may be larger than normal, but the actual backup will be smaller. The drop of the index is a meta-data only operation and thus minimally logged, but there is still some logging.
However - If you need to re-create that index then you aren't really buying yourself anything.
Have you considered trying to compress your backups? This has been available since 2005 (Enterprise) and 2008R2 (all editions) but is not typically set by default. Are you backing up to a new file each time or are you adding a new backup to the same file?
answered Jan 11 at 18:51
Jonathan FiteJonathan Fite
4,103818
YES - Deleting an index will cause your backup to be smaller. The log/Diff may be larger than normal, but the actual backup will be smaller. The drop of the index is a meta-data only operation and thus minimally logged, but there is still some logging.
However - If you need to re-create that index then you aren't really buying yourself anything.
Have you considered trying to compress your backups? This has been available since 2005 (Enterprise) and 2008R2 (all editions) but is not typically set by default. Are you backing up to a new file each time or are you adding a new backup to the same file?
answered Jan 11 at 18:51
Jonathan FiteJonathan Fite
4,103818
answered Jan 11 at 18:51
Jonathan FiteJonathan Fite
4,103818
answered Jan 11 at 18:51
Jonathan FiteJonathan Fite
4,103818
answered Jan 11 at 18:51
Jonathan FiteJonathan Fite
4,103818
4,103818
I am already using compression and the indexes which I need to drop are unused. Also, I am creating new file for backup with some retention period. Guess I will delete those unused indexes and see how much backup size is reduced. Thank you for the response..
– Aditya Sawant
Jan 11 at 19:16
add a comment |
I am already using compression and the indexes which I need to drop are unused. Also, I am creating new file for backup with some retention period. Guess I will delete those unused indexes and see how much backup size is reduced. Thank you for the response..
– Aditya Sawant
Jan 11 at 19:16
I am already using compression and the indexes which I need to drop are unused. Also, I am creating new file for backup with some retention period. Guess I will delete those unused indexes and see how much backup size is reduced. Thank you for the response..
– Aditya Sawant
Jan 11 at 19:16
I am already using compression and the indexes which I need to drop are unused. Also, I am creating new file for backup with some retention period. Guess I will delete those unused indexes and see how much backup size is reduced. Thank you for the response..
– Aditya Sawant
Jan 11 at 19:16
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.
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%2f226937%2findex-deletion-and-backup-size%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
