0

We came across an odd bug in Maintenance Plans on SSMS 2016 and 2017 for SQL Server versions 2012 and 2016 (at least). The setup is unusual, but I couldn't find anything about this or any similar problems.

Background

We are running a couple of hundred instances of SQL Server currently, mostly SQL 2012, some SQL 2016; mostly standard edition, a couple of EE installs. We also have only three SQL Server DBAs for these servers (I am one of those). As such, we depend on standard procedures across all servers, and a lot of automation to maintain and monitor these servers.

When setting up a new SQL Server for new DBs (not a migration of old DBs to a new server), one of the standard procedures is to schedule Maintenance Plans for User DBs for index maintenance before releasing the server to the devs for them to start developing their DBs and applications. Since we don't know anything about the details about what applications and databases will be using the server, we set up a maintenance plan with the "All User Databases" option configured.

Lead-Up

In the last few weeks, I happened across a couple of servers with performance issues that had very high fragmentation. When the jobs were examined, the index maintenance jobs were completing successfully, but I noticed that the completion time was less than three seconds, which was obviously incorrect.

When I checked the maintenance plans on these servers, they were set to process selected databases instead of all user DBs. They also had no databases selected, which was weird.

Problem

When we dug into this, we had a server that had just been configured, but no user DBs had been added yet. When we looked at its maintenance plan, it was also set to selected databases. We set it to all user DBs, saved it, and re-opened the maintenance plan, and it was reset to selected dbs. This was tested multiple times on two different servers in this state, and it always happened. (SQL 2016 servers in this test).

Question: Is this a known issue? I could find nothing about the issue.

If not a known issue, I'll report it to Microsoft. If it is, is it limited to certain versions of SQL Server or SSMS?

  • Are you opening the maintenance plan via the designer, clicking save, and then reopening in the designer and seeing this? Can't help but say Ola could solve your problem :) – scsimon Jan 8 at 19:21
  • 1
    Yes, that was exactly how we did it. I'm pushing for Ola's scripts to be implemented, but "Large Company, Inertia vs Change" is usually won by inertia unless a higher-up advocates for change. – Laughing Vergil Jan 9 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.