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 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:

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: