crcsupport
asked on
Trying to delete millions of records in a database
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?
USE [master]
GO
ALTER DATABASE <db name> SET RECOVERY full
GO
ALTER DATABASE <db name> SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE [db name]
GO
DBCC SHRINKFILE('<log file name>', 0, TRUNCATEONLY)
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?
USE [master]
GO
ALTER DATABASE <db name> SET RECOVERY full
GO
ALTER DATABASE <db name> SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE [db name]
GO
DBCC SHRINKFILE('<log file name>', 0, TRUNCATEONLY)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
wow I believe this ckpt trick is worth more than my comment :)
ASKER
The server is at simple recovery mode. I thought with simple recovery, it doesn't write any to log file, but it does. I tried to set 'NO WAIT' on our testing server which has the daily moved database. But it doesn't update the variable, instead still shows as 'NOTHING' ;
USE [master]
GO
ALTER DATABASE nextgen SET RECOVERY full
GO
ALTER DATABASE nextgen SET RECOVERY SIMPLE WITH NO_WAIT
GO
SELECT [name], recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;
The server is in simple recovery model and it doesn't have any mirroring or log ship set.