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)
LVL 1
crcsupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

magarityCommented:
Deleting millions of rows is always a pain!  I can't give a definitive answer to the safety of those ALTER statements but they do look OK.

Once you get that figured out, you can use this handy script to delete a few thousand at a time to keep it manageable: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28292477.html
(instead of the WHERE datefield in that one you just put your own critera)
0
Carl TawnSystems and Integration DeveloperCommented:
You should delete in batches rather than trying to do the whole lot in one go. The reason the log fills up is because deleting 1 million rows in one go is a single transaction so there is no opportunity to trim the log part way through.

Take a look at this for an example of batch deleting: http://stackoverflow.com/questions/896810/sql-batched-delete
0
BanthorCommented:
Changing to Simple recovery will break mirrors and log shipping. I don't know how your Production environment is configured.

You are going to use some kind of looping to and subsets. IF this has too be completed during some kind of downtime window just create a script that deletes a bunch, executes a log backup, then shrink
set  rowcount 10 

declare @rc int 
delete from admintools.dbo.bkp_files where BackupStartDate < Dateadd(Day,-7,getdate())

SELECT @rc = @@ROWCOUNT 
While @rc > 0 
BEGIN
    Begin Try
		Begin Transaction
			delete from admintools.dbo.bkp_files where BackupStartDate < Dateadd(Day,-7,getdate())
		Commit Transaction 
	end try 
	Begin Catch 
			Rollback Transaction
			-- Add Backup TRN Log 
			
	End Catch

END 

	-- Add Backup TRN Log 
	-- Execute Schrink File 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BanthorCommented:
you may want to try a value bigger than 10
set  rowcount 10

Open in new window

0
BanthorCommented:
Update , Forgot to reset @rc
set  rowcount 100

declare @rc int 
delete from admintools.dbo.bkp_files where BackupStartDate < Dateadd(Day,-7,getdate())

SELECT @rc = @@ROWCOUNT 
While @rc > 0 
BEGIN

    Begin Try
		Begin Transaction
			delete from admintools.dbo.bkp_files where BackupStartDate < Dateadd(Day,-7,getdate())
			SELECT @rc = @@ROWCOUNT
		Commit Transaction 
	end try 
	Begin Catch 
			Rollback Transaction
			-- Add Backup TRN Log 
			
	End Catch

END 

	-- Add Backup TRN Log 
	-- Execute Schrink File 

Open in new window

0
Scott PletcherSenior DBACommented:
NO -- don't run that code.

If your db is already in Simple mode, you should be OK as is.

You do need to DELETE the rows in batches, but at least 1000 at a time.

Run this code to verify that nothing is holding log records:

SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = '<db_name>'

If the second column is "NOTHING", then you're good.


DECLARE @rows_deleted int
DECLARE @total_rows_deleted int
SET @rows_deleted = 0
SET @total_rows_deleted = 0

WHILE 1 = 1
BEGIN
    DELETE TOP (1000)
    FROM [dbo].[table_name]
    SET @rows_deleted = @@ROWCOUNT
    SET @total_rows_deleted = @total_rows_deleted + @rows_deleted
    IF @rows_deleted < 1000
        BREAK
    WAITFOR DELAY '00:00:00.150'
END --WHILE

PRINT 'Total Rows Deleted = ' + CAST(@total_rows_deleted AS varchar(10))
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Scott's approach is what I would use myself. But I definitely would print out the (floating) 'total rows deleted' count in the loop (prior to the waitfor), so you have something to watch.
0
Scott PletcherSenior DBACommented:
Not a bad thought, but the PRINTs will be buffered anyway, so you won't see the output until the end, or the buffer fills up (which it won't unless you print many more bytes than just row counts).

If you want to check the progress initially, just cancel the query.  Then you should see the:
1000 row(s) processed
or the like messages over and over.

Then re-start the code.
0
BanthorCommented:
Rather than print , use raiserror with nowait and the message is printed at execution.

Declare @erm nvarchar(max) 
SELECT @erm = 'Deleted ' + cast(@RC as varchar(10)) + ' Rows' 
Raiserror(@ERM,0,1) with nowait 

Open in new window

0
BanthorCommented:
I do not see any reason for the delay and without a transaction wrapper the entire execution will wait for 1 to no longer equal 1
So you will be locking the table for the entire time and still filling the trn with a transaction that can not be mitigated by a log backup.
0
SurranoSystem EngineerCommented:
I agree with Banthor: a commit in the loop is a must, otherwise a loop makes no sense.

I'm not familiar with cursor usage in MS SQL but if you can manage it it's much better (performance-wise) than deleting first 1000 all the time.

If you need to delete the majority of the records, say 9M out of 10M total it's much better to
- create a new table from all the rows remaining (still with the cursor approach),
- drop old table,
- rename new table to old name
This probably needs application outage, though.

As a final solution I'd recommend reorganising this logging table to use partitions that rotate e.g. every day or every week. Partitions can be truncated:
- truncate is DDL
- truncate is O(1)
- no tedious transaction log
- no outage

Cheers,
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Nope, cursor is not better, because a commit destroys it. The delete should run with autocommit or explicit commit, of course, that much is true.
0
SurranoSystem EngineerCommented:
Sad to learn that. It works like charm in Oracle. I mean we use cursors to select the lines, but the rest of the code deletes lines based on data returned by cursor, not directly using the cursor. I don't know if it makes sense in MS SQL though.
0
Scott PletcherSenior DBACommented:
>> without a transaction wrapper the entire execution will wait for 1 to no longer equal 1
So you will be locking the table for the entire time and still filling the trn with a transaction that can not be mitigated by a log backup. <<


100% wrong.  Outside of an explicit transaction, SQL Server will treat every statement as a separate transaction and automatically commit it, or roll it back if an error occurred.
0
Scott PletcherSenior DBACommented:
>> Rather than print , use raiserror with nowait and the message is printed at execution. <<

That is also not true (in the sense that you won't see the message in your output screen yet).  Easy enough to demonstrate:


DECLARE @counter smallint
SET @counter = 1

WHILE @counter <= 50
BEGIN
    RAISERROR('Test message #%d.', 16, 1, @counter) WITH NOWAIT
    WAITFOR DELAY '00:00:00.250'
    SET @counter = @counter + 1
END
0
crcsupportAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
You want it to show as "nothing".

Again, do NOT put the db into FULL mode for dealing with the DELETEs -- that will just cause you issues when you do certain other tasks.  I'm not sure what web page you saw that on, but just IGNORE IT.

If you don't have enough log space to store 1,000 deleted rows, then increase the log space yourself ahead of time -- don't let it autogrow, as that is much less efficient.


-- increase log space to handle 1000 (or however many) deleted rows
ALTER DATABASE <db name> MODIFY FILE ( NAME = <logical_not_physical_log_file_name>, SIZE = 40MB )
-- if you get an error stating that the log is already larger than that, then you're good, and you can ignore the msg.


If you're that restricted on disk space, add a CHECKPOINT to the DELETE loop, to insure log truncation occurs in a very timely manner:


DECLARE @rows_deleted int
DECLARE @total_rows_deleted int
SET @rows_deleted = 0
SET @total_rows_deleted = 0

WHILE 1 = 1
BEGIN
    DELETE TOP (1000)
    FROM [dbo].[table_name]
    SET @rows_deleted = @@ROWCOUNT
    SET @total_rows_deleted = @total_rows_deleted + @rows_deleted
    IF @rows_deleted < 1000
        BREAK
    CHECKPOINT
    WAITFOR DELAY '00:00:00.150'
END --WHILE

PRINT 'Total Rows Deleted = ' + CAST(@total_rows_deleted AS varchar(10))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SurranoSystem EngineerCommented:
wow I believe this ckpt trick is worth more than my comment :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.