Transaction Log full, DBS in SIMPLE recovery, why does my purge routine fail on [SQLSTATE 42000] (Error 9002)
I have a 100 gig database and yesterday I did the following:
Changed recovery model from Full to SIMPLE.
Resize the Transactions log to 2 gig
Turned off the auto growth option on the log.
Disabled the backup transaction log jobs.
I have a purge routine created as a SP that runs 4 times a day and this SP reads and writes data to a history server and then purges the data from production.
This job was working until I did the changes above. Now I'm getting error
The transaction log for database 'ABC' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed.
I've check log_reuse_wait and log_reuse_wait_desc and I see "0" and "NOTHING"
Have I caused myself big problems?
Any assistance would be nice