Forums  Register  Login  My Profile  Inbox  Address Book  My Subscription  My Forums 

Member List  Search  FAQ  Ticket List  Log Out

 

Regards with SQL Scripts to delete mail in database

  		Logged in as: Guest
Users viewing this topic: none
  Printable Version
All Forums >> [Archiving & Fax] >> GFI MailArchiver >> Regards with SQL Scripts to delete mail in database Page: [1]
Login
Message << Older Topic   Newer Topic >>
Regards with SQL Scripts to delete mail in database - 21.Jul.2009 2:02:09 AM   
markie

 

Posts: 2
Joined: 21.Jul.2009
Status: offline 		Hi, I have read the previous post for delete mail in SQL database...

code:





declare @tbl table
(id int)

-- filter messages to be deleted by date
/*
insert into @tbl
select id from arc where [date]<'2005-09-20'
*/

-- filter messages to be deleted by subject
/*
insert into @tbl
select id from arc where full_subject like 'xx%'
*/

-- filter messages to be deleted by email (sender/recipient)
/*
insert into @tbl
select id from arc_add where email='test@testdomain.com'
*/

-- filter messages to be deleted by owner (use objectGuid from Active Directory)
/*
insert into @tbl
select id from arc_owners inner join arc_users on arc_owners.usrid=arc_users.usrid
where [name]='d0fba5f09c43be41ba93dcea92767204'
*/

-- filter messages to be deleted by message-id
/*
insert into @tbl
select id from arc where message_id = '<001201c5bdeb$182a3270$3200a8c0@testdomain.com>'
*/

delete from arc_sources where id in (select id from @tbl)
delete from arc_bodies where id in (select id from @tbl)
delete from arc_att where id in (select id from @tbl)
delete from arc_add where id in (select id from @tbl)
delete from arc_owners where id in (select id from @tbl)
delete from arc where id in (select id from @tbl)

delete from arc_threads
where NOT EXISTS(select id from arc where thread_id=arc_threads.thread_id)

I deleted some mail by using sender address and it's successfully, but if i search in the mailbox for the sender email address and it's return those delete email.However, they can not read anymore instead saying "Error:Object reference not set to an instance of an object"Is that anyway to remove the record from the search as well. I know those mails are gone but don't want them in the record... 

< Message edited by markie -- 21.Jul.2009 2:03:18 AM >
Post #: 1
RE: Regards with SQL Scripts to delete mail in database - 21.Jul.2009 6:50:50 AM   
dkrueger

 

Posts: 90
Joined: 8.Jul.2009
Status: offline 		markie,

The emails are still listed in the index and will be returned in a search. However opening the mails itself would fail since you removed them from the database.

You might have missed this step:
-- Now add the deleted messages to the arc_delete table so that the messages will be removed from the search index
insert into arc_delete(id) select id from tbl

After the following index job completed the deleted emails should not be listed in a search any longer.

_____________________________

Regards,

Dietmar Krueger
GFI Software - www.gfi.com
Web & Mail Security, Archiving & Fax, Networking & Security

(in reply to markie)
Post #: 2
RE: Regards with SQL Scripts to delete mail in database - 22.Jul.2009 7:01:04 PM   
markie

 

Posts: 2
Joined: 21.Jul.2009
Status: offline 		Just add this to end of the above script and execute it again?

(in reply to dkrueger)
Post #: 3
RE: Regards with SQL Scripts to delete mail in database - 24.Jul.2009 2:21:42 AM   
dkrueger

 

Posts: 90
Joined: 8.Jul.2009
Status: offline 		markie,

The complete script looks like this:

-- ** STEP A **
create table tbl
(id int)

-- ** STEP B **
-- filter messages to be deleted by date
/*insert into tbl select id from arc where [date]<'2005-09-20'*/
-- filter messages to be deleted by subject
/*insert into tbl select id from arc where full_subject like 'xx%'*/
-- filter messages to be deleted by email (sender/recipient)
/*insert into tbl select id from arc_add where email='test@testdomain.com'*/
-- filter messages to be deleted by owner (use objectGuid from Active Directory)
/*insert into tbl select id from arc_owners inner join arc_users on arc_owners.usrid=arc_users.usrid  where [name]='d0fba5f09c43be41ba93dcea92767204'*/
-- filter messages to be deleted by message-id
/*insert into tbl select id from arc where message_id = '<001201c5bdeb$182a3270$3200a8c0@testdomain.com>'*/

