|
You last visited: Today at 13:13
Advertisement
delete large un-needed data from database
Discussion on delete large un-needed data from database within the SRO Private Server forum part of the Silkroad Online category.
10/21/2018, 08:34
|
#1
|
elite*gold: 0 
Join Date: Feb 2012
Posts: 551
Received Thanks: 46
|
delete large un-needed data from database
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 
|
|
|
|
10/21/2018, 15:54
|
#2
|
elite*gold: 100
Join Date: Sep 2017
Posts: 1,097
Received Thanks: 889
|
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
|
|
|
|
|
10/21/2018, 16:46
|
#3
|
elite*gold: 0
Join Date: Feb 2012
Posts: 551
Received Thanks: 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
|
|
|
|
|
10/22/2018, 17:56
|
#4
|
elite*gold: 100
Join Date: Sep 2017
Posts: 1,097
Received Thanks: 889
|
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.
|
|
|
|
|
10/22/2018, 19:22
|
#5
|
elite*gold: 350
Join Date: Aug 2015
Posts: 1,999
Received Thanks: 1,184
|
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
|
|
|
|
|
10/22/2018, 21:01
|
#6
|
elite*gold: 100
Join Date: Sep 2017
Posts: 1,097
Received Thanks: 889
|
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]
|
|
|
|
|
10/23/2018, 01:18
|
#7
|
elite*gold: 0
Join Date: Apr 2016
Posts: 286
Received Thanks: 67
|
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
|
|
|
|
|
|
10/23/2018, 02:05
|
#8
|
elite*gold: 0
Join Date: Feb 2012
Posts: 551
Received Thanks: 46
|
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
|
|
|
|
|
10/23/2018, 07:48
|
#9
|
elite*gold: 100
Join Date: Sep 2017
Posts: 1,097
Received Thanks: 889
|
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
|
|
|
|
|
10/23/2018, 08:37
|
#10
|
elite*gold: 100
Join Date: Apr 2008
Posts: 860
Received Thanks: 1,465
|
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.
|
|
|
|
|
|
10/23/2018, 10:11
|
#11
|
elite*gold: 100
Join Date: Sep 2017
Posts: 1,097
Received Thanks: 889
|
Quote:
Originally Posted by florian0
|
Can't get you... I mean, I already know this!
|
|
|
|
|
10/23/2018, 12:04
|
#12
|
elite*gold: 100
Join Date: Apr 2008
Posts: 860
Received Thanks: 1,465
|
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 |
|
|
|
|
|
10/23/2018, 13:20
|
#13
|
elite*gold: 100
Join Date: Sep 2017
Posts: 1,097
Received Thanks: 889
|
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)
|
|
|
|
|
10/23/2018, 18:44
|
#14
|
elite*gold: 0
Join Date: Feb 2012
Posts: 551
Received Thanks: 46
|
[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 ?
|
|
|
|
|
10/23/2018, 20:03
|
#15
|
elite*gold: 100
Join Date: Sep 2017
Posts: 1,097
Received Thanks: 889
|
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.
|
|
|
|
 |
|
Similar Threads
|
[C++] Sockets - large data handling
03/08/2017 - C/C++ - 11 Replies
Hi I would ask you for some help.
I already searched around on google but I always found bad examples or atleast I didn't understand them. (If I worked with sockets then mostly in Java and only chat stuff)
So I want to send large data over network, as I read send/recv has a max length and that I have to send data in chunks. Or don't I have to do that?
The large data is in my case a file, which I read in a char vector and want to send it. The files I want to send are between 100-500KB....
|
Questions] data Folder >> Create data.Pack
02/15/2012 - Dekaron Private Server - 8 Replies
Questions] data Folder >> Create data.Pack
To make the data folder data.pak
Use repacker We've been able to run the update, but the game does not
You, packer and should think about doing anything else specifically that anyway?
Sorry... Bad english..
|
G Data G Data InternetSecurity 2010 KEY
12/23/2010 - elite*gold Trading - 6 Replies
G Data IS 2011+ Backup KEY
Neben verbessertem Testsieger-Virenschutz und Optimierung der intuitiven Benutzerführung sichert G Data InternetSecurity zusätzlich alle Online-Aktivitäten durch eine „Silent“ Firewall. Die professionelle O&O-Vollbackupsoftware bietet zusätzlichen Schutz; selbst bei Totalausfall, Verlust oder Hardwarewechsel.
http://www.gdata.de/uploads/tx_commerce/GDISOB201 1_DE_Webshop_Detail.jpg
Einfach. Sicher. Ressourcenschonend.
|
My Cq Data at zfserver\MYSQL\data
01/14/2009 - CO2 Private Server - 3 Replies
sorry
|
All times are GMT +2. The time now is 13:13.
|
|
