Link to home
Start Free TrialLog in
Avatar of crcsupport
crcsupportFlag for United States of America

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)
SOLUTION
Avatar of magarity
magarity

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of crcsupport

ASKER

The server is on virtual machince with limited space. When I run this, should I keep shrinking log file and run batch? Or is there any way to disable log file growing?

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
wow I believe this ckpt trick is worth more than my comment :)