• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Transaction log is full when deleting millions

Hi..
I have stored procedure that deletes 10  million records a night.
Everytime  I run it I get Transaction Log full.... I tried running it in batches or 1 million.

How can I delete the records and ensure the log is not full so the delete completes.
I don't even care about the transaction log... they are temp tables and records.

thanks
0
JElster
Asked:
JElster
  • 9
  • 9
  • 5
  • +5
1 Solution
 
LowfatspreadCommented:
if they are temp tables have you considered jus truncateing them?

what proportion of the total table does the 10 million represent?

do you commit after the 1 million have been deleted?

how big is your transaction log?

do you start with a database backuo...?

can you explain the scenario is some more detail (how many tables... straight deletes or are triggers involved, related tables....)

 is your system 24/7 ?
 
0
 
JElsterAuthor Commented:
The 10 million represent 80% of the data...
I don't do a commit... just a DELETE FROM TABLEA
I think my log is 10Mg --  maybe that's the problem...
Yes... I start with  
BACKUP LOG   WITH TRUNCATE ONLY
DBCC SHRINKDATADASE

THANKS
0
 
rizwanidreesCommented:
you can run following commands after one batch to empty logs

BACKUP LOG MYDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MYDB_LOG, 0,TRUNCATEONLY)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
chapmandewCommented:
DO NOT TRUNCATE YOUR LOG FILE!  If you dont' care about it, then set your recovery mode to full.

Your log file is going to get large doing that many deletes because you're doing it in one transaction.  If you create a loop and delete 10,000 records at a time, this problem should subside...IF you are doing transaction log backups or if you set your recovery mode to simple.
0
 
JElsterAuthor Commented:
What do I do... if I don't care about the log?  Do I still back it up ? What command should I use?
thaks
0
 
chapmandewCommented:
if you don't care, then set the recovery model to simple.

alter database databasename
set recovery simple

then you shouldn't have to worry about the log, ongoing.  You'll still need to shrink it right now if you're out of space though.

also, you'll still want to delete  your records in chunks, not all at one time.
0
 
JElsterAuthor Commented:
what is the correct code..

alter database databasename
set recovery simple
delete from databasename.table

?
Do I also backup?

thanks
0
 
chapmandewCommented:
to set to full recovery:

alter database databasename
set recovery simple

now, do the following:

CHECKPOINT

dbcc shrinkfile('yourdbname_log', 0)

what version of sql server are you on?
0
 
JElsterAuthor Commented:
2000

what is checkpoint?
0
 
chapmandewCommented:
A checkpoint writes all dirty pages (pages that have been changed in memory, but yet to be committed) to disk.  Necessary to do this so that the active portion of the log is moved so inactive portion can be truncated.

http://msdn.microsoft.com/en-us/library/ms188748.aspx
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
The following are methods to limit transaction log growth:

> Hardware (the obvious): increase log drive space
> Recovery mode: If a data loss of 1 day is acceptable, change the database recovery mode to simple
> Transaction volume control: Assuming the table is YOURTABLE, create a new table named YOURTABLE_NEW put the 20% you want to keep into the new table with same structure.  Insert all rows you want to *keep* nito that new table.  Once the new table is filled, simply rename the old table to YOUTABLE_OLD and rename the new table to YOURTABLE.  
> Development:Limit the size of the rollback segment through the code.  Set up the maximum rollback size to 10000 rows....
> All above recommendations...

Hope this helps
0
 
JElsterAuthor Commented:
After each batch delete ?

alter database databasename
set recovery simple
CHECKPOINT
dbcc shrinkfile('yourdbname_log', 0)


????
0
 
chapmandewCommented:
No...just do the steps I gave you one time.  Then, go about doing your batch delete through a loop.
0
 
dervishiCommented:
backup transaction log file using:

BACKUP LOG MYDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MYDB_LOG, 0,TRUNCATEONLY)
0
 
JElsterAuthor Commented:
Should I backup the log and truncate..?
I don't care about the log... just want to delete and make the log the smallest possible
0
 
chapmandewCommented:
Should I backup the log and truncate..?

Sure, you can if you don't care about the log.  But, you're better off just switching to simple because then this won't happen again.  
0
 
dervishiCommented:
Should I backup the log and truncate..?

yes you should and then you can shrink the log file.
0
 
JElsterAuthor Commented:
I'm confused...
Do I set it first to simple -  than then from then on

