For as long as weâ€™ve been using SQL Monitor weâ€™ve had the Purge settings set to DO NOT PURGE. Yesterday I tried to adjust a few of those selecting 1yr as the Retention/Purge value. I changed only 2 of the Purge choices. Within a very short period of time the tempdb database on the SQL Server hosting our Data Repository blew up and ate up all the available drive space on the drive hosting the tempdb log file.
To fix this I had to change the Purge settings back to NEVER PURGE and restart SQL Server so as to shrink tempdb back down. Twice Iâ€™ve done this and both times tempdb blew up and so I know for a fact that changing the Purge settings is causing this issue.
Does anyone know if there is any way to either control how the purge is done or to calculate in advance how much space the tempdb needs so that IO can adjust the drive space and then re-do the purge settings? I am at the point where Iâ€™d like to purge some of the data that is beyond 1 year but I canâ€™t till I know what tempdb will need to get the job done.
PS â€“ If RedGate support has some tool/script/query that can be used to manually purge older data Iâ€™m happy to take a look at it.