-- ========================================== -- SILKROAD - Full Cleanup Plan (Like Joymax) -- Production Ready - Run as single script -- Change date in Phase 1 before each use -- ========================================== PRINT '========================================'; PRINT 'SILKROAD - Full Cleanup Started'; PRINT '========================================'; PRINT ''; -- ========================================== -- Phase 1: Find Inactive Characters -- ========================================== IF OBJECT_ID('tempdb..#CharsToDelete') IS NOT NULL DROP TABLE #CharsToDelete; CREATE TABLE #CharsToDelete (CharID INT PRIMARY KEY); -- CHANGE DATE HERE: Characters not logged in since this date will be deleted INSERT INTO #CharsToDelete (CharID) SELECT CharID FROM _Char WHERE LastLogout < '2024-01-01 00:00:00' OR LastLogout IS NULL; DECLARE @TotalChars INT = (SELECT COUNT(*) FROM #CharsToDelete); PRINT '[PHASE 1] Inactive Characters Found: ' + CAST(@TotalChars AS varchar); -- ========================================== -- Phase 2: Delete Related Data -- ========================================== PRINT '[PHASE 2] Deleting related data...'; DELETE FROM _Inventory WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _InventoryForAvatar WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharSkill WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharSkillMastery WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _Skill_BaoHiem_TNET WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharQuest WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharCOS WHERE OwnerCharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharCOS WHERE RefCharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _Friend WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _Friend WHERE FriendCharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _GuildMember WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _Memo WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _BlockedWhisperers WHERE OwnerID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _StaticAvatar WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharCollectionBook WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharAlchemy_MK_Recipe WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharTrijob WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharTrijobSafeTrade WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _OldTrijob WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _TimedJob WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _TimedJobForPet WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharNameList WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharNickNameList WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _ClientConfig WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _ExploitLog WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _FleaMarketNetwork WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _SiegeFortressBattleRecord WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _ResultOfPackageItemToMappingWithServerSide WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _TrainingCampBuffStatus WHERE DonorCharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _TrainingCampBuffStatus WHERE RecipientCharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _TrainingCampMember WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _TrainingCampSubMentorHonorPoint WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _UniqueKillList WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _UniqueRanking WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _RefObjCharExtraSkill WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _CharInstanceWorldData WHERE CharID IN (SELECT CharID FROM #CharsToDelete); PRINT '[PHASE 2] Related data deleted successfully'; -- ========================================== -- Phase 3: Save Affected UserJIDs & Delete from _User -- ========================================== PRINT '[PHASE 3] Saving affected accounts...'; CREATE TABLE #UsersToCheck (UserJID INT PRIMARY KEY); INSERT INTO #UsersToCheck (UserJID) SELECT DISTINCT u.UserJID FROM _User u WHERE u.CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _User WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DECLARE @AffectedUsers INT = (SELECT COUNT(*) FROM #UsersToCheck); PRINT '[PHASE 3] Affected Accounts: ' + CAST(@AffectedUsers AS varchar); -- ========================================== -- Phase 4: Hard Delete Characters -- ========================================== PRINT '[PHASE 4] Deleting characters permanently...'; DELETE FROM _DeletedChar WHERE CharID IN (SELECT CharID FROM #CharsToDelete); DELETE FROM _Char WHERE CharID IN (SELECT CharID FROM #CharsToDelete); PRINT '[PHASE 4] Characters Deleted: ' + CAST(@TotalChars AS varchar); -- ========================================== -- Phase 5: Clean Personal Chest FIRST -- ========================================== PRINT '[PHASE 5] Cleaning ALL empty account chests...'; DELETE FROM _Chest WHERE UserJID NOT IN ( SELECT DISTINCT UserJID FROM _User ); DECLARE @DeletedChestItems INT = @@ROWCOUNT; DELETE FROM _ChestInfo WHERE JID NOT IN ( SELECT DISTINCT UserJID FROM _User ); DECLARE @DeletedChestInfo INT = @@ROWCOUNT; PRINT '[PHASE 5] Chest Items Deleted: ' + CAST(@DeletedChestItems AS varchar); PRINT '[PHASE 5] Chests Deleted: ' + CAST(@DeletedChestInfo AS varchar); -- ========================================== -- Phase 6: Delete Empty Accounts -- ========================================== PRINT '[PHASE 6] Deleting empty accounts...'; DELETE FROM _AccountJID WHERE JID NOT IN ( SELECT DISTINCT UserJID FROM _User ); DECLARE @DeletedAccounts INT = @@ROWCOUNT; PRINT '[PHASE 6] Empty Accounts Deleted: ' + CAST(@DeletedAccounts AS varchar); DROP TABLE #UsersToCheck; -- ========================================== -- Phase 7: Clean Orphan Items -- ========================================== PRINT '[PHASE 7] Cleaning orphan items...'; DECLARE @OrphanCount INT; SELECT @OrphanCount = COUNT(*) FROM _Items WHERE ID64 NOT IN ( SELECT DISTINCT ItemID FROM _Inventory UNION SELECT DISTINCT ItemID FROM _InventoryForAvatar UNION SELECT DISTINCT ItemID FROM _InventoryForLinkedStorage UNION SELECT DISTINCT ItemID FROM _Chest ); PRINT '[PHASE 7] Orphan Items Found: ' + CAST(@OrphanCount AS varchar); IF @OrphanCount > 0 AND @OrphanCount <= 50000 BEGIN DELETE FROM _Items WHERE ID64 NOT IN ( SELECT DISTINCT ItemID FROM _Inventory UNION SELECT DISTINCT ItemID FROM _InventoryForAvatar UNION SELECT DISTINCT ItemID FROM _InventoryForLinkedStorage UNION SELECT DISTINCT ItemID FROM _Chest ); PRINT '[PHASE 7] Deleted ' + CAST(@OrphanCount AS varchar) + ' orphan items (single batch)'; END ELSE IF @OrphanCount > 50000 BEGIN PRINT '[PHASE 7] Large number detected - deleting in batches of 5000...'; DECLARE @BatchCount INT = 0; WHILE EXISTS ( SELECT 1 FROM _Items WHERE ID64 NOT IN ( SELECT DISTINCT ItemID FROM _Inventory UNION SELECT DISTINCT ItemID FROM _InventoryForAvatar UNION SELECT DISTINCT ItemID FROM _InventoryForLinkedStorage UNION SELECT DISTINCT ItemID FROM _Chest ) ) BEGIN DELETE TOP (5000) FROM _Items WHERE ID64 NOT IN ( SELECT DISTINCT ItemID FROM _Inventory UNION SELECT DISTINCT ItemID FROM _InventoryForAvatar UNION SELECT DISTINCT ItemID FROM _InventoryForLinkedStorage UNION SELECT DISTINCT ItemID FROM _Chest ); SET @BatchCount = @BatchCount + 1; PRINT ' Batch #' + CAST(@BatchCount AS varchar) + ' - 5000 items deleted'; WAITFOR DELAY '00:00:01'; END PRINT '[PHASE 7] All orphan items deleted in ' + CAST(@BatchCount AS varchar) + ' batches'; END ELSE BEGIN PRINT '[PHASE 7] No orphan items found - clean!'; END -- ========================================== -- Phase 8: Final Cleanup & Summary -- ========================================== DROP TABLE #CharsToDelete; PRINT ''; PRINT '========================================'; PRINT 'CLEANUP COMPLETED SUCCESSFULLY!'; PRINT '========================================'; PRINT ''; PRINT 'SUMMARY:'; PRINT ' Characters Deleted : ' + CAST(@TotalChars AS varchar); PRINT ' Affected Accounts : ' + CAST(@AffectedUsers AS varchar); PRINT ' Chest Items Deleted : ' + CAST(@DeletedChestItems AS varchar); PRINT ' Chests Deleted : ' + CAST(@DeletedChestInfo AS varchar); PRINT ' Empty Accounts Deleted : ' + CAST(@DeletedAccounts AS varchar); PRINT ' Orphan Items Deleted : ' + CAST(@OrphanCount AS varchar); PRINT ''; PRINT '========================================';
USE SRO_VT_SHARD; GO -- الخطوة 1: تصغير قاعدة البيانات أولاً (لإرجاع المساحة) DBCC SHRINKDATABASE (SRO_VT_SHARD, 10); GO -- الخطوة 2: إعادة بناء الفهارس (لتنظيم البيانات وتحسين الأداء) EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD'; GO