Solved

Unexpected growth of Log(.ldf file)(Deleting 500MB table is creating 10GB of log)

Posted on 2011-03-18
6
352 Views
Last Modified: 2012-05-11
I am trying to detele a table of size 500Mb and it is creating a log file of 10GB. If I try to delete 3-4 tables of that size simultaneously then it is overflowing my tempdb drive. I know deleting in small chunks is a better way to do it but i cannot implement it in my scenario.

Is there any proper explaination of why deleting 500MB table is creating 10GB of log?

0
Comment
Question by:dbaner2
6 Comments
 
LVL 29

Expert Comment

by:Randy Downs
Comment Utility
Maybe this will help - http://stackoverflow.com/questions/571750/make-sql-server-faster-at-manipulating-data-turn-off-transaction-logging

"configure the database (each database on a server can be different) for simple backups the log file won't grow until you back it up. This is done by setting the recovery mode to "simple".

With simple backups the log is only used to hold the state of transactions until they are fully written into the main database.
"
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Are you deleting everything in the tables?  Use TRUNCATE instead or you can try to break up the deletes.
0
 
LVL 14

Expert Comment

by:Daniel_PL
Comment Utility
Are you completely deleting data from tables?
If yes (and table(s) don't have foreign key(s)) you can truncate table(s). When you need part of data from table(s) (basically smaller than deleted part) you can first insert data you want to persist, truncate table and get your data back.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:dbaner2
Comment Utility
I understand that part. But I am more interested to know:

Why deleting 500MB table is creating 10GB of log? What is SQL Server writing into the log file so much more than the data itself?

0
 
LVL 29

Accepted Solution

by:
Randy Downs earned 250 total points
Comment Utility
The log is probably documenting each delete row at a time. Still 10 G seems excessive
0
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 250 total points
Comment Utility
This is by design, delete is performed on row basis so each row needs to be fully logged with maintaining each log sequence number.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now