Default Maintenance Plan Shrink Database – Video Tip

The problem with using the default maintenance plans is that SQL Server Management Studio makes it easy to turn on some features that are really bad practice.

Transcription:

Steve Stedman 0:09
Welcome to today’s training video. I’m Steve Stedman. I’m the founder of Stedman Solutions. Today we’re going to talk about one of the common items reported by the sequel daily checkup from Stedman Solutions. This is a product that we make that we install on your server that monitors and lets us know when there’s issues. And it lets you know with alerting and notification what some of those issues are, let me show you an example. Here’s an example of the output of one of those reports. And this is just on one of my test servers. But you will get an email with different notice of where the problem areas are. Some of the reports have specific issues, like you’re showing check DB not run recently. But today, we’re just going to take a look at one of those issues and what we can do to resolve it. The item I wanted to talk about today is the default maintenance plan shrink database task. What this is showing is that the sequel, daily checkup has detected that someone has turned on the default maintenance plans rank database task. And this can be really, really dangerous. From a performance perspective, breaking databases generally is a bad thing to do. But there is a rare occasion that you do need to shrink a database. And that’s in the event that your database is getting smaller over time, and you need to clean up some disk space, and you’re low on disk space. Normally, under a database that continues to grow, or that’s a it’s a fixed size, we don’t ever recommend shrinking the database. However, there are cases like shrinking a log file where it gets too big on it, you need to shrink it down and expand it out again, like we talked about in another day related to virtual log files. But you don’t ever, ever, ever, ever want to use the default maintenance plans shrink database, because what this will do is when that maintenance job kicks off in this might be different when you’ve got something under load or your database is being used. It’ll just go and look and decide, okay, let’s shrink the database and see how small we can make it. And that’s not a good thing. Because when you shrink databases, it messes with the layout of the database fragmentation. And it has a really, really big performance impact on the system. So how does this happen in the first place? Well, typically somebody who is new to SQL Server, they go in and they see maintenance plans. And that’s something that you can add to do maintenance on the server. I really like sequel agent jobs better than maintenance plans. And I really try and avoid maintenance plans at all costs. But they may see that oh sequel maintenance plan, I can do these things to check up on my database. And I create a new maintenance plan. And I see this list of things that I can click all the checkboxes that sound good, check database integrity, yes, I want to do that. And that is absolutely something that you will want to do. But you just won’t want to do it through a default maintenance plan, you’ll want to use other scripts to do that. But then they Microsoft kind of pushes out shrink database, reorganize index, rebuild index, update, statistics. And all these things that go in here that are generally most of them are fairly good things to do. However, the shrink database, whoever created this, and added this into the default maintenance plan should be slapped. They really should. And but if you check shrink database, and you’re new to this, you think, Oh, that’s good, it’ll clean up space over time. And then you go in. And when you get to the shrink task, you say, let’s shrink all the databases. What this is going to do if you schedule this once a day is it’s going to go through. And for each and every database on your system, it’s going to try and shrink the data file, causing all kinds of things to be moved around causing excess IO causing fragmentation causing blocking as things are being moved. And it’s really just a complete waste, like 99.9% of the time on your SQL Server. And I say that the time you would want to shrink the database and you wouldn’t do it with a maintenance plan, you want to do it manually it would be if your database has grown too big and you’ve cleared out data for some reason you want to gain that space back on a random, occasional basis. Or if your log files have grown too big and you want to gain back some of the log space or you’re shrinking the logs to reduce VLF count and you’re going to expand it out again. But never and I say never and I’ve worked on lots and lots of SQL Servers over over the last 30 years. Never is shrink database a good idea to be run on your SQL Server. Here’s some more information that may help you out my blog. We can get more information Steve Stedman calm or Stedman Solutions COMM The last link will be the Stedman solutions, a sequel, daily checkup, monthly subscription. We have lots of people using the sequel daily checkup today. If it’s something that you’d like to use, let me know and we’ll get it set up for you. Alright, have a great day.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!