For a while, I’ve been wanting to explore deeper or “hack” into the internals of the BizTalk Databases, i.e. BizTalkMsgBox and BizTalkDTA. When do records gets stored in these database, how long do they stay there, and when do they get “cleaned-up”.
So here is my first scenario. I use a SQL command to Find out how many rows of each table in your BizTalk Databases (as per previous blog post).
Now let me show you how I collected the data. I ran two queries, once against each of the two databases, captured the date/time to use the exact same time (for purposes of joining later) and store statistics. I change the label ‘Before’ to ‘During’ and ‘After’ later when I run the script again.
USE BizTalkMsgBoxDb declare @WorkDateTime dateTime2 = getDate() declare @Label varchar(15) = 'Test' print @WorkDateTime print @Label insert into StatSnap.dbo.TableRowCounts SELECT @WorkDateTime, 'BizTalkMsgDb', sc.name +'.'+ ta.name TableName , @Label, SUM(pa.rows) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN sys.schemas sc ON ta.SCHEMA_ID = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) GROUP BY sc.name,ta.name ORDER BY SUM(pa.rows) DESC USE BizTalkDTADb insert into StatSnap.dbo.TableRowCounts SELECT @WorkDateTime, 'BizTalkMsgDb', sc.name +'.'+ ta.name TableName , @Label, SUM(pa.rows) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN sys.schemas sc ON ta.SCHEMA_ID = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) GROUP BY sc.name,ta.name ORDER BY SUM(pa.rows) DESC
Once the data is collected, the following query shows the which tables changed in size. I manually entered the date/times to match-up one specific earlier run from any later runs.
use statsnap --truncate table TableRowCounts --select * from TableRowCounts select TRCBefore.DBName, TRCBefore.TableName, TRCBefore.Label, TRCBefore.RowCountStat, TRCDuring.Label, TRCDuring.RowCountStat, TRCAfter.Label, TRCAfter.RowCountStat, TRCLater.Label, TRCLater.RowCountStat from TableRowCounts as TRCBefore inner join TableRowCounts as TRCDuring on TRCBefore.DBname = TRCDuring.DBName and TRCBefore.TableName = TRCDuring.Tablename inner join TableRowCounts as TRCAfter on TRCBefore.DBname = TRCAfter.DBName and TRCBefore.TableName = TRCAfter.Tablename where TRCBefore.DateTimeStat = '2015-01-27 09:16:42.2800000' and TRCDuring.DateTimeStat = '2015-01-27 09:19:55.6900000' and TRCAfter.DateTimeStat = '2015-01-27 09:27:30.7900000' and (TRCBefore.RowCountStat != TRCDuring.RowCountStat or TRCBefore.RowCountStat != TRCAfter.RowCountStat )
The resulting query combining all stats:
I created an orchestration that takes a simple messages from a file receive location, maps it, delays 3 minutes, then sends it to a file. I snapshot the stats ‘Before’ starting the orchestration, ‘During’ – while the 3 minute delay is happening, then again ‘After’ the orchestration has completed.
One of the most important columns is nState, the values are described in this StackOverflow post.
1 : Started 2 : Completed 3 : Terminated 4 : Suspended 5 : ReadyToRun 6 : Active 8 : Dehydrated 16: CompletedWithDiscardedMessages 32: SuspendedNonResumable 64: InBreakpoint
It turns out that I had four suspended instances for a few weeks back, 2 not-resumeable (nstate=32), and 2 resumeable (nstate=4) . You can actually see the error messages in the nvcErrorDescription column. I terminated those, then ran the query again. After deleting them, the following remain. Note that one has nState = 8, which would be my dehydrated orchestration (in the 3 minute delay). By running the query frequently while the orchestration is running, you can watch the nState change from 2 to 8. Sometimes it stayed with the value of 2 the whole 3 minutes.
:Later, I dropped four different messages, causing 4 orchestration instances to start. Below you can see which GUIDs are the same, and one one change per instance
The bts_applications table contains hosts, not applications:
This was not a surprise to me, because I had seen it before, but someone new to the internal of the database might find it surprising. When you create a new “Host” using BizTalk Admin Console (or via Object Explorer, WMI, or Powershell Extensions), five SQL tables are created for each host.
I will continue with more observations and details in Part 2.