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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

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
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
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 2005

From novice to tech pro — start learning today.