-- ** STEP C **
-- Perform this step if you are using FileSystem to keep your email messages
-- Now select all deleted messages and copy them to a text file
-- Download the ids from tbl by using the marc4_mssql_download script

-- ** STEP D **
-- Now delete the messages in @tbl from all archive tables
delete from arc_sources where id in (select id from tbl)
delete from arc_bodies where id in (select id from tbl)
delete from arc_att where id in (select id from tbl)
delete from arc_add where id in (select id from tbl)
delete from arc_owners where id in (select id from tbl)
delete from arc_retention where id in (select id from tbl)
delete from arc_tags_msg_updates where msgid in (select id from tbl)
delete from arc_tags_msg_deleted where msgid in (select id from tbl)
delete from arc_tags_msg where msgid in (select id from tbl)
delete from arc where id in (select id from tbl)
delete from arc_hash where id in (select id from tbl)
delete from arc_keys where msgid in (select id from tbl)
delete from arc_threads where not exists(select id from arc where thread_id=arc_threads.thread_id)
-- Now add the deleted messages to the arc_delete table so that the messages will be removed from the search index
insert into arc_delete(id) select id from tbl

-- ** STEP E **
-- Delete temporary table
/* drop table tbl */

WARNING: GFI Software is not responsible for any data loss and will not be held accountable for the misuse of these scripts. Use them at your own risk!


_____________________________

Regards,

Dietmar Krueger
GFI Software - www.gfi.com
Web & Mail Security, Archiving & Fax, Networking & Security

(in reply to markie)
Post #: 4
RE: Regards with SQL Scripts to delete mail in database - 1.Sep.2009 3:33:38 PM   
bhgewilson

 

Posts: 7
Joined: 5.May2009
Status: offline 		Does anyone know the syntax to delete say messages sent from userx to usery

Thanks,

Brad

(in reply to dkrueger)
Post #: 5
RE: Regards with SQL Scripts to delete mail in database - 10.Sep.2009 3:07:17 AM   
Idealshopping

 

Posts: 76
Joined: 6.Apr.2006
Status: offline 		Can anyone confirm these scripts still work with with Ver6.1?

Cheers 

(in reply to bhgewilson)
Post #: 6
RE: Regards with SQL Scripts to delete mail in database - 10.Sep.2009 9:28:20 AM   
crutter

 

Posts: 10
Joined: 8.Sep.2009
Status: offline 		I'd like to know too.

I assume we run this as a query within SQL ?

(in reply to Idealshopping)
Post #: 7
RE: Regards with SQL Scripts to delete mail in database - 10.Sep.2009 10:03:56 AM   
Idealshopping

 

Posts: 76
Joined: 6.Apr.2006
Status: offline
quote:

ORIGINAL: crutter

I'd like to know too.

I assume we run this as a query within SQL ?



Correct and against the relevant DB. I haven't used it in a while but want to cut out some rubbish but am having trouble getting it to work.. which hasn't been an issue before.

(in reply to crutter)
Post #: 8
RE: Regards with SQL Scripts to delete mail in database - 18.Sep.2009 3:07:39 AM   
Idealshopping

 

Posts: 76
Joined: 6.Apr.2006
Status: offline
quote:

ORIGINAL: Idealshopping

Can anyone confirm these scripts still work with with Ver6.1?

Cheers 


Can no-one confirm if these work with Ver6.1? I am having problems with them.

(in reply to Idealshopping)
Post #: 9
RE: Regards with SQL Scripts to delete mail in database - 18.Sep.2009 3:11:49 AM   
crutter

 

Posts: 10
Joined: 8.Sep.2009
Status: offline 		Seems to have worked for me, but the email I deleted is still listed in my outlook connector mailbox so I'm getting an error popping up every so often.

Do u have to deleted and recreate ur GFI outlook mailbox after u've deleted mail directly from the SQL database ?

(in reply to Idealshopping)
Post #: 10
RE: Regards with SQL Scripts to delete mail in database - 18.Sep.2009 7:56:41 AM   
Idealshopping

 

Posts: 76
Joined: 6.Apr.2006
Status: offline
quote:

ORIGINAL: crutter

Seems to have worked for me, but the email I deleted is still listed in my outlook connector mailbox so I'm getting an error popping up every so often.

Do u have to deleted and recreate ur GFI outlook mailbox after u've deleted mail directly from the SQL database ?


No, it sounds like you may have missed the step to remove it form the index.

(in reply to crutter)
Post #: 11
Page:   [1]
All Forums >> [Archiving & Fax] >> GFI MailArchiver >> Regards with SQL Scripts to delete mail in database Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts