The InventSumDateTable and InventSumDateTrans tables are used for some reports in Dynamics AX (at least in 2009). When the reports finish they delete the records stored in these tables, however if the report is terminated mid-process these records aren't cleaned out and this can cause substantial performance problems for these reports.
I wanted to setup some sort of notification to tell me when there is data in these tables when it's unlikely that someone would be running a report. I decided I didn't want to automatically clean it out since it's possible they could actually be running a report and because I'd like to restart the AOS at the same time... don't know if it matters, but don't want anything cached.
Here is a the SQL job that I created which will email me when there are records in the table, but won't email me when there isn't. I scheduled the job to run nightly after the backup completes. The database name, database mail profile, and recipient email address need to be configured.
USE [AXDATABASE]
GO
SET ANSI_NULLS
ON
GO
DECLARE @RECORD_CHECK INT
SELECT COUNT(RECID) [Count]
INTO
#tmpCountInventSumDate
FROM
InventSumDateTable
INSERT
#tmpCountInventSumDate
( [Count]
)
SELECT COUNT(RECID)
FROM InventSumDateTrans
SET @RECORD_CHECK = ( SELECT SUM([Count])
FROM #tmpCountInventSumDate
)
DROP TABLE
#tmpCountInventSumDate
WHILE @RECORD_CHECK >
0
BEGIN
EXEC
msdb.dbo.sp_send_dbmail
@profile_name = '[Database Mail Profile]'
,
@recipients = '[recipient
email address]'
,
@subject = 'InventSumDate
Tables'
,
@body = 'InventSumDateTable
and InventSumDateTrans temp tables are populated.'
,
@importance = 'high'
,
@query = 'SELECT ''InventSumDateTable'' [Table]
, COUNT(RECID) [Count]
INTO #tmpCountInventSumDate
FROM AXDATABASE.dbo.InventSumDateTable
INSERT #tmpCountInventSumDate
( [Table]
, [Count]
)
SELECT ''InventSumDateTrans''
, COUNT(RECID)
FROM AXDATABASE.dbo.InventSumDateTrans
SELECT *
FROM #tmpCountInventSumDate
DROP TABLE #tmpCountInventSumDate'
SET
@RECORD_CHECK = 0
END
