delete large un-needed data from database

thebigbody · 10/21/2018, 08:34

hello guys i have a question about what if large servers want to clean their databases so they reduce the maximum size of it
i have a very large database which have more than 44gb data files
and i want to reduce its size by deleting all inactive chars , items and to do that i used a condition that LastLogout must be < 2016 year
but the query is very slow and i found that it will need more than 14 days to finish ! "i calculated it" so anyone have a solution ?
PS : i made it on 2 steps
1

Quote:

USE SHARD
GO

DECLARE @CharID int
declare @CharName varchar(max) = (select CharName16 FROM _Char where CharID = @CharID)

DECLARE item_cur CURSOR FOR
SELECT DISTINCT CharID FROM _Char where LastLogout < '2016-01-01 00:00:00' ORDER BY CharID
OPEN item_cur
FETCH NEXT FROM item_cur INTO @CharID
WHILE @@FETCH_STATUS = 0
BEGIN
delete from _BindingOptionWithItem where nitemdbid in (select ItemID from _Inventory where charid = @charid )
delete From _ItemPool where itemid in (select ID64 FROM _Items where ID64 in ( select ItemID FROM _Inventory where CharID = @CharID))
delete From _ItemPool where itemid in (select ID64 FROM _Items where ID64 in ( select ItemID FROM _InventoryForAvatar where CharID = @CharID))
delete FROM _Items where ID64 in ( select ItemID FROM _Inventory where CharID = @CharID)
delete FROM _Items where ID64 in ( select ItemID FROM _InventoryForAvatar where CharID = @CharID)
delete from _TrainingCamp where ID IN (SELECT CampID FROM _TrainingCampMember where CharID = @CharID )
delete from _CharNameList where CharID = @CharID
delete from _CharNickNameList where CharID = @CharID
delete from _BlackNameList where BlacklistName = @CharName
delete from _CharCollectionBook where CharID = @CharID
FETCH NEXT FROM item_cur INTO @CharID
END
CLOSE item_cur

DEALLOCATE item_cur

2

Quote:

USE SHARD_VICTOR
GO

DECLARE item_cur CURSOR FOR
SELECT DISTINCT CharID FROM _Char where LastLogout < '2016-01-01 00:00:00' ORDER BY CharID
OPEN item_cur
FETCH NEXT FROM item_cur INTO @CharID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC _DeleteCharPermanently_NoTX @UserJID, @CharID
FETCH NEXT FROM item_cur INTO @CharID
END
CLOSE item_cur

DEALLOCATE item_cur

the reason why i used those 2 that the built in proc _DeleteCharPermanently_NoTX doesn't delete the items from _Items table and other staff that i had to make it manually
i hope i'm clear with that and i hope i find a good solution here

#HB · 10/21/2018, 15:54

Edit any "SRO_VT_SHARD" to your database name.

Code:

USE SRO_VT_SHARD; 
GO 
  
-- Truncate the log by changing the database recovery model to SIMPLE. 
ALTER DATABASE SRO_VT_SHARD 
SET RECOVERY SIMPLE; 
GO 
-- Shrink the truncated log file to 1 MB. 
DBCC SHRINKFILE (SRO_VT_SHARD_Log, 1); 
DBCC SHRINKDATABASE (SRO_VT_SHARD,0); 
GO

thebigbody · 10/21/2018, 16:46

Quote:

Originally Posted by #HB

Edit any "SRO_VT_SHARD" to your database name.

Code:

USE SRO_VT_SHARD; 
GO 
  
-- Truncate the log by changing the database recovery model to SIMPLE. 
ALTER DATABASE SRO_VT_SHARD 
SET RECOVERY SIMPLE; 
GO 
-- Shrink the truncated log file to 1 MB. 
DBCC SHRINKFILE (SRO_VT_SHARD_Log, 1); 
DBCC SHRINKDATABASE (SRO_VT_SHARD,0); 
GO

i don't only want to reduce my database size
i want to delete some parameters from the tables so that i can use any recall query fast from large tables such as _Items

#HB · 10/22/2018, 17:56

Quote:

Originally Posted by thebigbody

i don't only want to reduce my database size
i want to delete some parameters from the tables so that i can use any recall query fast from large tables such as _Items

Delete parameters! You OFC cannot do that or even change the parameter name since they're being used by the server executable files with their current name.

