delete large un-needed data from database

Page 1 of 2 1 2
10/21/2018 08:34 thebigbody#1
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 #HB#2
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 thebigbody#3
Quote:
Originally Posted by #HB View Post
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 #HB#4
Quote:
Originally Posted by thebigbody View Post
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 B1Q#5
Quote:
Originally Posted by #HB View Post
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 #HB#6
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 hoangphan7#7
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 thebigbody#8
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]
sorry but u didn't get it again :D
i want to delete only old chars not the whole db
read my main post again

Quote:
Originally Posted by hoangphan7 View Post
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 :eek:
will try it and tell you
10/23/2018 07:48 #HB#9
Quote:
Originally Posted by thebigbody View Post
sorry but u didn't get it again :D
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 florian0#10
Quote:
Originally Posted by #HB View Post
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.
[Only registered and activated users can see links. Click Here To Register...]
10/23/2018 10:11 #HB#11
Quote:
Originally Posted by florian0 View Post
[Only registered and activated users can see links. Click Here To Register...]
Can't get you... I mean, I already know this!
10/23/2018 12:04 florian0#12
Quote:
Originally Posted by #HB View Post
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 View Post
sorry but u didn't get it again :D
i want to delete only old chars not the whole db
read my main post again
10/23/2018 13:20 #HB#13
Quote:
Originally Posted by florian0 View Post
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 thebigbody#14
[QUOTE=#HB;37195694]Nah buddy, truncate doesn't delete the entire table, it just deletes the rows and

WTF ? :confused:
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 #HB#15
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.
Page 1 of 2 1 2