SQL Code to DELETE inactive accounts

[nkerz]

Ok, this SQL code will remove all accounts that there is NO characters inside of them.

DECLARE @Acc varchar(10), @Chars varchar(10)

DECLARE LISTA CURSOR LOCAL FOR
SELECT memb___id FROM MEMB_INFO
OPEN LISTA
FETCH NEXT FROM LISTA INTO @Acc
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CHECKLISTA CURSOR LOCAL FOR
SELECT TOP 1 Name FROM Character WHERE (AccountID=@Acc)
OPEN CHECKLISTA
FETCH NEXT FROM CHECKLISTA INTO @Chars
IF @@ROWCOUNT = 0
begin
DELETE from MEMB_INFO where memb___id=@Acc
end
CLOSE CHECKLISTA
DEALLOCATE CHECKLISTA

FETCH NEXT FROM LISTA INTO @Acc
END
CLOSE LISTA
DEALLOCATE LISTA

and please, if u want to test it BEFORE delete, change line:

DELETE from MEMB_INFO where memb___id=@Acc

to this:

print @Acc+' will be deleted'

RUN FROM QUERY ANALYZER

if anyone found bugs or have something to add, pls reply

 

[marcus84]

Based on this we can write query to remove accounts have not connect to server more than how many days we want......Thanks for script.

 

[nkerz]

Based on this we can write query to remove accounts have not connect to server more than how many days we want......Thanks for script.

exactly

 

[john_d]

here is some useful once i made from the first post.

1. Delete all account/characters/accountcharacter of all accounts that has never been logged in...

DECLARE @Acc varchar(10), @Chars varchar(10)

DECLARE LISTA CURSOR LOCAL FOR
SELECT memb___id FROM MEMB_INFO
OPEN LISTA
FETCH NEXT FROM LISTA INTO @Acc
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CHECKLISTA CURSOR LOCAL FOR
SELECT memb___id FROM memb_stat WHERE (memb___id=@Acc)
OPEN CHECKLISTA
FETCH NEXT FROM CHECKLISTA INTO @Chars

IF @@ROWCOUNT = 0
begin
delete from character where accountid = @Acc
delete from accountcharacter where id = @Acc
delete from memb_info where memb___id = @Acc
end
CLOSE CHECKLISTA
DEALLOCATE CHECKLISTA

FETCH NEXT FROM LISTA INTO @Acc
END
CLOSE LISTA
DEALLOCATE LISTA

ill make more later.. let see how much help it gives... hehehe i really need to prune the server

 

[HeroSlayer]

Based on this we can write query to remove accounts have not connect to server more than how many days we want......Thanks for script.
-----------------------------------------------------------------
Such as if you never connect to play on my serv more than 60 says..your acc will be deleted..

 

[leoferrarezi]

Nice scripts guys... Based on this I made one to delete ghost vaults. Maybe u deleted or banned some account without deleting his vault, and here it goes:

DECLARE @Acc varchar(10), @Chars varchar(10)

DECLARE LISTA CURSOR LOCAL FOR
SELECT AccountId FROM warehouse
OPEN LISTA
FETCH NEXT FROM LISTA INTO @Acc
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CHECKLISTA CURSOR LOCAL FOR
SELECT memb___id FROM memb_info where memb___id=@Acc
OPEN CHECKLISTA
FETCH NEXT FROM CHECKLISTA INTO @Chars
IF @@ROWCOUNT = 0
begin
DELETE from warehouse where AccountId=@Acc
print @acc+' vault deleted'
end
CLOSE CHECKLISTA
DEALLOCATE CHECKLISTA

FETCH NEXT FROM LISTA INTO @Acc
END
CLOSE LISTA
DEALLOCATE LISTA

like above, if u want to test it first, REMOVE the line "DELETE ....."

wish u like it... bye and sorry bad english

 

[FT1FT1]

The True Story

LOL
Well those scripts are all based on mine and ALL WRONG!!!... lol
Here is the corrected version....lol

This is a library so, latest scripts uses basic ones to accomplish his tasks.

This library is not complete here since it will be released as a SkyTeam release soon.

so, here are the basic scripts:

CREATE PROCEDURE [dbo].[FT1WEB_DELETE_CHARACTER]
@NAME VARCHAR(10)
AS

