How do I clean up orphaned documents?

Instances running MongoDB 5.0 or earlier

Connect to the mongos node using the root role or a privileged account, then run the following statement on each collection you want to check:

db.getSiblingDB("<db>").<collection>.find().readPref("secondary").readConcern("local").explain("executionStats")

Replace the placeholders with your values:

In the results, focus on the SHARDING_FILTER stage. The chunkSkips field shows the number of orphaned documents in that collection on the current shard node.

n},
{
        "shardName" : "xxx",
        "executionSuccess" : true,
        "executionStages" : {
                "stage" : "SHARDING_FILTER",
                "nReturned" : 31186672,
                "executionTimeMillisEstimate" : 1761,
                "works" : 34711383,
                "advanced" : 31186672,
                "needTime" : 3524710,
                "needYield" : 0,
                "saveState" : 271182,
                "restoreState" : 271182,
                "isEOF" : 1,
                "chunkSkips" : 3524709,
                "inputStage" : {
                        "stage" : "COLLSCAN",
                        "nReturned" : 34711381,
                        "executionTimeMillisEstimate" : 658,
                        "works" : 34711383,
                        "advanced" : 34711381,
                        "needTime" : 1,
                        "needYield" : 0,
                        "saveState" : 271182,
                        "restoreState" : 271182,
                        "isEOF" : 1,
                        "direction" : "forward",
                        "docsExamined" : 34711381,
                        "docsExaminedBySizeInBytes" : NumberLong("14316055356")
                }
        }
}
]
},
"allPlansExecution" : [
        {
                "shardName" : "xxx",
                "allPlans" : [ ]

Instances running MongoDB 6.0 or later

Connect to the mongos node using the root role or a privileged account, then run one of the following statements:

// Check all namespaces in the sharded cluster
db.getSiblingDB("admin").aggregate( [{ $shardedDataDistribution: { } }] ).pretty()

// Check a single namespace
db.getSiblingDB("admin").aggregate( [{ $shardedDataDistribution: { } },{ $match: { "ns": "<db>.<collection>" } }] ).pretty()

The output shows the data distribution across all shards, including orphaned document counts. Example output:

[
  {
    "ns": "test.names",
    "shards": [
      {
        "shardName": "shard-1",
        "numOrphanedDocs": 0,
        "numOwnedDocuments": 6,
        "ownedSizeBytes": 366,
        "orphanedSizeBytes": 0
      },
      {
        "shardName": "shard-2",
        "numOrphanedDocs": 0,
        "numOwnedDocuments": 6,
        "ownedSizeBytes": 366,
        "orphanedSizeBytes": 0
      }
    ]
  }
]

If numOrphanedDocs is not 0 for any shard, proceed to clean up orphaned documents. For field descriptions, see the MongoDB documentation.

ApsaraDB for MongoDB instances

Step 1: Create the cleanup script

Create a JavaScript script named cleanupOrphaned.js on a server that can connect to the sharded cluster instance. Use the script that matches your MongoDB version.

// The IDs of the shard nodes to clean
var shardNames = ["shardName1", "shardName2"];
// The databases to clean
var databasesToProcess = ["database1", "database2", "database3"];

shardNames.forEach(function(shardName) {
    // Traverse the specified databases
    databasesToProcess.forEach(function(dbName) {
        var dbInstance = db.getSiblingDB(dbName);
        // Get all collection names in the database
        var collectionNames = dbInstance.getCollectionNames();

        // Traverse all collections
        collectionNames.forEach(function(collectionName) {
            // Build the full collection name
            var fullCollectionName = dbName + "." + collectionName;
            // Build the cleanupOrphaned command
            var command = {
                runCommandOnShard: shardName,
                command: { cleanupOrphaned: fullCollectionName }
            };

            // Run the cleanupOrphaned command
            var result = db.adminCommand(command);
            printjson(result);
            if (result.ok) {
                print("Cleaned up orphaned documents for collection " + fullCollectionName + " on shard " + shardName);
            } else {
                print("Failed to clean up orphaned documents for collection " + fullCollectionName + " on shard " + shardName);
            }
        });
    });
});

function cleanupOrphanedOnShard(shardName, fullCollectionName) {
    var nextKey = { };
    var result;

    while ( nextKey != null ) {
        var command = {
            runCommandOnShard: shardName,
            command: { cleanupOrphaned: fullCollectionName, startingFromKey: nextKey }
        };

        result = db.adminCommand(command);
        printjson(result);

        if (result.ok != 1 || !(result.results.hasOwnProperty(shardName)) || result.results[shardName].ok != 1 ) {
            print("Unable to complete at this time: failure or timeout.")
            break
        }

        nextKey = result.results[shardName].stoppedAtKey;
    }

    print("cleanupOrphaned done for coll: " + fullCollectionName + " on shard: " + shardName)
}

var shardNames = ["shardName1", "shardName2", "shardName3"]
var fullCollectionName = "database.collection"

shardNames.forEach(function(shardName) {
    cleanupOrphanedOnShard(shardName, fullCollectionName);
});

mongo --host <Mongoshost> --port <Primaryport> --authenticationDatabase <database> -u <username> -p <password> cleanupOrphaned.js > output.txt

load("cleanupOrphaned.js")

mongosh "mongodb://root:xxx@xxx.mongodb.rds.aliyuncs.com:3717" --authenticationDatabase admin
Current Mongosh Log ID: 65b213b61c3b76ba0648a859
Connecting to:          mongodb://<credentials>@xxx.mongodb.rds.aliyuncs.com:3717/?directConnection=true&authSource=admin&appName=mongosh.1
Using MongoDB:          4.2.23
Using Mongosh:          1.6.1

For mongosh info see: https://docs.mongodb.com/mongodb-shell/

[direct: mongos] test> load("cleanupOrphaned.js")
{
  results: {
    'xxx.xxx': {
      ok: 1,
      '$gleStats': {
        lastOpTime: {
          ts: Timestamp({ t: 1706169270, i: 1 }),
          t: Long("1")
        },
        electionId: ObjectId("7fffffff0000000000000001")
      },
      lastCommittedOpTime: Timestamp({ t: 1706169270, i: 1 }),
      '$configServerState': {
        opTime: {
          ts: Timestamp({ t: 1706169272, i: 1 }),

Self-managed MongoDB databases

1. Download the cleanupOrphaned.js script:

   wget "https://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/120562/cn_zh/1564451237979/cleanupOrphaned.js"

2. Open cleanupOrphaned.js and replace test with the name of the database to clean.

   Important

   To clean multiple databases, repeat steps 2 and 3 for each database.

   function cleanupOrphaned(coll) {
     var nextKey = { };
     var result;
   
     while ( nextKey != null ) {
       result = db.adminCommand( { cleanupOrphaned: coll, startingFromKey: nextKey } );
   
       if (result.ok != 1)
         print("Unable to complete at this time: failure or timeout.")
   
       printjson(result);
   
       nextKey = result.stoppedAtKey;
     }
   }
   var dbName = 'test'
   db = db.getSiblingDB(dbName)
   db.getCollectionNames().forEach(function(collName) {
       cleanupOrphaned(dbName + "." + collName);
   });

3. Run the following command on each shard node:

   Note

   Repeat this step on every shard node.

   Parameter	Description
   <Shardhost>	The IP address of the shard node
   <Primaryport>	The service port of the primary node in the shard
   <database>	The authentication database for the account
   <username>	The database account
   <password>	The account password

   mongo --host <Shardhost> --port <Primaryport> --authenticationDatabase <database> -u <username> -p <password> cleanupOrphaned.js

   Example: A self-managed cluster with three shard nodes:

   mongo --host 172.16.1.10 --port 27018 --authenticationDatabase admin -u dtstest -p 'Test123456' cleanupOrphaned.js
   
   mongo --host 172.16.1.11 --port 27021 --authenticationDatabase admin -u dtstest -p 'Test123456' cleanupOrphaned.js
   
   mongo --host 172.16.1.12 --port 27024 --authenticationDatabase admin -u dtstest -p 'Test123456' cleanupOrphaned.js