Solved

Log file for MS SQL Server is full

Posted on 2001-08-06
10
154 Views
Last Modified: 2010-05-02
I have written a Visual Basic application that is attempting to write 120,000 records to a table in a Microsoft SQL Server database.

However, as I add the records, the adding process slows down to a crawl.  After adding 50,000 records, I aborted the add job as it was adding records very, very slowly.

Upon looking at the available space on the drive, I found that there was just a few megabytes left.  When I tried to delete the 50,000 records, MS SQL Server gave me an error message saying that the transaction log file for the database was full and said I should backup the transaction log for the database to free up some log space.

As I don't do checkpoints, I don't care about the transaction log.  So how can I get around this problem when I want to add 120,000 records at one time?  Do I need to turn the transaction log off somehow?

Thank you for your input.
0
Comment
Question by:EYoung
  • 5
  • 4
10 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 6357887
In the database, set Truncate Log On Checkpoint flag, this will not log the transactions to the log file and you should get your inserts in without any problems. I am not aware of a way of doing this other than on the server itself though so you would have to be very careful if you required logging of other transactions.
0
 
LVL 7

Author Comment

by:EYoung
ID: 6360945
I checked the Truncate Log On Checkpoint checkbox for the database and re-ran the job.  The job is not running any faster.

I use the .AddNew and .Update commands to put the new records into the table.  Should I be using the .Insert command to make it run faster or is there something else I am overlooking?

Thanks for any help.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6361044
Using Connection.Execute "INSERT INTO ..." is often faster then the the ADO methods
0
 
LVL 7

Author Comment

by:EYoung
ID: 6361129
I have not used the Connection.Execute "INSERT INTO..." command before.  Could you provide a simple example?

The code I usually use is:

        Set rsLines = New ADODB.Recordset
        rsLines.Source = "Select * from Lines " & _
            "Where Line = '@&)'"
        Set rsLines.ActiveConnection = cnSQL
        rsLines.LockType = adLockOptimistic
        rsLines.CursorType = adOpenKeyset
        rsLines.Open
       
        rsLines.AddNew
        rsLines.Fields("Line") = Mid(mRecordID, 4, 3)
        rsLines.Update


If I change to the Connection.Execute command, will that really make a difference or will there only be a minor improvement?  (I need to find a way to significantly improve the input speed.)

Right now the input starts out adding 100 records in about 5 seconds.  However, after every 10,000 records are added the time to add 100 records increases by about 5 more seconds.  So after adding 40,000 records, the time to add the next 100 records has reached 20 seconds or so.  At that rate it will take a long time to add 120,000 records.  Help.

Thanks for the assistance.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6361395
You may 50% gain because all your lines can be replaced with:

cnSQL.execute "Insert INTO Lines(Line) VALUES ('" & Mid(mRecordID, 4, 3) & "')"

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Author Comment

by:EYoung
ID: 6361505
emoreau,

The actual SQL table has about 70 fields.  What would the code look like for say 3 fields?  Would it look like:

   cnSQL.execute "Insert INTO Lines(Line) VALUES ('" & Mid(mRecordID, 4, 3) & "')", Lines(Size) VALUES ('" & "Mid Size" & "')", Lines(Color) VALUES ('" & "Blue" & "')"

Thanks for the help.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6362010
This way:

cnSQL.execute "Insert INTO Lines(Line, Size, Color) VALUES ('" & Mid(mRecordID, 4, 3) & "', 'Mid Size', 'Blue')"
0
 
LVL 7

Author Comment

by:EYoung
ID: 6362103
I understand (thanks) but to do that with 70 or so fields would get fairly complex.

I just can't believe that adding 120,000 records would bring MS SQL Server to its knees.  I have already set the Truncate Log On Checkpoint but that does not seem to help.

Any other ideas?
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 50 total points
ID: 6362207
SQL Server is not that busy! You just don't seem to use it correctly!

How does your loop is set? If the Select query is inside the loop, you can get it out of the loop.
0
 
LVL 7

Author Comment

by:EYoung
ID: 6364875
I am closing out this question as I won't be able to test that until next Monday.  Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vba is buggy because of corrupted user profile. Any fast way to fix it? 28 125
DIR issue 7 50
MS Access - Capture pressed key onclick 4 28
Run code from text file in vb 1 56
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

929 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