B1Q · 10/22/2018, 19:22

Quote:

Originally Posted by #HB

Delete parameters! You OFC cannot do that or even change the parameter name since they're being used by the server executable files with their current name.

I think you're mistaking "columns" for "parameters"
He meant Rows

#HB · 10/22/2018, 21:01

My bad... You ofc mean "cleaning database", if I got it.

Code:

USE [SRO_VT_ACCOUNT] 
GO 
TRUNCATE TABLE [dbo].[TB_User] 
TRUNCATE TABLE [dbo].[TB_User_Bak] 
TRUNCATE TABLE [dbo].[SK_CharRenameLog] 
TRUNCATE TABLE [dbo].[_Punishment] 
TRUNCATE TABLE [dbo].[_ServiceManagerLog] 
TRUNCATE TABLE [dbo].[Test_HN] 
TRUNCATE TABLE [dbo].[_BlockedUser] 
TRUNCATE TABLE [dbo].[_CasGMChatLog] 
TRUNCATE TABLE [dbo].[_Notice] 
TRUNCATE TABLE [dbo].[_SMCLog] 
TRUNCATE TABLE [dbo].[QuaySoEpoint] 
TRUNCATE TABLE [dbo].[SK_ITEM_GuardLog] 
TRUNCATE TABLE [dbo].[SK_ItemSaleLog] 
TRUNCATE TABLE [dbo].[SK_PackageItemSaleLog] 
TRUNCATE TABLE [dbo].[SK_PK_UpdateLog] 
TRUNCATE TABLE [dbo].[SK_ResetSkillLog] 
TRUNCATE TABLE [dbo].[SK_Silk] 
TRUNCATE TABLE [dbo].[SK_SilkBuyList] 
TRUNCATE TABLE [dbo].[SK_SilkGoods] 
TRUNCATE TABLE [dbo].[SK_SubtractSilk_VAS] 
TRUNCATE TABLE [dbo].[SR_ShardCharNames] 
TRUNCATE TABLE [dbo].[TB_Net2e] 
TRUNCATE TABLE [dbo].[TB_Net2e_Bak] 
TRUNCATE TABLE [dbo].[tb_paygate_trans]

