Solved

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

Posted on 2011-03-18
6
356 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
ID: 35166907
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
ID: 35166938
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
ID: 35166973
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:dbaner2
ID: 35166975
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
ID: 35167019
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
ID: 35167033
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
My Query is not giving correct result. Please help 5 40
tempdb log contention 16 39
Please help with the below query - SQL Server 11 18
SQL Help 27 45
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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