delete large un-needed data from database

Page 2 of 2 1 2
10/23/2018 23:10 B1Q#16
Quote:
Originally Posted by #HB View Post
Nah buddy, truncate doesn't delete the entire table, it just deletes the rows and

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

Quote:
Originally Posted by #HB View Post
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.
just stop dude you don't get it.
he wants to delete a character's data including skills items and everything related to it.
10/24/2018 00:06 McDiesel#17
Quote:
Originally Posted by #HB View Post
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]
you need to read the request again :D Delete All Old Char's ( Keep new )
10/24/2018 13:51 #HB#18
Quote:
Originally Posted by B1Q View Post
this melted my brain
Because you got "entire table" as "entire rows". It's nice that you've got a brain tho.

Maybe I didn't read the main post carefully, I got you now.
10/24/2018 14:23 B1Q#19
Quote:
Originally Posted by #HB View Post
Because you got "entire table" as "entire rows". It's nice that you've got a brain tho.

Maybe I didn't read the main post carefully, I got you now.
yep i gotta admit, having a brain is really helpful it helps writing your own code
you couldn't read the main post*
10/24/2018 14:29 #HB#20
This one to delete a specific character: [Only registered and activated users can see links. Click Here To Register...]
10/24/2018 23:35 thebigbody#21
Quote:
Originally Posted by #HB View Post
I don't know why you're caring some much about time, I don't think that time can be handled.
because the "time" i'm talking about is 14 days and consumes all CPU in my server so i need to run the query while the server is offline
imagine this :rolleyes:

Quote:
Originally Posted by #HB View Post
This one to delete a specific character: [Only registered and activated users can see links. Click Here To Register...]
thanks for trying to help but unfortunately this deletes specific chars
and can't make a condition with LastLogout so it won't work with me
10/25/2018 11:39 #HB#22
Quote:
Originally Posted by thebigbody View Post
and can't make a condition with LastLogout so it won't work with me
Looks like you haven't got much experience at SQL, you can simply do a simple loop to work with all characters with any condition. There you [Only registered and activated users can see links. Click Here To Register...], that one deletes all characters who have LastLogout value less than 3 days of current time, edit -3 to -(amount of days).

Learn more about SQL to help yourself.
10/25/2018 19:18 thebigbody#23
Quote:
Originally Posted by #HB View Post
Looks like you haven't got much experience at SQL, you can simply do a simple loop to work with all characters with any condition. There you [Only registered and activated users can see links. Click Here To Register...], that one deletes all characters who have LastLogout value less than 3 days of current time, edit -3 to -(amount of days).

Learn more about SQL to help yourself.
omg :facepalm:
thanks for helping bro :)
Page 2 of 2 1 2