الموقع العربي الاول للعبة Silkroad Online

سلام عليكم

عارف اني منزلتش حاجة من زمان. اعذروني
ندخل في المهم علي طول

1- دا كيوري بيمسح الشخصيات اللي مش بتفتح زي جوي ماكس . هيا كل فترة بتسمح الشخصيات اللي مش اكتف علشان تخفف الضغط علي الداتا بتاعتها
ودا هيكون مهم للسيرفرات اللي بتقعد بالسنين , مش السيزون اللي شهرين واقلب

كود:

-- ==========================================

-- 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 '========================================';

كل اللي عليك هتغير التاريخ وبس
دا معناه انه قبل التاريخ دا هيمسح الشخصيات باسكيلاتها بسلطاتها

2- دا كيوري هيخفف حجم الداتا بعد الحذف. لما تيجي تعمل باك اب هتلاقي المساحة قلت كتير
و التصغير دا هيسرعلك الداتا والتعامل في ال SQL

كود:

USE SRO_VT_SHARD;

GO



-- الخطوة 1: تصغير قاعدة البيانات أولاً (لإرجاع المساحة)

DBCC SHRINKDATABASE (SRO_VT_SHARD, 10);

GO



-- الخطوة 2: إعادة بناء الفهارس (لتنظيم البيانات وتحسين الأداء)

EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD';

GO

قبل اي شئ احتياطي بنصح ناخد باك اب

تحياتي