Skip to main content
3 of 4
added 527 characters in body
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

Create a stored procedure in master:

USE master;
GO
CREATE PROCEDURE dbo.FixMaxDop
AS
BEGIN
  SET NOCOUNT ON;
  EXEC sys.sp_configure N'show advanced options', 1;
  RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'max degree of parallelism', 8;
  RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'show advanced options', 0;
  RECONFIGURE WITH OVERRIDE;
  -- optionally, drop or alter the procedure so it doesn't run again
END
GO

Now, you can set the procedure to be a startup procedure (see here and here):

EXEC sys.sp_procoption N'dbo.FixMaxDop', 'startup', 'true';

If you only want this to run once, then you'll want to flip that option off after the configuration option has been set correctly:

EXEC sys.sp_procoption N'dbo.FixMaxDop', 'startup', 'false';

Alternatively, you can do as MichaelK suggested - create a job with a step that calls the above procedure, make sure it is owned by sa or someone in the sysadmin role, and then set it to start when SQL Server Agent starts. However, you're relying heavily on SQL Server Agent being set to start automatically (it might not be, and that might change before the next restart), and that Agent will start successfully after the next service restart (it might fail for a variety of reasons, including service account changes). You also can't create a schedule that runs when Agent starts and only runs once (it's one or the other), so you'd have to do something else like script an additional job step that disables the job or removes the schedule.

Not really a big deal for the procedure not to run again, I suppose, unless you make some other manual change later and forget about this - then you'll revert to this explicit setting the next time you restart after that change...

Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619