delete from ExtChrData where [name]=@NAME
delete from guildmember where [name]=@NAME
delete from guildmember where ([g_name] in (select g_name from guild where [g_master]=@NAME))
delete from guild where [g_master]=@NAME
delete from Life where [character]=@NAME
delete from lifelog where ChrName=@NAME
delete from optiondata where [name]=@NAME
delete from event_info where charactername=@NAME

UPDATE ACCOUNTCHARACTER SET GAMEID1=NULL WHERE GAMEID1=@NAME
UPDATE ACCOUNTCHARACTER SET GAMEID2=NULL WHERE GAMEID2=@NAME
UPDATE ACCOUNTCHARACTER SET GAMEID3=NULL WHERE GAMEID3=@NAME
UPDATE ACCOUNTCHARACTER SET GAMEID4=NULL WHERE GAMEID4=@NAME
UPDATE ACCOUNTCHARACTER SET GAMEID5=NULL WHERE GAMEID5=@NAME
UPDATE ACCOUNTCHARACTER SET GAMEIDC=NULL WHERE GAMEIDC=@NAME

delete [Character] where [name]=@NAME
GO

CREATE PROCEDURE [dbo].[FT1WEB_DELETE_ACCOUNT]
@ID VARCHAR(10)
AS

--DELETE ACCOUNT PROCEDURE
--DECLARACION DE VARIABLES
DECLARE @GUID INT, @NAME VARCHAR(10)
--OBTENEMOS EL ID
SET @GUID = (SELECT memb_guid from MEMB_INFO WHERE memb___id=@ID)
--PROCEDEMOS A BORRAR LOS REGISTROS
DELETE VI_MIDS_INFO WHERE memb___id = @ID
DELETE VI_LEAV_INFO WHERE memb___id = @ID
DELETE VI_FQNW_INFO WHERE memb___id = @ID
DELETE VI_CURR_INFO WHERE memb___id = @ID
DELETE T_MU2003_EVENT WHERE AccountID = @ID
DELETE T_Event_Barunson WHERE Memb_Id = @ID
DELETE PointMemo WHERE AccountId = @ID
DELETE MIDS_INFO WHERE Memb_Guid = @GUID
DELETE MembInfoModifyOrigin WHERE memb___id= @ID
DELETE MembInfoModify WHERE memb___id= @ID
DELETE MEMB_STAT WHERE memb___id= @ID
DELETE MEMB_NOTICE WHERE memb___id= @ID
DELETE MEMB_INFO WHERE memb___id= @ID
DELETE MEMB_DETA WHERE memb_guid = @GUID
DELETE MEMB_DESC WHERE memb_guid = @GUID
DELETE MEMB__OUT WHERE memb_guid = @GUID
DELETE LOG_UserLogin WHERE UserID = @ID
DELETE LOG_TakeItem WHERE UserID = @ID
DELETE LOG_ITEMSHOP WHERE UserID = @ID
DELETE LEAV_MEMB WHERE Memb_guid = @GUID
DELETE ExtIdData WHERE AccountID = @ID
DELETE ExtChrData WHERE AccountID = @ID
DELETE EVENT_INFO WHERE AccountID = @ID
DELETE CURR_INFO WHERE memb_guid = @GUID
DELETE AccountCharacter WHERE id=@ID
DELETE warehouse WHERE AccountId = @ID

DECLARE LISTA CURSOR LOCAL FORWARD_ONLY FOR
select name from Character where AccountID=@ID

OPEN LISTA
FETCH NEXT FROM LISTA INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
--borramos los characters
delete from ExtChrData where [name]=@NAME
delete from guildmember where [name]=@NAME
delete from guildmember where ([g_name] in (select g_name from guild where [g_master]=@NAME))
delete from guild where [g_master]=@NAME
delete from Life where [character]=@NAME
delete from lifelog where ChrName=@NAME
delete from optiondata where [name]=@NAME
delete from event_info where charactername=@NAME

FETCH NEXT FROM LISTA INTO @NAME
END
CLOSE LISTA
DEALLOCATE LISTA
DELETE Character WHERE AccountID = @ID
GO

Here are the Second level Procedures:

CREATE PROCEDURE [dbo].[FT1WEB_DELETE_EMPTY_ACCOUNTS]
AS

