With some luck your environment was installed by a BizTalk professional and the most critical maintenance tasks were configured at installation. When I say critical maintenance tasks, I mean the BizTalk backup job, DTA Purge & Archive,… If these tasks are not configured I’m sure your production BizTalk environment will only be running smoothly for a couple of days or months but definitely not for years!
I’m not going to write about basic configuration tasks as most BizTalk professionals should be aware of these.
At Codit Managed Services our goal is to detect, avoid and solve big and small issues before it’s too late. This way we often detect smaller missing configuration tasks. Not necessary at first, but definitely necessary for the BizTalk environment to keep on running smoothly through the years!
Purging/archiving the BAM databases:
I’m going to explain you how you should maintain the BizTalk BAM databases:
In most environments these maintenance tasks are missing. You will find the necessary information on the internet, but probably only when it’s urgent and you really need it.
My goal is to provide you with this information in time, so you are able to configure the BAM maintenance tasks without putting your SQL server or yourself under some heavy stress. If it comes to BAM and data purging, it’s rather simple. By default, nothing will be purged or archived. Yes, even if you have the BizTalk backup and all the other jobs succesfully running!
I have seen a production environment, running for only two years where the BAMPrimaryImport database had a size of 90GB! It takes a lot of time and processing power to purge such a database. To maintain and purge the BAM databases you will need to configure how long you want to keep the BAM data, configure archiving, trigger several SQL SSIS packages,…
The problem is: purging is configured per Activity, so this is a task for the developer and not a task for the guy who installed the BizTalk environment. You will find all the information to do this on following sites:
http://blogs.biztalk360.com/bam-production-environment-management
http://blogs.msdn.com/b/nabeelp/archive/2013/10/22/sql-script-to-clean-up-old-bamarchive-tables.aspx
http://www.biztalkbill.com/Home/tabid/40/EntryId/103/BizTalk-BAM-Archiving.aspx
http://geekswithblogs.net/andym/archive/2009/05/21/132346.aspx
BAMData not being purged immediately?
Something very important that you should be aware of is the fact that if you for example want to keep a year of data, you will have to wait another year for the data being purged/archived in a supported way!
That’s why it’s so important to configure these jobs in time! It’s not like the DTA P&A job, where the data is purged immediately.
You can find more information about this on following blog: http://www.richardhallgren.com/bam-tracking-data-not-moved-to-bam-archive-database
Purging the BAMAlertsApplication database:
I’m rather sure the following maintenance task will not be sheduled to clean your BAMAlertsApplication database. I only discovered this myself a couple of days ago! Probably not a lot of people notice this database because it’s rather small. After 2 years running in production with a small load it had a size of 8GB. But it’s 8GB of (wasted) diskspace!
If you search on the internet on how to clean this database you will find nothing official by Microsoft,…
Credits on how to purge the BAMAlertsApplication go to Patrick Wellink and his blogpost: http://wellink.bloggingabout.net/2011/02/03/millions-of-records-in-the-bamalertsapplication-and-how-to-get-rid-of-them-nsvacuum-to-the-rescue
If you wonder what the NSVacuum stored procedure looks like, you can find it below:
USE [BAMAlertsApplication]
GO
BEGIN
DECLARE @QuantumsVacuumed INT
DECLARE @QuantumsRemaining INT
DECLARE @VacuumStatus INT
DECLARE @StartTime DATETIME
SET @QuantumsVacuumed = 0
SET @QuantumsRemaining = 0
SET @StartTime = GETUTCDATE()
— Volunteer to be a deadlock victim
SET DEADLOCK_PRIORITY LOW
EXEC @VacuumStatus = [dbo].[NSVacuumCheckTimeAndState] @StartTime, @SecondsToRun
IF (0 != @VacuumStatus) — VacuumStatus 0 == Running
BEGIN
GOTO CountAndExit
END
DECLARE @CutoffTime DATETIME
DECLARE @RetentionAge INT
DECLARE @VacuumedAllClasses BIT
— Remember the last run time and null counts
UPDATE [dbo].[NSVacuumState] SET LastVacuumTime = @StartTime, LastTimeVacuumEventCount = 0, LastTimeVacuumNotificationCount = 0
— Get the retention age from the configuration table (there should only be 1 row)
SELECT TOP 1 @RetentionAge = RetentionAge FROM [dbo].[NSApplicationConfig]
SET @CutoffTime = DATEADD(second, -@RetentionAge, GETUTCDATE())
— Vacuum incomplete event batches
EXEC [dbo].[NSVacuumEventClasses] @CutoffTime, 1
— Mark expired quantums as ‘being vacuumed’
UPDATE [dbo].[NSQuantum1] SET QuantumStatusCode = 32
WHERE (QuantumStatusCode & 64) > 0 AND — Marked completed
(EndTime < @CutoffTime) — Old
DECLARE @QuantumId INT
DECLARE @QuantumEndTime DATETIME
DECLARE QuantumsCursor CURSOR
LOCAL READ_ONLY FAST_FORWARD
FOR
SELECT QuantumId, EndTime
FROM NSQuantum1 WITH (READUNCOMMITTED)
WHERE QuantumStatusCode = 32
ORDER BY EndTime
OPEN QuantumsCursor
— Do until told otherwise or the time limit expires
WHILE (1=1)
BEGIN
EXEC @VacuumStatus = [dbo].[NSVacuumCheckTimeAndState] @StartTime, @SecondsToRun
IF (0 != @VacuumStatus) — VacuumStatus 0 == Running
BEGIN
BREAK
END
FETCH NEXT FROM QuantumsCursor INTO @QuantumId, @QuantumEndTime
IF (@@FETCH_STATUS != 0)
BEGIN
SET @VacuumStatus = 2 — VacuumStatus 2 == Completed
SET @QuantumsRemaining = 0
GOTO CloseCursorAndExit
END
— Vacuum the Notifications
EXEC [dbo].[NSVacuumNotificationClasses] @QuantumId, @VacuumedAllClasses OUTPUT
EXEC @VacuumStatus = [dbo].[NSVacuumCheckTimeAndState] @StartTime, @SecondsToRun
IF (0 != @VacuumStatus)
BEGIN
BREAK
END
— Vacuum the Events in this quantum
EXEC [dbo].[NSVacuumEventClasses] @QuantumEndTime, 0
— Delete this Quantum from NSQuantums1 if its related records were also deleted
IF (1 = @VacuumedAllClasses)
BEGIN
DELETE [dbo].[NSQuantum1] WHERE QuantumId = @QuantumId
— Update the count of quantums vacuumed
SET @QuantumsVacuumed = @QuantumsVacuumed + 1
END
EXEC @VacuumStatus = [dbo].[NSVacuumCheckTimeAndState] @StartTime, @SecondsToRun
IF (0 != @VacuumStatus)
BEGIN
BREAK
END
END — Main WHILE loop
CloseCursorAndExit:
CLOSE QuantumsCursor
DEALLOCATE QuantumsCursor
CountAndExit:
— Report progress
SET @QuantumsRemaining = (SELECT COUNT(*) FROM [dbo].[NSQuantum1] WITH (READUNCOMMITTED) WHERE QuantumStatusCode = 32)
SELECT @VacuumStatus AS Status, @QuantumsVacuumed AS QuantumsVacuumed,
@QuantumsRemaining AS QuantumsRemaining
END — NSVacuum
You need shedule the NSVacuum command on your environment. Run this step by step, as it puts a lot of stress on your SQL server.
The content of this post is something every BizTalk professional should add to his BizTalk Server installation/deployment procedure!
Subscribe to our RSS feed