Read Paul's documentation : https://www.sqlskills.com/help/waits/vdi_client_other/

thank God they are using varbinary(max) for every item in the tables, or at least Nvarchar(max)

Are you using identities or sequences to auto-increment the IDs?

did someone RESEED your identity values ?

if @tocheck=1

DROP TABLE IF EXISTS #partA

begin

insert into #partA (ID)  <- this table no longer exists here if @tocheck=1 !!! ( you are not using select ... into #partA )

select...

Indexes art there for two reasons:

• to enforce a constraint , needed for the data model ( unique - primary key )
• to support queries.
• Depending on the consumption pattern,...

Can you elaborate about your instance ? OS / SQLversion & cu

Are you monitoring I/O performance ?

Can you post the full details of the errors you get ?

Did you check...

my  bad. Sorry

You cannot.

Wait until the query finishes ( or cancel the query )

check the Lock compatibility matrix

You need select on that table.

The SQLAgent roles are made to support SSMS SQLAgent usage.

the builtin Query Store will be of great help

A great starting ref can be found here: SQLSkills Blogs Erin

STEP1:

if sys.fn_hadr_is_primary_replica ( dbname ) = 1
begin
     print 'OK'
end
else
begin
RAISERROR('DB dbname is not primary', 16, -1);
end

Set step one to "quit job reporting success" as...

This is indeed a feature that's been requested for some time in SQL Server.

We have created a "jobadmin" SQL account on some servers.

That account owns all user jobs and has...

No need to launch "EXEC msdb.dbo.sp_stop_job @job_name = @name;"  if you just fail the step "fail with success"

Why don't you just perform this check in the first step of the job? ( and "fail reporting success" )

FWIW: I've read on multiple threads regarding "backup database", providing parameters , BUFFERCOUNT = 17 , MAXTRANSFERSIZE = 1048576  have better results regarding performance of the command in...