BACKUP LOG MYDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MYDB_LOG, 0,TRUNCATEONLY)

????

thanks
0
 
grayeCommented:
I always wonder if folks really understand the implications of switching to the the Simple Recovery mode.
It reminds me of this story...   A customer brings a computer in for repair.  The computer is hopelessly lost and needs to be reloaded.  The computer repair person asks the customer if all of their data has been backed up before they reformat the C: drive.  The customer almost always says "Yes", but then after the technician has reformated and reloaded the OS, the customer crys "where's all my files!"   Did the customer really understand the implications of reformatting the C: drive... I doubt it.
My point (if I ever get around to it) is that switching recovery models isn't something that you do lightly... and generaly requires you to fully understand the protection mechanisms that are in place to protect your database.
0
 
chapmandewCommented:
If you set it to simple, you don't have to do the log backup, just the shrink.  So:

alter database yourdbname
set recovery simple
go
checkpoint
go
dbcc shrinkfile('dbname_log',0)
go

then you're done.
0
 
chapmandewCommented:
>>I always wonder if folks really understand the implications of switching to the the Simple Recovery mode.

Im pretty sure I understand. :)

 Full recovery mode only helps if you're doing log backups, which the user is not.  
0
 
grayeCommented:
Tim, I know you understand it...   but I wonder about the folks asking the questions
0
 
chapmandewCommented:
I'd say a solid 99% of the people who use SQL server don't understand it (or understand how/why the trans log is used anyway). I still see this question about 5 times a week. Not really sure better ways to convey it. Ideas?
0
 
Anthony PerkinsCommented:
Which brings me to my pet peeve with SQL Server Express.  Why Microsoft ships it with the default of Full Recovery is beyond me.  At the very least MS should give a clear warning at the implications of using Full Recovery.  Something like:  If you do not backup the Transaction Log than bad things will happen.

IMHO the vast majority of developers using SQL Server Express, just graduated from MS Access and do not even know that there is a Transaction Log let alone what it does.
0
 
JElsterAuthor Commented:
Why is the log used.... I'm not aware of a useful way of using or querying it to see the transaction history without a third party tool.
0
 
Anthony PerkinsCommented:
>>Not really sure better ways to convey it. Ideas?<<
Perhaps you should write an article on the subject. Wait, you did:
Help! My SQL Server Log File is too big!!!
http://blogs.techrepublic.com.com/datacenter/?p=448
:)
0
 
grayeCommented:
Here is something that I wrote up just a few minutes ago on a similar question:
Most folks who run a production database use the full recovery model.   This because it provides better protection for both "user mistakes" and "failures".
Most folks are familar with failures...where a hard drive crashes and you have to restore from the last backup.  But a lot of new SQL administrator are not that familiar with recovering from a "user mistake".  For example, a user accidently deletes a table that contains useful data.  Your job is to return the database to the exact point in time before the user delete the table.  That kind of restore requires a full recovery model.
Most folks who have a database are actually concerned about the potential loss of data (particulary the loss of data between full backups).   Consider your bank... Do you want your bank account data to be covered by a system where an entire days worth of transactions could be lost if a failure or user mistake occured?

If you have the time, I'd recommend that you read the following article (or the similar article from other members here)
http://www.emmet-gray.com/Articles/SQL_LogMaintenance.htm 
0
 
Anthony PerkinsCommented:
>>Why is the log used....<<
It helps if you want to keep your job.  Simply put, if something should go vitally wrong at 2PM, you do not want to have to go back to your last full backup (assuming that you are making backups), but rather you want to have the option of restoring to the minute prior to the problem occurring.  This can only happen if you are using Full Recovery.  But then it is your responsibility to ensure that you are running Transaction Log backups.

That is just one of its many benefits.  It has many more functions that you can pick up from BOL.
0
 
Anthony PerkinsCommented:
Here is another article on the subject written by Mark Wills:
Managing the Transaction Log for the Accidental DBA
http://sqlservernation.com/blogs/admin/archive/2009/05/28/managing-the-transaction-log-for-the-accidental-dba.aspx
0
 
Anthony PerkinsCommented:
In summary:  Use Full recovery only if you are prepared to make Transaction Log backups, if not change it to Simple.  Just understand the consequences of your actions and make sure your resume is updated. :)
0
 
JElsterAuthor Commented:
thanks - makes sense now....
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 9
  • 9
  • 5
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now