Solved

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

Posted on 2011-03-18
6
360 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 30

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 60

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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 

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 30

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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