Database Maintenance Job and Backup Scheduling
I would request to have expert advise on scheduling maintenance and backup jobs. Below was the scenario prior to my change:
- Full backup of database scheduled to run at 12:30AM everyday.
- Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour.
- Log backup is scheduled to run every 15 minutes as Log-shipping is configured.
- Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday morning at 1:45AM.
We used to face space issue on the storage disk in the above scenario as Full backup was run before Maintenance job and hence subsequent differential backup was getting bigger and bigger in size until next full backup was run. This prompted me to run Full backup after maintenance job and I also checked the fragmentation level in mid of week and based on the values decided to run maintenance job twice a week, below is the modified plan:
- Full backup of database scheduled to run at 12:30AM on all days except on Sunday and Tuesday when maintenance job is scheduled. On Sunday and Tuesday, Full backup is taken at 2:30AM.
- Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour - No change.
- Log backup is scheduled to run every 15 minutes as Log-shipping is configured - No change
- Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday and Tuesday morning at 1:45AM.
The issue I am facing right now is the size of log backup immediately after maintenance job, Log backup is much bigger than Full backup of database itself. Needless to say, Log backups are transferred to Secondary site, which is then uploaded there for syncing purpose. This taken longer than expected and in between Log-shipping alert gets triggered since primary and secondary is not in sync. Earlier also, Log backup was bigger however used to be much lesser than that of Full backup and was taking considerably lesser time in transferring from primary to secondary server.
I am not really sure, if this is a valid scenario wherein the changes(insert/update/delete) was so voluminous in last 3 days that the maintenance job created bigger log file than full backup and would stabilize gradually or I should be scheduling two full backup on Sunday and Tuesday(when maintenance job is running) - One at 12:30AM and another one after maintenance job.
Appreciate your kind advise.
sql-server backup maintenance ola-hallengren
asked 3 mins ago
PranawPranaw
10610
add a comment |
I would request to have expert advise on scheduling maintenance and backup jobs. Below was the scenario prior to my change:
- Full backup of database scheduled to run at 12:30AM everyday.
- Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour.
- Log backup is scheduled to run every 15 minutes as Log-shipping is configured.
- Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday morning at 1:45AM.
We used to face space issue on the storage disk in the above scenario as Full backup was run before Maintenance job and hence subsequent differential backup was getting bigger and bigger in size until next full backup was run. This prompted me to run Full backup after maintenance job and I also checked the fragmentation level in mid of week and based on the values decided to run maintenance job twice a week, below is the modified plan:
- Full backup of database scheduled to run at 12:30AM on all days except on Sunday and Tuesday when maintenance job is scheduled. On Sunday and Tuesday, Full backup is taken at 2:30AM.
- Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour - No change.
- Log backup is scheduled to run every 15 minutes as Log-shipping is configured - No change
- Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday and Tuesday morning at 1:45AM.
The issue I am facing right now is the size of log backup immediately after maintenance job, Log backup is much bigger than Full backup of database itself. Needless to say, Log backups are transferred to Secondary site, which is then uploaded there for syncing purpose. This taken longer than expected and in between Log-shipping alert gets triggered since primary and secondary is not in sync. Earlier also, Log backup was bigger however used to be much lesser than that of Full backup and was taking considerably lesser time in transferring from primary to secondary server.
I am not really sure, if this is a valid scenario wherein the changes(insert/update/delete) was so voluminous in last 3 days that the maintenance job created bigger log file than full backup and would stabilize gradually or I should be scheduling two full backup on Sunday and Tuesday(when maintenance job is running) - One at 12:30AM and another one after maintenance job.
Appreciate your kind advise.
sql-server backup maintenance ola-hallengren
asked 3 mins ago
PranawPranaw
10610
add a comment |
I would request to have expert advise on scheduling maintenance and backup jobs. Below was the scenario prior to my change:
- Full backup of database scheduled to run at 12:30AM everyday.
- Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour.
- Log backup is scheduled to run every 15 minutes as Log-shipping is configured.
- Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday morning at 1:45AM.
We used to face space issue on the storage disk in the above scenario as Full backup was run before Maintenance job and hence subsequent differential backup was getting bigger and bigger in size until next full backup was run. This prompted me to run Full backup after maintenance job and I also checked the fragmentation level in mid of week and based on the values decided to run maintenance job twice a week, below is the modified plan:
- Full backup of database scheduled to run at 12:30AM on all days except on Sunday and Tuesday when maintenance job is scheduled. On Sunday and Tuesday, Full backup is taken at 2:30AM.
- Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour - No change.
- Log backup is scheduled to run every 15 minutes as Log-shipping is configured - No change
- Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday and Tuesday morning at 1:45AM.
The issue I am facing right now is the size of log backup immediately after maintenance job, Log backup is much bigger than Full backup of database itself. Needless to say, Log backups are transferred to Secondary site, which is then uploaded there for syncing purpose. This taken longer than expected and in between Log-shipping alert gets triggered since primary and secondary is not in sync. Earlier also, Log backup was bigger however used to be much lesser than that of Full backup and was taking considerably lesser time in transferring from primary to secondary server.
I am not really sure, if this is a valid scenario wherein the changes(insert/update/delete) was so voluminous in last 3 days that the maintenance job created bigger log file than full backup and would stabilize gradually or I should be scheduling two full backup on Sunday and Tuesday(when maintenance job is running) - One at 12:30AM and another one after maintenance job.
Appreciate your kind advise.
sql-server backup maintenance ola-hallengren
asked 3 mins ago
PranawPranaw
10610
I would request to have expert advise on scheduling maintenance and backup jobs. Below was the scenario prior to my change:
- Full backup of database scheduled to run at 12:30AM everyday.
- Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour.
- Log backup is scheduled to run every 15 minutes as Log-shipping is configured.
- Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday morning at 1:45AM.
We used to face space issue on the storage disk in the above scenario as Full backup was run before Maintenance job and hence subsequent differential backup was getting bigger and bigger in size until next full backup was run. This prompted me to run Full backup after maintenance job and I also checked the fragmentation level in mid of week and based on the values decided to run maintenance job twice a week, below is the modified plan:
- Full backup of database scheduled to run at 12:30AM on all days except on Sunday and Tuesday when maintenance job is scheduled. On Sunday and Tuesday, Full backup is taken at 2:30AM.
- Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour - No change.
- Log backup is scheduled to run every 15 minutes as Log-shipping is configured - No change
- Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday and Tuesday morning at 1:45AM.
The issue I am facing right now is the size of log backup immediately after maintenance job, Log backup is much bigger than Full backup of database itself. Needless to say, Log backups are transferred to Secondary site, which is then uploaded there for syncing purpose. This taken longer than expected and in between Log-shipping alert gets triggered since primary and secondary is not in sync. Earlier also, Log backup was bigger however used to be much lesser than that of Full backup and was taking considerably lesser time in transferring from primary to secondary server.
I am not really sure, if this is a valid scenario wherein the changes(insert/update/delete) was so voluminous in last 3 days that the maintenance job created bigger log file than full backup and would stabilize gradually or I should be scheduling two full backup on Sunday and Tuesday(when maintenance job is running) - One at 12:30AM and another one after maintenance job.
Appreciate your kind advise.
sql-server backup maintenance ola-hallengren
sql-server backup maintenance ola-hallengren
asked 3 mins ago
PranawPranaw
10610
asked 3 mins ago
PranawPranaw
10610
asked 3 mins ago
PranawPranaw
10610
asked 3 mins ago
PranawPranaw
10610
asked 3 mins ago
PranawPranaw
10610
10610
add a comment |
add a comment |
0
active
oldest
votes
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%2f230109%2fdatabase-maintenance-job-and-backup-scheduling%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f230109%2fdatabase-maintenance-job-and-backup-scheduling%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
