Trying to delete millions of records in a database
Posted on 2013-12-19
I'm trying to delete millions of records, they are all useless logs being recorded. When I delete, my transaction log gets filled even though my database is set to simple recovery. Why log is getting filled even with simple recovery? So it shows error messages.
Now, I searched online, it shows that I have to change to FULL recovery, then change back to Simple to change log_reuse_wait to NO_WAIT so that it doesn't fill up log file. Is this safe to run in production server?
ALTER DATABASE <db name> SET RECOVERY full
ALTER DATABASE <db name> SET RECOVERY SIMPLE WITH NO_WAIT;
USE [db name]
DBCC SHRINKFILE('<log file name>', 0, TRUNCATEONLY)