SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc
I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup stuff, but the table maintenance baffles me some. Does anyone have any input on how often I should (or how often you do would suffice too) the following tasks?
- Check Database Integrity
- Rebuild Indexes
- Reorganize Indexes
- Update Statistics
- Shrink Database?
Am I missing anything? Again if you can share how often you do these tasks that would be great...and/or share any general information about your approach to table maintenance that would be helpful. Lastly does it matter what order I run these tasks in (when setting up a job)?
Oh and I'm open to any links which might be of help!
sql-server-2008 sql-server-2005 maintenance
edited Feb 2 '12 at 2:30
Nick Chammas
10.7k1663108
asked Mar 31 '10 at 20:00
AlbertAlbert
16016
migrated from stackoverflow.com Feb 2 '12 at 0:48
This question came from our site for professional and enthusiast programmers.
add a comment |
I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup stuff, but the table maintenance baffles me some. Does anyone have any input on how often I should (or how often you do would suffice too) the following tasks?
- Check Database Integrity
- Rebuild Indexes
- Reorganize Indexes
- Update Statistics
- Shrink Database?
Am I missing anything? Again if you can share how often you do these tasks that would be great...and/or share any general information about your approach to table maintenance that would be helpful. Lastly does it matter what order I run these tasks in (when setting up a job)?
Oh and I'm open to any links which might be of help!
sql-server-2008 sql-server-2005 maintenance
edited Feb 2 '12 at 2:30
Nick Chammas
10.7k1663108
asked Mar 31 '10 at 20:00
AlbertAlbert
16016
migrated from stackoverflow.com Feb 2 '12 at 0:48
This question came from our site for professional and enthusiast programmers.
add a comment |
I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup stuff, but the table maintenance baffles me some. Does anyone have any input on how often I should (or how often you do would suffice too) the following tasks?
- Check Database Integrity
- Rebuild Indexes
- Reorganize Indexes
- Update Statistics
- Shrink Database?
Am I missing anything? Again if you can share how often you do these tasks that would be great...and/or share any general information about your approach to table maintenance that would be helpful. Lastly does it matter what order I run these tasks in (when setting up a job)?
Oh and I'm open to any links which might be of help!
sql-server-2008 sql-server-2005 maintenance
edited Feb 2 '12 at 2:30
Nick Chammas
10.7k1663108
asked Mar 31 '10 at 20:00
AlbertAlbert
16016
I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup stuff, but the table maintenance baffles me some. Does anyone have any input on how often I should (or how often you do would suffice too) the following tasks?
- Check Database Integrity
- Rebuild Indexes
- Reorganize Indexes
- Update Statistics
- Shrink Database?
Am I missing anything? Again if you can share how often you do these tasks that would be great...and/or share any general information about your approach to table maintenance that would be helpful. Lastly does it matter what order I run these tasks in (when setting up a job)?
Oh and I'm open to any links which might be of help!
sql-server-2008 sql-server-2005 maintenance
sql-server-2008 sql-server-2005 maintenance
edited Feb 2 '12 at 2:30
Nick Chammas
10.7k1663108
asked Mar 31 '10 at 20:00
AlbertAlbert
16016
edited Feb 2 '12 at 2:30
Nick Chammas
10.7k1663108
asked Mar 31 '10 at 20:00
AlbertAlbert
16016
edited Feb 2 '12 at 2:30
Nick Chammas
10.7k1663108
edited Feb 2 '12 at 2:30
Nick Chammas
10.7k1663108
edited Feb 2 '12 at 2:30
Nick Chammas
10.7k1663108
10.7k1663108
asked Mar 31 '10 at 20:00
AlbertAlbert
16016
asked Mar 31 '10 at 20:00
AlbertAlbert
16016
asked Mar 31 '10 at 20:00
AlbertAlbert
16016
16016
migrated from stackoverflow.com Feb 2 '12 at 0:48
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com Feb 2 '12 at 0:48
This question came from our site for professional and enthusiast programmers.
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Usually you'd do weekly or daily depending on your usage and maintenance windows.
You pretty much never shrink, especially not scheduled.
For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job.
Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option (based on fragmentation).
We use weekly index/DBCC and daily statistics. And never shrink.
From Simple-talk: Don't Forget to Maintain Your Indexes
edited 26 mins ago
Paul White♦
51.3k14278450
answered Mar 31 '10 at 20:09
gbngbn
63.7k7137212
add a comment |
Re: Shrinking. I see so many people getting their claws out at the very mention of 'shrink' and 'database' in the same sentence, so time to clear things up a little.
Shrinking data is baaaaad. It literally turns your indexes into quivering shells of their former glory. Don't do it.
Shrinking log should not be done routinely, but if you have a ridiculously outsized log (i.e. in one case I saw a 40GB log for a 100MB database, due to whoever set it up putting recovery model to full then never dumping the transaction log), you can shrink the log (after BACKUP LOG) to reclaim that space without any ill effects (although, obviously, the SHRINK will chew up I/O while it's running).
PS: Speaking of log files, check your log file size and autogrowth settings. Small autogrowth settings can lead to underperforming log I/O (due to a poorly-explained feature in SQL Server called Virtual Log Files), particularly on batch transactions.
answered Feb 2 '12 at 4:27
Simon RighartsSimon Righarts
4,2332028
add a comment |
For scripts, Ola Hallengren has wicked awesome things maintenance. Use maintenance plans for now, once you become a little comfortable with the process of doing maintenance, switch to using scripts, running scheduled SQL agent jobs.
At my place of work, I am doing DBCC, reorg'ing indexes every night. On the weekends, it's DBCC, rebuild indexes, update stats.
Don't shrink anything. If you do, Brent Ozar will yell at you, it is never fun to have a SQL MCM yell at you.
answered Feb 2 '12 at 1:55
RateControlRateControl
703718
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%2f12082%2fsql-server-2008-table-maintenance-rebuild-reorganize-update-stats-check-int%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Usually you'd do weekly or daily depending on your usage and maintenance windows.
You pretty much never shrink, especially not scheduled.
For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job.
Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option (based on fragmentation).
We use weekly index/DBCC and daily statistics. And never shrink.
From Simple-talk: Don't Forget to Maintain Your Indexes
edited 26 mins ago
Paul White♦
51.3k14278450
answered Mar 31 '10 at 20:09
gbngbn
63.7k7137212
add a comment |
Usually you'd do weekly or daily depending on your usage and maintenance windows.
You pretty much never shrink, especially not scheduled.
For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job.
Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option (based on fragmentation).
We use weekly index/DBCC and daily statistics. And never shrink.
From Simple-talk: Don't Forget to Maintain Your Indexes
edited 26 mins ago
Paul White♦
51.3k14278450
answered Mar 31 '10 at 20:09
gbngbn
63.7k7137212
add a comment |
Usually you'd do weekly or daily depending on your usage and maintenance windows.
You pretty much never shrink, especially not scheduled.
For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job.
Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option (based on fragmentation).
We use weekly index/DBCC and daily statistics. And never shrink.
From Simple-talk: Don't Forget to Maintain Your Indexes
edited 26 mins ago
Paul White♦
51.3k14278450
answered Mar 31 '10 at 20:09
gbngbn
63.7k7137212
Usually you'd do weekly or daily depending on your usage and maintenance windows.
You pretty much never shrink, especially not scheduled.
For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job.
Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option (based on fragmentation).
We use weekly index/DBCC and daily statistics. And never shrink.
From Simple-talk: Don't Forget to Maintain Your Indexes
edited 26 mins ago
Paul White♦
51.3k14278450
answered Mar 31 '10 at 20:09
gbngbn
63.7k7137212
edited 26 mins ago
Paul White♦
51.3k14278450
edited 26 mins ago
Paul White♦
51.3k14278450
edited 26 mins ago
Paul White♦
51.3k14278450
51.3k14278450
answered Mar 31 '10 at 20:09
gbngbn
63.7k7137212
answered Mar 31 '10 at 20:09
gbngbn
63.7k7137212
answered Mar 31 '10 at 20:09
gbngbn
63.7k7137212
63.7k7137212
add a comment |
add a comment |
Re: Shrinking. I see so many people getting their claws out at the very mention of 'shrink' and 'database' in the same sentence, so time to clear things up a little.
Shrinking data is baaaaad. It literally turns your indexes into quivering shells of their former glory. Don't do it.
Shrinking log should not be done routinely, but if you have a ridiculously outsized log (i.e. in one case I saw a 40GB log for a 100MB database, due to whoever set it up putting recovery model to full then never dumping the transaction log), you can shrink the log (after BACKUP LOG) to reclaim that space without any ill effects (although, obviously, the SHRINK will chew up I/O while it's running).
PS: Speaking of log files, check your log file size and autogrowth settings. Small autogrowth settings can lead to underperforming log I/O (due to a poorly-explained feature in SQL Server called Virtual Log Files), particularly on batch transactions.
answered Feb 2 '12 at 4:27
Simon RighartsSimon Righarts
4,2332028
add a comment |
Re: Shrinking. I see so many people getting their claws out at the very mention of 'shrink' and 'database' in the same sentence, so time to clear things up a little.
Shrinking data is baaaaad. It literally turns your indexes into quivering shells of their former glory. Don't do it.
Shrinking log should not be done routinely, but if you have a ridiculously outsized log (i.e. in one case I saw a 40GB log for a 100MB database, due to whoever set it up putting recovery model to full then never dumping the transaction log), you can shrink the log (after BACKUP LOG) to reclaim that space without any ill effects (although, obviously, the SHRINK will chew up I/O while it's running).
PS: Speaking of log files, check your log file size and autogrowth settings. Small autogrowth settings can lead to underperforming log I/O (due to a poorly-explained feature in SQL Server called Virtual Log Files), particularly on batch transactions.
answered Feb 2 '12 at 4:27
Simon RighartsSimon Righarts
4,2332028
add a comment |
Re: Shrinking. I see so many people getting their claws out at the very mention of 'shrink' and 'database' in the same sentence, so time to clear things up a little.
Shrinking data is baaaaad. It literally turns your indexes into quivering shells of their former glory. Don't do it.
Shrinking log should not be done routinely, but if you have a ridiculously outsized log (i.e. in one case I saw a 40GB log for a 100MB database, due to whoever set it up putting recovery model to full then never dumping the transaction log), you can shrink the log (after BACKUP LOG) to reclaim that space without any ill effects (although, obviously, the SHRINK will chew up I/O while it's running).
PS: Speaking of log files, check your log file size and autogrowth settings. Small autogrowth settings can lead to underperforming log I/O (due to a poorly-explained feature in SQL Server called Virtual Log Files), particularly on batch transactions.
answered Feb 2 '12 at 4:27
Simon RighartsSimon Righarts
4,2332028
Re: Shrinking. I see so many people getting their claws out at the very mention of 'shrink' and 'database' in the same sentence, so time to clear things up a little.
Shrinking data is baaaaad. It literally turns your indexes into quivering shells of their former glory. Don't do it.
Shrinking log should not be done routinely, but if you have a ridiculously outsized log (i.e. in one case I saw a 40GB log for a 100MB database, due to whoever set it up putting recovery model to full then never dumping the transaction log), you can shrink the log (after BACKUP LOG) to reclaim that space without any ill effects (although, obviously, the SHRINK will chew up I/O while it's running).
PS: Speaking of log files, check your log file size and autogrowth settings. Small autogrowth settings can lead to underperforming log I/O (due to a poorly-explained feature in SQL Server called Virtual Log Files), particularly on batch transactions.
answered Feb 2 '12 at 4:27
Simon RighartsSimon Righarts
4,2332028
answered Feb 2 '12 at 4:27
Simon RighartsSimon Righarts
4,2332028
answered Feb 2 '12 at 4:27
Simon RighartsSimon Righarts
4,2332028
answered Feb 2 '12 at 4:27
Simon RighartsSimon Righarts
4,2332028
4,2332028
add a comment |
add a comment |
For scripts, Ola Hallengren has wicked awesome things maintenance. Use maintenance plans for now, once you become a little comfortable with the process of doing maintenance, switch to using scripts, running scheduled SQL agent jobs.
At my place of work, I am doing DBCC, reorg'ing indexes every night. On the weekends, it's DBCC, rebuild indexes, update stats.
Don't shrink anything. If you do, Brent Ozar will yell at you, it is never fun to have a SQL MCM yell at you.
answered Feb 2 '12 at 1:55
RateControlRateControl
703718
add a comment |
For scripts, Ola Hallengren has wicked awesome things maintenance. Use maintenance plans for now, once you become a little comfortable with the process of doing maintenance, switch to using scripts, running scheduled SQL agent jobs.
At my place of work, I am doing DBCC, reorg'ing indexes every night. On the weekends, it's DBCC, rebuild indexes, update stats.
Don't shrink anything. If you do, Brent Ozar will yell at you, it is never fun to have a SQL MCM yell at you.
answered Feb 2 '12 at 1:55
RateControlRateControl
703718
add a comment |
For scripts, Ola Hallengren has wicked awesome things maintenance. Use maintenance plans for now, once you become a little comfortable with the process of doing maintenance, switch to using scripts, running scheduled SQL agent jobs.
At my place of work, I am doing DBCC, reorg'ing indexes every night. On the weekends, it's DBCC, rebuild indexes, update stats.
Don't shrink anything. If you do, Brent Ozar will yell at you, it is never fun to have a SQL MCM yell at you.
answered Feb 2 '12 at 1:55
RateControlRateControl
703718
For scripts, Ola Hallengren has wicked awesome things maintenance. Use maintenance plans for now, once you become a little comfortable with the process of doing maintenance, switch to using scripts, running scheduled SQL agent jobs.
At my place of work, I am doing DBCC, reorg'ing indexes every night. On the weekends, it's DBCC, rebuild indexes, update stats.
Don't shrink anything. If you do, Brent Ozar will yell at you, it is never fun to have a SQL MCM yell at you.
answered Feb 2 '12 at 1:55
RateControlRateControl
703718
answered Feb 2 '12 at 1:55
RateControlRateControl
703718
answered Feb 2 '12 at 1:55
RateControlRateControl
703718
answered Feb 2 '12 at 1:55
RateControlRateControl
703718
703718
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.
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%2f12082%2fsql-server-2008-table-maintenance-rebuild-reorganize-update-stats-check-int%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
