How to clean up the BAMAlertsApplication database

Question

After a year this db has grown quite big. At thi smoment I have no alerts specified still there are a large number of records in this database. Anybody knows how to clean this up ?

Table Name	# Records	Reserved (KB)	Data (KB)	Indexes (KB)	Unused (KB)
dbo.NS1656726317051365490889801197641825559041739165739Events	1.771.792	1.399.184	1.356.304	42.680	200
dbo.NSQuantum1	689.287	58.648	36.280	21.448	920
dbo.NSRuleFirings1	5.982	720	400	104	216
dbo.NS1656726317051365490889801197641825559041739165739EventBatches	11.025	712	496	16	200

 

 

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 1

Hi Patrick,

I think you want to do something similar as depicted in jesus rodriguez blog (Cleaning up BAM Activity Instances), but then for your alerts. There should be a stored-procedure available I would think that can do that for you or you would have to create one.

Regards,

Steef-Jan Wiggers
MCTS BizTalk Server
http://soa-thoughts.blogspot.com/
If this answers your question please mark it accordingly

 

---

BizTalk

Answer 2

Take a look at this BAM Tasks for Administrators.

Cheers

Bali

MCTS: BizTalk Server

----------------------------------------------------------

Mark As Answer or Vote As Helpful if this helps.

Answer 3

Hi Patrick,

You can execute the following command: bm.exe remove-alerts -View:myView Server:myServer -Database:myDatabase. You will find command (tool) bm in ..\\Program Files\Microsoft BizTalk Server 200x\Tracking. Perhaps with SQL Trace you can find out what is executed within SQL Server.

Regards,

Steef-Jan Wiggers
MCTS BizTalk Server
http://soa-thoughts.blogspot.com/
If this answers your question please mark it accordingly

 

---

BizTalk

Answer 4

This might be helpful to you: http://weblogs.asp.net/gsusx/archive/2008/01/09/cleaning-up-bam-activity-instances.aspx

---

Please mark it as Answer if this answers your question
Thanks.
Mo

Answer 5

BM get-alerts tells me no alerts are defined......

So all alerst are gone still this these table grow by the day....

 

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 6

Hi Patrick,

I think you want to do something similar as depicted in jesus rodriguez blog (Cleaning up BAM Activity Instances), but then for your alerts. There should be a stored-procedure available I would think that can do that for you or you would have to create one.

Regards,

Steef-Jan Wiggers
MCTS BizTalk Server
http://soa-thoughts.blogspot.com/
If this answers your question please mark it accordingly

 

---

BizTalk

I also had a look at the BAM job, and this job is configured and running... There are no records in the table where it tries to delete stuff.

I am wondering what these strange tables are doing on my SQL Server........

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 7

This might be helpful to you: http://weblogs.asp.net/gsusx/archive/2008/01/09/cleaning-up-bam-activity-instances.aspx

---

Please mark it as Answer if this answers your question
Thanks.
Mo

That post deals with the BamPrimaryImport database. The database BAMAlertsApplication is not mentioned at all. All the BAM databases have the expected size and stuff is cleaned regularly. Only this database grows day by day.....

I am looking of a way to get rid of 1.5 million alert records....

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 8

You should refer to:

Removing Obsolete Data
http://msdn.microsoft.com/en-us/library/aa238411(SQL.80).aspx

However I don't want to recommend you do the purge work by yourself in case this is a critical production server. It will be ideal if you can open an incident for it and perform the task with asistance from Microsoft.

---

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 9

You should refer to:

Removing Obsolete Data
http://msdn.microsoft.com/en-us/library/aa238411(SQL.80).aspx

However I don't want to recommend you do the purge work by yourself in case this is a critical production server. It will be ideal if you can open an incident for it and perform the task with asistance from Microsoft.

---

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Hmmm that's a nice reply coming from Microsoft,

Open a case........

Come on, all you BizTalkers out there have a look at that table and see how many records are in there.... Is there nobody who worte a nice clean up procedure ?

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 10

I have found something,

See : http://msdn.microsoft.com/en-us/library/ms171064(v=sql.90).aspx

Currently runnin NSVacuum to see what it does to my tables.....

 

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 11

So the definitive answer is NSVacuum. I wrote a blog post about it and if you ever used BAM you should have a read !

Go on and read it : http://bloggingabout.net/blogs/wellink/archive/2011/02/03/millions-of-records-in-the-bamalertsapplication-and-how-to-get-rid-of-them.aspx

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread