search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

Exclude option expanded feature #683

Open redtrain65 opened 2 years ago

redtrain65 commented 2 years ago

Currently we have databases in AGs. We have a very large database that is no longer used but is there for historical purposes. We run our Index Optimize to update stats at the AG level. We tend to run our maintenance jobs at the AG level so if a database gets added to an AG we do not have to update our jobs.

What I would like is that at the AG level to be able to exclude a database so Index Optimize runs on all of the databases except the ones specified. I see you can do this at the USER_DATABASE level but to at the AG level.

Something like this:

@AvailabilityGroups = 'TPA0AG_01,-CUSTOMERDB', to run Index optimize on all databases in the AG except CUSTOMERDB

bwiggins10 commented 2 years ago

Hey redtrain65 I believe this feature exists already!

Assuming you're using @AvailabilityGroups because you have multiple AGs on the same server, for TPA0AG_01, I'm pretty sure you can do the following:

@Databases = 'AVAILABILITY_GROUP_DATABASES, -CUSTOMERDB', @AvailabilityGroups = 'TPA0AG_01'

redtrain65 commented 2 years ago

That does not work. Tried it.

@AvailabilityGroups = N'AG01', @Databases = 'AVAILABILITY_GROUP_DATABASES, -ABC_DEMO_PROD',

Msg 50000, Level 16, State 3, Procedure dbo.DatabaseBackup, Line 2267 [Batch Start Line 0] You can only specify one of the parameters @Databases and @AvailabilityGroups.

bwiggins10 commented 2 years ago

Hm I never would've guessed that they were mutually exclusive. Definitely seems like an ola issue then.

One suggestion would be that if the database is truly only for historical purposes, why not remove it from the AG and set it to read-only and restore a copy to the other node(s)?

redtrain65 commented 2 years ago

In the coming months we plan on doing that. Many other projects going on and getting approval from all parties to do this is a very low priority. It is mostly for doing Index stats right now. I know I can supply a list of dbs instead of specifying the AG name. I just fear we will forget to add a db to it is all.