SQL Server – Massive MSreplcommands Table Cleanup Job Not Deleting Commands

No.

At least in my testing both of them have identical execution plans and identical locking behaviour. Both place an IX lock on tableA and a schema stability lock on TableB straight away and then follow the same behaviour in doing the clustered index scan on TableA taking IU locks on the page, U locks on the key, then in the event of a match converting the page lock to IX then the key lock to X prior to deleting the row. This is exactly the same pattern as if the NOLOCK hint on tableA is removed entirely so it is pointless in this case.

My original results had some differences between the two queries in that the Join version has a series of apparently aquiring and immediately releasing schema stability locks on TableA that didn't show up in the EXISTS version. That seems to be related to whether the "Include Actual Execution Plan" option is enabled in SSMS. Today if I have this option enabled this series appears in the locking info for both of them so not sure why originally it only appeared in one. Maybe some timing issue.

You can see this locking information using TF1200 as below (with "Include Actual Execution Plan" option disabled).

Code

SET NOCOUNT ON;

CREATE TABLE tableA(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

CREATE TABLE tableB(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

SELECT OBJECT_ID('tableA') AS tableA,
       OBJECT_ID('tableB') AS tableB;

INSERT INTO tableA VALUES (0,0),(1,1),(2,2),(3,3);
INSERT INTO tableB VALUES (1,1),(2,2),(4,4),(5,5),(6,6),(7,7);

SELECT *, %%lockres%%  AS lockres
FROM tableA


DECLARE @JoinSQLNoLockTableA nvarchar(max) = N'
DELETE tableA
FROM tableA a WITH(NOLOCK)
JOIN tableB b WITH(NOLOCK)
  ON 
      b.colA = a.colA
  AND b.colB = a.colB;'

DECLARE @ExistsSQL nvarchar(max) = N'
DELETE FROM tableA 
WHERE EXISTS
(
  SELECT *
  FROM tableB b WITH(NOLOCK) 
  where
      b.colA = tableA.colA 
  AND b.colB = tableA.colB 
);'  

DECLARE @JoinSQLWithoutNoLockTableA nvarchar(max) = REPLACE(@JoinSQLNoLockTableA,'tableA a WITH(NOLOCK)', 'tableA a')

/*Run the commands first with the trace flag off so the locking
info is less full of irrelevant stuff about plan compilation */
EXEC (@JoinSQLNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@ExistsSQL);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@JoinSQLWithoutNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);


/*Run with TF1200 on*/

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLNoLockTableA - Start';
EXEC (@JoinSQLNoLockTableA);
PRINT '@JoinSQLNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@ExistsSQL - Start';
EXEC (@ExistsSQL);
PRINT '@ExistsSQL - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLWithoutNoLockTableA - Start';
EXEC (@JoinSQLWithoutNoLockTableA);
PRINT '@JoinSQLWithoutNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

DROP TABLE tableA,
           tableB;

ObjectIds

tableA      tableB
----------- -----------
308196148   372196376

LockRes

colA        colB        lockres
----------- ----------- ------------------------------
0           0           (00009620dd9a)
1           1           (02006d47cbee)
2           2           (040060eff172)
3           3           (06009b88e706)

Output (All three queries)

Process 52 acquiring Sch-S lock on OBJECT: 23:372196376:0  (class bit0 ref1) result: OK

Process 52 acquiring IX lock on OBJECT: 23:308196148:0  (class bit2000000 ref1) result: OK

Process 52 acquiring IU lock on PAGE: 23:1:14144 (class bit0 ref1) result: OK

Process 52 acquiring U lock on KEY: 23:72057594051231744 (00009620dd9a) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (00009620dd9a)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit0 ref1) result: OK

Process 52 acquiring IX lock on PAGE: 23:1:14144 (class bit2000000 ref0) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (02006d47cbee)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (040060eff172) (class bit0 ref1) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (040060eff172) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (040060eff172)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (06009b88e706) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (06009b88e706)

Process 52 releasing lock reference on PAGE: 23:1:14144

Process 52 releasing lock on OBJECT: 23:372196376:0 

If you are sure that everything is replicated to all subscribers and you really need to shrink it right now, you can try this.

1. Execute the following snippet inside your replication database. It will mark all replications as successful and your replication queue will be empty. Be sure that this is really the case! Otherwise you may loose some elements which need to be replicated.

Script:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, 
     @numtrans = 0, @time = 0, @reset = 1

2. Take a backup and tail the log. It should now be empty.