tigertoolbox icon indicating copy to clipboard operation
tigertoolbox copied to clipboard

Installing AdaptiveIndexDefrag in MSDB ONLY to Defrag Multiple Databases

Open MTL777 opened this issue 4 years ago • 1 comments

This is not really an issue I am posting. I just have questions about AdaptiveIndexDefrag:

  1. By default the script creates the objects that AdaptiveIndexDefrag needs in the system database MSDB. In this setup with its objects in MSDB, can I use it to defrag the indexes in multiple databases all at the same time using several SQL Server jobs, one for each database? Will the SQL Server jobs not be colliding with each other, causing resource sharing or locking conflicts? I hope it will work fine and be able to defrag indexes in multiple databases when installed in MSDB alone because we have many databases on our server and it would be a pain to install it on each one. So my plan is for each SQL Server job to call EXEC MSDB.dbo.usp_AdaptiveIndexDefrag in the context of the particular database that each SQL Server job is assigned to operate upon.

  2. If I setup the SQL Server jobs to start defragging our databases all together at 1:00 AM, would that be a good plan or should I setup the SQL Server jobs to start running at different times?

Thanks you so much in advance!

MTL777 avatar Oct 21 '21 17:10 MTL777

just put 2 footballs in orange juice thats how you fix it

jinja365 avatar Mar 02 '22 09:03 jinja365