USE [SRO_VT_SHARD] 
GO 
truncate table _OpenMarket 
print 'Cleaned _OpenMarket' 
truncate table _CharSkill 
print 'Cleaned _CharSkill' 
truncate table _CharSkillMastery 
print 'Cleaned _CharSkillMastery' 
truncate table _GuildMember 
print 'Cleaned _GuildMember' 
truncate table _BlockedWhisperers 
print 'Cleaned _BlockedWhisperers' 
truncate table _Inventory 
print 'Cleaned _Inventory' 
truncate table _InventoryForAvatar 
print 'Cleaned _InventoryForAvatar' 
truncate table _InventoryForLinkedStorage 
print 'Cleaned _InventoryForLinkedStorage' 
truncate table _TrainingCampMember 
print 'Cleaned _TrainingCampMember' 
truncate table _TrainingCampSubMentorHonorPoint 
print 'Cleaned _TrainingCampSubMentorHonorPoint' 
update _TrainingCampHonorRank set CampID = NULL, Rank = NULL 
print 'Cleaned _TrainingCampHonorRank' 
truncate table _TrainingCampBuffStatus 
print 'Cleaned _TrainingCampBuffStatus' 
delete from _TrainingCamp 
print 'Cleaned _TrainingCamp' 
truncate table _StaticAvatar 
print 'Cleaned _StaticAvatar' 
truncate table _User 
print 'Cleaned _User' 
truncate table _Friend 
print 'Cleaned _Friend' 
truncate table _Memo 
print 'Cleaned _Memo' 
truncate table _TimedJob 
print 'Cleaned _TimedJob' 
truncate table _CharTrijobSafeTrade 
print 'Cleaned _CharTrijobSafeTrade' 
delete from _CharTrijob 
print 'Cleaned _CharTrijob' 
truncate table _InvCOS 
print 'Cleaned _InvCOS' 
delete from _CharCOS where ID > 0 
print 'Cleaned _CharCOS' 
delete from _Char where charid > 0 
print 'Cleaned _Char' 
truncate table _Chest 
print 'Cleaned _Chest' 
truncate table _ChestInfo 
print 'Cleaned _ChestInfo' 
truncate table _ItemPool 
print 'Cleaned _ItemPool' 
delete from _Items where ID64 > 0 
print 'Cleaned _Items' 
delete from _AccountJID 
print 'Cleaned _AccountJID' 
update _AlliedClans set Ally1 = 0, Ally2 = 0, Ally3 = 0, Ally4 = 0, Ally5 = 0, Ally6 = 0, Ally7 = 0, Ally8 = 0 
print 'Cleaned _AlliedClans' 
truncate table _GuildWar 
print 'Cleaned _GuildWar' 
truncate table _CharNameList 
print 'Cleaned _CharNameList' 
truncate table _CharQuest 
print 'Cleaned _CharQuest' 
truncate table _ClientConfig 
print 'Cleaned _ClientConfig' 
truncate table _DeletedChar 
print 'Cleaned _DeletedChar' 
truncate table _SiegeFortressStoneState 
print 'Cleaned _SiegeFortressStoneState' 
truncate table _SiegeFortressRequest 
print 'Cleaned _SiegeFortressRequest' 
truncate table _SiegeFortressObject 
print 'Cleaned _SiegeFortressObject' 
truncate table _SiegeFortressItemForge 
print 'Cleaned _SiegeFortressItemForge' 
truncate table _SiegeFortressBattleRecord 
print 'Cleaned _SiegeFortressBattleRecord' 
update _SiegeFortressStruct SET OwnerGuildID = '0' 
print 'Cleaned _SiegeFortressStruct' 
UPDATE _SiegeFortress SET GuildID = '0', TaxRatio = '0', Tax = '0', NPCHired = '0', Introduction = NULL, CreatedDungeonTime = NULL, CreatedDungeonCount = NULL 
print 'Cleaned _SiegeFortress' 
truncate table _CharNickNameList 
print 'Cleaned _CharNickNameList' 
truncate table _GPHistory 
print 'Cleaned _GPHistory' 
truncate table _GuildChest 
print 'Cleaned _GuildChest' 
delete from _Guild where ID > 0 
print 'Cleaned _Guild' 
delete from _AlliedClans where ID > 0 
print 'Cleaned _AlliedClans'

USE [SRO_VT_SHARDLOG] 
GO 
TRUNCATE TABLE [dbo].[_LogCashItem] 
TRUNCATE TABLE [dbo].[_LogEventChar] 
TRUNCATE TABLE [dbo].[_LogEventItem] 
TRUNCATE TABLE [dbo].[_LogEventSiegeFortress] 
TRUNCATE TABLE [dbo].[_LogSchedule] 
TRUNCATE TABLE [dbo].[_LogServerEvent]

hoangphan7 · 10/23/2018, 01:18

Try this! Goodluck!

Quote:

USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_STRG_FREE_ITEM_NoTX] Script Date: 12/03/2011 04:26:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[_STRG_FREE_ITEM_NoTX]
@ItemToFree bigint
as

if (not exists(select ItemID from _ItemPool with (nolock) where ItemID = @ItemToFree))
return -1
DELETE FROM _ItemPool where ItemID = @ItemToFree
DELETE FROM _Items WHERE ID64 = @ItemToFree
return 1

thebigbody · 10/23/2018, 02:05

Quote:

Originally Posted by #HB

My bad... You ofc mean "cleaning database", if I got it.

Code:

