Solved

Log Files Fill up m- can they be bypassed?

Posted on 2014-01-31
8
196 Views
Last Modified: 2014-02-01
One of my users has a special application that can create very large files (300GB) when performing stochastic runs. When he attempts to delete this very large file within his database, the log files fill up and the deletion stops. This prompts us to truncate the log files and continue the deleting. We cannot delete in chunks because this is simply one large file of data within the database.

In my old mainframe days, we used to mount "scratch tapes" or assign "scratch drives" to take data that we really had no intention of saving.

In this SqlServer scenario, I do not care if we lose the data records that we are deleting but SqlServer wants to"save the day" in case the deletions need to be reversed.

Is there a way to bypass the log files and simply delete large amounts of data in a database? Is there a way to setup a ficticious drive that will take the log file entries and have an unlimited soze because it is not really a data file but just a named target?

Thanks!
0
Comment
Question by:ZogFromNewJersey
  • 4
  • 3
8 Comments
 
LVL 3

Accepted Solution

by:
Ryan Lanham earned 250 total points
ID: 39824858
1) Convert the Recovery Model to Simple Recovery

If you are truncating the transaction logs, this means you are breaking the T-Log LSN (Log Sequence Numbers). This follows that if disaster comes, you would not be able to restore your T-Logs and there would be no option for you to do point in time recovery. If you are fine with this situation and there is nothing to worry, I suggest that you change your recovery model to Simple Recovery Model. This way, you will not have extra ordinary growth of your log file.

2) Start Taking Transaction Log Backup

If your business does not support loss of data or requires having point in time recovery, you cannot afford anything less than Full Recovery Model. In Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39824879
the fastest will be this:
* create a temporary table with ONLY the data you want to keep
* truncate the table
* insert back the data you wanted to keep from the temporary table

this has some constraints, like if you have foreign keys or the like on the table(s), you might first need to drop those (but first note them down), and recreate them afterwards...
0
 

Author Comment

by:ZogFromNewJersey
ID: 39825015
I am already in the simple Recovery mode. The log files get maxed out even in that mode.

The entire tabel gets deleted. Nothing is saved, But the table is 300GB in size - far larger than the log files allocation.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:ZogFromNewJersey
ID: 39825221
I am probably looking for the impossible. Thanks for the try!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39825943
Did you try TRUNCATE TABLE ? That statement only logs the fact, but not the data deleted.
0
 

Author Comment

by:ZogFromNewJersey
ID: 39826413
After the log files fills up, the program abends. Then the log file undeletes the records it deleted. I tried truncating the log file first but the same error persists.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39826433
Sorry but I don't refer to TRUNCATE LOG, but TRUNCATE TABLE....
0
 

Author Comment

by:ZogFromNewJersey
ID: 39827219
The table is not flat and has relational tables indexed to it. I have to delete in a way not to create orphan records. Thanks for the idea and it would work with a flat table.
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 28
string fuctions 4 25
SQL Error - Query 6 24
SQL Syntax: How to force case sensitive query? 2 20
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

816 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

10 Experts available now in Live!

Get 1:1 Help Now