Hello, my name is I'm a senior principal engineer working with the GSE team.
This video is dedicated to discussing how to create a MS SQL server maintenance plan. We're gonna look at the possible task available when creating a maintenance plan along with how to configure the plan to run automatically on a plan schedule. So when considering SQL server maintenance plans, there are several tasks that we can set up through the maintenance plan. One is checking database consistency with a tool called D BC C. We can also do things like shrink database files. We can also do a reorganization or rebuild of our indexes, which is uh a best practice to keep those from being overly fragmented. We can also update database statistics. We can do a clean up of the history that's compiled within maintenance plans, begin to also do database backups and log backups. And the last task that's available is to do a clean up of maintenance, uh maintenance task activities.
So to go ahead and jump into this, looking at maintenance plans, we actually have a container within SQL server management studio called maintenance plants. All we have to do is right. Click on that and click maintenance, uh pick maintenance plan wizard. Now you may see this, if you're attempting to create your maintenance plan, it will basically point out that agent XPS A component is turned off. So whenever you see this particular message that tells you one thing and that is that the SQL server agent service is not running for my installation of sequel. So let's go ahead and start that service and that is a requirement for any maintenance plan creation or to run a maintenance plan. The SQL server agent service has to be engaged has to be running. So I just wanted to make a point of that before we got started at this point. Let's go ahead and go into the maintenance plan wizard.
We have the introductory screen, we'll click next, we'll go ahead and give our maintenance plan a name. So demo underscore MP, one of the first options that we have here is to actually schedule this thing by default. It is not scheduled. It has to be run by the user or by, by a user. So if I do a change on this, it takes me to the scheduling window by default, it's gonna try to run this weekly. If I don't um define a schedule for this. In this particular case, I'm going to change this to daily. It starts at midnight. I'm ok with that. So I'm gonna go ahead and click ok for that option. And if I do next, it's gonna give me a list of the task that I can choose to perform in this maintenance plan. So, one of the things I want to do is to do a check of my database to confirm that uh it's consistent and it's free of corruption. Moving down the list of task, I'm gonna go ahead and do a reorganization of the indexes.
So if my uh index fragmentation exceeds 15% it's gonna reorganize those indexes. I'm also gonna skip back by a few of these things. Uh I'm gonna go ahead and select cleanup history. This particular setting is dedicated to get, getting rid of uh some of the older information in my maintenance plan and you'll see what I'm talking about in a few minutes here on that particular choice. Also, I'm going to select, backing up my database. So there are a few other options I could have gone with there, but I don't want to run a shrink of my database or a rebuild of my indexes or an update of my statistics on a daily basis unless I absolutely have to. Those are a, those are actually tasks that would fit into a weekly maintenance plan much better than a daily one. So if I do it next on this, I see the selections that I've made. And at this point, it's going to ask me to define the objects that are going to be the subjects of those task. So in this particular case, I'm gonna go ahead and select my sample databases here. Adventure works. 2017, 2019, click. OK.
On that, I'm gonna go with the defaults for the integrity check. And the next task that I selected was to reorganize my indexes. I'm gonna select those databases once again, I'm gonna go with the defaults on this. I don't see any need to make changes there. So as far as the cleanup history, there is a default of four weeks. So any of these particular files that are listed, for instance, backup and restore history, SQL server, agent, job history, or maintenance plan history. After four weeks, any of those older logs will be uh pushed out of the uh of the uh container that they're being held in. So I'm gonna go ahead to the next one. This is the backup section. One of the things that you really want to be tuned into is where's my backup going by default. So windows, whenever you install a version of SQL server creates a default backup folder. So it will push the backup that you specify in your maintenance plan to that. If you want to change that location, you're more than able to at this point with this particular selection.
I'm not gonna go into a lot more detail on that, but we do have to define our databases, we want to back up and this maintenance plan once again is dedicated to these uh these adventure work databases. So if I click on next, I see that it's gonna create a report for me. Uh If I wanted this email, uh if I wanted this report, I should say email to me, I would specify uh an email report and I would have to define other things in order to do that. And I haven't done that. So we're just going to create the uh history report on its own. So on the complete the wizard window, you see a listing of all the selections that you made and obviously you can go back if there's anything that you didn't like in that selection list. So let's go ahead and click finish. So the maintenance plan has been created. If we look under maintenance plans, we now see demo MP. Now you may wonder if the maintenance plan is actually up to doing its job. So all you have to do is right, click on this and select execute and it is gonna take a little bit of time.
We ask it to do a check of the databases to reorganize the indexes and to do a backup of a couple of databases, not a whole lot of detailed information. If you're following this manually in studio management, of course, ideally, this will run in the middle of the night. So I have a return of success. So it completed all the tasks that I defined in the maintenance plan. So if for whatever reason, you are having issues with maintenance plans and you want to get a better idea of why they may or may not be working. You can go to the history section under the maintenance plan, get more information on it there. Uh If you break down into subcategories, it will show you a little bit more detail, not gonna go into a whole lot of specifics on this, but this gives you feedback on what's going on with your maintenance plan.
Now, if you ever want to make a change to the maintenance plan, you have the option to modify and it will in separate sections, lay out the task that you define for that maintenance plan. And if you want to make some changes, you're more than free to do that, an obvious change here under this particular uh section cleanup history, let's say we want to make it two weeks instead of four, we're more than able to do that by doing that right there. Ok. So that concludes our demo on how to set up and run an MS SQL server maintenance plan. I hope this video has helped you understand how to create the maintenance plan and schedule it to run on a planned schedule.
Thank you for watching.