USE [SRO_VT_ACCOUNT] 
GO 
TRUNCATE TABLE [dbo].[TB_User] 
TRUNCATE TABLE [dbo].[TB_User_Bak] 
TRUNCATE TABLE [dbo].[SK_CharRenameLog] 
TRUNCATE TABLE [dbo].[_Punishment] 
TRUNCATE TABLE [dbo].[_ServiceManagerLog] 
TRUNCATE TABLE [dbo].[Test_HN] 
TRUNCATE TABLE [dbo].[_BlockedUser] 
TRUNCATE TABLE [dbo].[_CasGMChatLog] 
TRUNCATE TABLE [dbo].[_Notice] 
TRUNCATE TABLE [dbo].[_SMCLog] 
TRUNCATE TABLE [dbo].[QuaySoEpoint] 
TRUNCATE TABLE [dbo].[SK_ITEM_GuardLog] 
TRUNCATE TABLE [dbo].[SK_ItemSaleLog] 
TRUNCATE TABLE [dbo].[SK_PackageItemSaleLog] 
TRUNCATE TABLE [dbo].[SK_PK_UpdateLog] 
TRUNCATE TABLE [dbo].[SK_ResetSkillLog] 
TRUNCATE TABLE [dbo].[SK_Silk] 
TRUNCATE TABLE [dbo].[SK_SilkBuyList] 
TRUNCATE TABLE [dbo].[SK_SilkGoods] 
TRUNCATE TABLE [dbo].[SK_SubtractSilk_VAS] 
TRUNCATE TABLE [dbo].[SR_ShardCharNames] 
TRUNCATE TABLE [dbo].[TB_Net2e] 
TRUNCATE TABLE [dbo].[TB_Net2e_Bak] 
TRUNCATE TABLE [dbo].[tb_paygate_trans]

USE [SRO_VT_SHARD] 
GO 
truncate table _OpenMarket 
print 'Cleaned _OpenMarket' 
truncate table _CharSkill 
print 'Cleaned _CharSkill' 
truncate table _CharSkillMastery 
print 'Cleaned _CharSkillMastery' 
truncate table _GuildMember 
print 'Cleaned _GuildMember' 
truncate table _BlockedWhisperers 
print 'Cleaned _BlockedWhisperers' 
truncate table _Inventory 
print 'Cleaned _Inventory' 
truncate table _InventoryForAvatar 
print 'Cleaned _InventoryForAvatar' 
truncate table _InventoryForLinkedStorage 
print 'Cleaned _InventoryForLinkedStorage' 
truncate table _TrainingCampMember 
print 'Cleaned _TrainingCampMember' 
truncate table _TrainingCampSubMentorHonorPoint 
print 'Cleaned _TrainingCampSubMentorHonorPoint' 
update _TrainingCampHonorRank set CampID = NULL, Rank = NULL 
print 'Cleaned _TrainingCampHonorRank' 
truncate table _TrainingCampBuffStatus 
print 'Cleaned _TrainingCampBuffStatus' 
delete from _TrainingCamp 
print 'Cleaned _TrainingCamp' 
truncate table _StaticAvatar 
print 'Cleaned _StaticAvatar' 
truncate table _User 
print 'Cleaned _User' 
truncate table _Friend 
print 'Cleaned _Friend' 
truncate table _Memo 
print 'Cleaned _Memo' 
truncate table _TimedJob 
print 'Cleaned _TimedJob' 
truncate table _CharTrijobSafeTrade 
print 'Cleaned _CharTrijobSafeTrade' 
delete from _CharTrijob 
print 'Cleaned _CharTrijob' 
truncate table _InvCOS 
print 'Cleaned _InvCOS' 
delete from _CharCOS where ID > 0 
print 'Cleaned _CharCOS' 
delete from _Char where charid > 0 
print 'Cleaned _Char' 
truncate table _Chest 
print 'Cleaned _Chest' 
truncate table _ChestInfo 
print 'Cleaned _ChestInfo' 
truncate table _ItemPool 
print 'Cleaned _ItemPool' 
delete from _Items where ID64 > 0 
print 'Cleaned _Items' 
delete from _AccountJID 
print 'Cleaned _AccountJID' 
update _AlliedClans set Ally1 = 0, Ally2 = 0, Ally3 = 0, Ally4 = 0, Ally5 = 0, Ally6 = 0, Ally7 = 0, Ally8 = 0 
print 'Cleaned _AlliedClans' 
truncate table _GuildWar 
print 'Cleaned _GuildWar' 
truncate table _CharNameList 
print 'Cleaned _CharNameList' 
truncate table _CharQuest 
print 'Cleaned _CharQuest' 
truncate table _ClientConfig 
print 'Cleaned _ClientConfig' 
truncate table _DeletedChar 
print 'Cleaned _DeletedChar' 
truncate table _SiegeFortressStoneState 
print 'Cleaned _SiegeFortressStoneState' 
truncate table _SiegeFortressRequest 
print 'Cleaned _SiegeFortressRequest' 
truncate table _SiegeFortressObject 
print 'Cleaned _SiegeFortressObject' 
truncate table _SiegeFortressItemForge 
print 'Cleaned _SiegeFortressItemForge' 
truncate table _SiegeFortressBattleRecord 
print 'Cleaned _SiegeFortressBattleRecord' 
update _SiegeFortressStruct SET OwnerGuildID = '0' 
print 'Cleaned _SiegeFortressStruct' 
UPDATE _SiegeFortress SET GuildID = '0', TaxRatio = '0', Tax = '0', NPCHired = '0', Introduction = NULL, CreatedDungeonTime = NULL, CreatedDungeonCount = NULL 
print 'Cleaned _SiegeFortress' 
truncate table _CharNickNameList 
print 'Cleaned _CharNickNameList' 
truncate table _GPHistory 
print 'Cleaned _GPHistory' 
truncate table _GuildChest 
print 'Cleaned _GuildChest' 
delete from _Guild where ID > 0 
print 'Cleaned _Guild' 
delete from _AlliedClans where ID > 0 
print 'Cleaned _AlliedClans'

