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