--DELETE ACCOUNTS WITH NO CHARACTERS

DECLARE @ID VARCHAR(10)
--HACEMOS UNA LISTA DE CUENTAS SIN CHARACTERS
DECLARE LISTA CURSOR LOCAL FORWARD_ONLY FOR
/*
SELECT dbo.MEMB_INFO.memb___id
FROM dbo.MEMB_INFO LEFT OUTER JOIN
dbo.[Character] ON dbo.[Character].AccountID = dbo.MEMB_INFO.memb___id
WHERE (dbo.[Character].AccountID IS NULL)
*/
SELECT dbo.MEMB_INFO.memb___id
FROM dbo.MEMB_INFO
LEFT OUTER JOIN dbo.MEMB_STAT ON dbo.MEMB_INFO.memb___id = dbo.MEMB_STAT.memb___id
LEFT OUTER JOIN dbo.[Character] ON dbo.[Character].AccountID = dbo.MEMB_INFO.memb___id
WHERE ((dbo.MEMB_STAT.ConnectTM IS NULL) AND (dbo.[Character].Name IS NULL))
OR ((DATEDIFF(d, ISNULL(dbo.MEMB_STAT.ConnectTM, CONVERT(DATETIME, '1900-01-01 00:00:00', 102)), GETDATE()) > 1)
AND (dbo.[Character].Name IS NULL))

OPEN LISTA
FETCH NEXT FROM LISTA INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
--BORRAMOS CADA CUENTA
PRINT 'DELETING:'+@ID
EXECUTE FT1WEB_DELETE_ACCOUNT @ID
FETCH NEXT FROM LISTA INTO @ID
END
CLOSE LISTA
DEALLOCATE LISTA
GO


CREATE PROCEDURE [dbo].[FT1WEB_DELETE_UNUSED_ACCOUNTS]
AS
DECLARE @ACC VARCHAR(10)

DECLARE LISTA CURSOR LOCAL FOR
SELECT TOP 100 PERCENT UserID
FROM dbo.LOG_UserLogin
GROUP BY UserID
HAVING (MAX(LoginTM) < DATEADD(D,-30,GETDATE()))
ORDER BY MAX(LoginTM)

OPEN LISTA
FETCH NEXT FROM LISTA INTO @ACC
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE FT1WEB_DELETE_ACCOUNT @ACC

FETCH NEXT FROM LISTA INTO @ACC
END
CLOSE LISTA
DEALLOCATE LISTA
GO

U can now call from the SQL Query Analizer the followings procedures to make your work easy:

To delete a single character:
EXECUTE FT1WEB_DELETE_CHARACTER '<CharName>'
The delete an account and all his characters:
EXECUTE FT1WEB_DELETE_ACCOUNT '<AccName>'
To delete every account that doesnt have characters (empty accounts):
EXECUTE FT1WEB_DELETE_EMPTY_ACCOUNTS
To delete accounts that have 30 days without connecting to the server (unused accounts)
EXECUTE FT1WEB_DELETE_UNUSED_ACCOUNTS

Also, un can set a SQL Agent Job to run the last 2 procedures every day at 9AM, those 2 doesnt need parameters so the procedure script is SO SIMPLE:

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'ACCOUNT CLEANUP2')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''ACCOUNT CLEANUP2'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'ACCOUNT CLEANUP2'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'ACCOUNT CLEANUP2', @owner_login_name = N'MUSERVER\Administrator', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'CLEAN 30 DAYS', @command = N'EXECUTE FT1WEB_DELETE_EMPTY_ACCOUNTS
GO
EXECUTE FT1WEB_DELETE_UNUSED_ACCOUNTS
GO', @database_name = N'Muonline', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'every day at 9 AM', @enabled = 1, @freq_type = 4, @active_start_date = 20050323, @active_start_time = 210000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

 

[leoferrarezi]

yeah, FT1FT1, sorry for use ur script to make mine, so the mine works fine to me... lol

 

[nkerz]

sry to say but I've never seen ur script! ^^
at least mine I can say I made on my own
ps: I'm brazillian, thats why "LISTA"

 

[WhiteFox]

Something is missing from this query

DECLARE @Acc varchar(10), @Chars varchar(10)

