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: