shrink or truncate log while a delete is running

SQL Server 2005

Ran a 'Delete from' on a table which is a fully logged operation.

is there a way to truncate or shrink the log WHILE the delete is running?

Log is filling up my drive
itsonlyme4Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Add another log file for that db on a different drive.  This isn't normally done, but it beats the db stopping all activity :-).

Once you get things back under control, you can remove the extra log file and shrink the main log file back down to a more normal size.
0
 
Brian CroweDatabase AdministratorCommented:
No.  the log file will not shrink while there is an ongoing transaction.  If possible I would recommend putting the database in simple recovery for the duration of the DELETE.

USE myDatabase;
GO

ALTER DATABASE myDatabase
SET RECOVERY SIMPLE;

<Perform Delete>

ALTER DATABASE myDatabase
SET RECOVERY FULL;
0
 
Steve WalesSenior Database AdministratorCommented:
I agree with Brian on the fact that you can't shrink while an active transaction is present.

For future reference, try  deleting in smaller chunks, performing a log backup between each one.

However ... setting the database to Simple Recovery Mode for the duration of the delete (or for any time in a production database) destroys your recovery chain.

The minute you take a database from full to simple recovery mode, your ability to perform point in time recovery dies.

Proceed with caution if you go down that path.  Take an IMMEDIATE backup (but even then, until your backup ends, you're unprotected).
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.

 
itsonlyme4Author Commented:
I cannot set the DB to Simple recovery mode while the delete operation is running.   if I stop the delete..  is the rollback also a logged event?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
is there a way to truncate or shrink the log WHILE the delete is running?
You can run the shrink but won't help much since won't touch in the entry logs created by the delete command.
It's your DELETE command committing in blocks? If so, that should help if run regularly transaction log backups.
Otherwise you can follow Scott's suggestion and add a new transaction log file on another disk with more empty space.
Just don't change the Recovery Model.
0
 
Scott PletcherSenior DBACommented:
SQL can't truncate active log records, so none of the DELETE log records themselves could be affected.  In that sense, "you can't truncate" during a DELETE is true.  

But the log is a circular file.  Thus, I believe it's theoretically possible the log could truncate and shrink with a DELETE running if the DELETE happened to start at/near the beginning of the log file.  In that case, some previously used log space further on in the log file might be freed (truncated).
0
 
itsonlyme4Author Commented:
Thank you.  I will try that..   another question..  the LOGFILE is set to File Growth 10 % and Max Filesize  2,097,152

Right now my log file is 69GB and there is 17GB free on the drive.     the delete operation seems to be stalled.  can I change the File growth and MAX size on the fly while the delete is running?
0
 
itsonlyme4Author Commented:
Thank you all!!!    I was getting ready to add another logfile and my delete operation completed!!    Thank  you again!
0
 
Scott PletcherSenior DBACommented:
>> can I change the File growth and MAX size on the fly while the delete is running? <<

Yes.  Those can be changed at any time.
0
 
itsonlyme4Author Commented:
Thank you!
0
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.

All Courses

From novice to tech pro — start learning today.