USE [SRO_VT_SHARDLOG] 
GO 
TRUNCATE TABLE [dbo].[_LogCashItem] 
TRUNCATE TABLE [dbo].[_LogEventChar] 
TRUNCATE TABLE [dbo].[_LogEventItem] 
TRUNCATE TABLE [dbo].[_LogEventSiegeFortress] 
TRUNCATE TABLE [dbo].[_LogSchedule] 
TRUNCATE TABLE [dbo].[_LogServerEvent]

sorry but u didn't get it again

i want to delete only old chars not the whole db
read my main post again

Quote:

Originally Posted by hoangphan7

Try this! Goodluck!

Quote:

USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_STRG_FREE_ITEM_NoTX] Script Date: 12/03/2011 04:26:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[_STRG_FREE_ITEM_NoTX]
@ItemToFree bigint
as

if (not exists(select ItemID from _ItemPool with (nolock) where ItemID = @ItemToFree))
return -1
DELETE FROM _ItemPool where ItemID = @ItemToFree
DELETE FROM _Items WHERE ID64 = @ItemToFree
return 1

interesting

will try it and tell you

#HB · 10/23/2018, 07:48

Quote:

Originally Posted by thebigbody

sorry but u didn't get it again
i want to delete only old chars not the whole db
read my main post again

This time you're the guy who misses-understand, the query I gave you cleans the database from any old characters/users/items/logs/guilds/unions..etc

florian0 · 10/23/2018, 08:37

Quote:

Originally Posted by #HB

This time you're the guy who misses-understand, the query I gave you cleans the database from any old characters/users/items/logs/guilds/unions..etc

Quote:

Originally Posted by Microsoft

TRUNCATE TABLE (Transact-SQL)

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

#HB · 10/23/2018, 10:11

Quote:

Originally Posted by florian0

Can't get you... I mean, I already know this!

florian0 · 10/23/2018, 12:04

Quote:

Originally Posted by #HB

Can't get you... I mean, I already know this!

If you know that truncates deletes all entries in a table, then you should realize that your queries will delete the whole database.

Not just:

Quote:

Originally Posted by thebigbody

sorry but u didn't get it again
i want to delete only old chars not the whole db
read my main post again

#HB · 10/23/2018, 13:20

Quote:

Originally Posted by florian0

If you know that truncates deletes all entries in a table, then you should realize that your queries will delete the whole database.

Not just:

Nah buddy, truncate doesn't delete the entire table, it just deletes the rows and

Quote:

Originally Posted by Microsoft

without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

(You can test TRUNCATE TABLE command yourself before you try my query above)

thebigbody · 10/23/2018, 18:44

[QUOTE=#HB;37195694]Nah buddy, truncate doesn't delete the entire table, it just deletes the rows and

WTF ?

it does delete the whole rows inside the table

guys again i don't want to delete all the rows from my tables i just want to delete some of them but this some are like 7 million rows and it took so long
my question was that i want to do that without taking this large time ?

#HB · 10/23/2018, 20:03

You can use this to delete top (number) of rows:

Code:

DELETE TOP (500) from [dbo].[TableName]

You can put a condition (where) at any situation:

Code:

DELETE TOP (500) from [dbo].[_Users] where UserID >= 5000

I don't know why you're caring some much about time, I don't think that time can be handled.