DECLARE LISTA CURSOR LOCAL FOR
SELECT memb___id FROM MEMB_INFO
OPEN LISTA
FETCH NEXT FROM LISTA INTO @Acc
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CHECKLISTA CURSOR LOCAL FOR
SELECT TOP 1 Name FROM Character WHERE (AccountID=@Acc)
OPEN CHECKLISTA
FETCH NEXT FROM CHECKLISTA INTO @Chars
IF @@ROWCOUNT = 0
begin
DELETE from MEMB_INFO where memb___id=@Acc
end
CLOSE CHECKLISTA
DEALLOCATE CHECKLISTA

FETCH NEXT FROM LISTA INTO @Acc
END
CLOSE LISTA
DEALLOCATE LISTA

This will delete the accounts safely but inside the VI_CURR_INFO table, the accounts is still there. How do I add the VI_CURR_INFO into the query?

 

[john_d]

right below the DELETE from memb_info

 

[WhiteFox]

So add it like this?

DECLARE @Acc varchar(10), @Chars varchar(10)

DECLARE LISTA CURSOR LOCAL FOR
SELECT memb___id FROM MEMB_INFO
OPEN LISTA
FETCH NEXT FROM LISTA INTO @Acc
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CHECKLISTA CURSOR LOCAL FOR
SELECT TOP 1 Name FROM Character WHERE (AccountID=@Acc)
OPEN CHECKLISTA
FETCH NEXT FROM CHECKLISTA INTO @Chars
IF @@ROWCOUNT = 0
begin
DELETE from MEMB_INFO where memb___id=@Acc
DELETE from VI_CURR_INFO where memb___id=@Acc
end
CLOSE CHECKLISTA
DEALLOCATE CHECKLISTA

FETCH NEXT FROM LISTA INTO @Acc
END
CLOSE LISTA
DEALLOCATE LISTA

 

[mikedakill]

And another good job from our hard Mu Stuff workers xD

 

[FT1FT1]

yeah, FT1FT1, sorry for use ur script to make mine, so the mine works fine to me... lol

Man your version doesnt work since the account data is stored on 26 different tables and u are deleting only one. So, your database is full of garbage info from bad deleted accounts.

I did post the right procedure. I like to help and bring u the tools to properly delete accounts with no side efects.

I did laugh since your "twolined" delete account procedure is a real short version compared with the one we use and carefully develop. But thanks anyway for your efforts and sharing actitude. We can make it work all togheter.

 

[themad]

nice job scripters

 

[methodm]

uhm, correct me if i'm wrong, but as far as i can read, this is all about deleting EMPTY accounts... so FT1FT1 tell me, what are those all procedures for if on empty account are NO characters? maybe I am a moron, but i think that "empty" means that there is nothing there. Am i right or am I wrong?

 

[Xe_Xe]

I need some help

i have some kinde of erorr i have 1 acc with unredabal info maybe somone ca help me to Delete it
Server: Msg 823, Level 24, State 2, Line 23
I/O error (torn page) detected during read at offset 0x00000001334000 in file 'C:\Muserver\db\MuOnline_data.mdf'.

Connection Broken

 

[demonas]

he just included in this topic an sql proc to delete accounts empty or not including guild entry,warehouse,log file etc. "wipe clean" an account

 

[hcomaler]

Help with SQL

Please need SQL code will remove all accounts that there is Blocked. Thanks

 

[FT1FT1]

uhm, correct me if i'm wrong, but as far as i can read, this is all about deleting EMPTY accounts... so FT1FT1 tell me, what are those all procedures for if on empty account are NO characters? maybe I am a moron, but i think that "empty" means that there is nothing there. Am i right or am I wrong?

OK Mr.
As all can see u r very low experiensed on DB stuff

Lets say that Someone go to your server and get an account set... then one or two character and a guild, put some items on warehouse and get into DS and BC a few times. After that he get bored and delete all characters.

Results: Your procedure will detect the account as an NO CHARACTER account but will leave orphan records inside atleast 20 tables..

One more example:
Some one setup an account using your website, it will create records in 2 tables (MEMB_INFO and MEMB_DETA) and your procedure will leave some data at MEMB_DETA since your are deleting only MEMB_INFO.

so.. use your own if u dont like mine but look at the facts