Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    52

    Problem with host and MS SQL Log Files

    Hello,

    I am having a problem with my web host handling a MS SQL problem that I am having.


    --- ERROR ---
    Microsoft OLE DB Provider for SQL Server error '80040e14'

    The log file for database 'DATABASENAME' is full. Back up the transaction log for the database to free up some log space.

    /bx/bannersupload.asp, line 88
    --- END OF ERROR ---

    I am using 2 ASP scripts BanManPro and LinkXpro and have for many years without any problems. I have used it on shared servers as well as my own dedicated.

    I have always had a maintenance plan that ran every day and truncated the transaction logs every night. This is a very popular and widely used script.

    I sent the author my problem and he responded:

    *--- author statement ---
    Is this SQL 7? If so, just check the Truncate Log on Checkpoint for both databases and you will be set. If it is SQL 2000, setup a maintenance plan that backs up the database and log file every day and then the log file will truncate at that time.
    ***--- end author statement -----

    The log fills up after about a week or two and with only about 500 banner impressions a day then I have to open a ticket for them to clear the logs. This indicates to me that there does not seem to be a plan that truncates the file.

    ---THEIR MOST RECENT RESPONSE---
    It isn't a case of following a developers instructions for this. They are used to these types of intensive scripts being on a dedicated server, with resources to spare, and waste, on multiple backups of DB's residing on the very same server.

    We'll raise the transaction log once again, as allowing daily backups of the transaction log, on top of the backups already in place of the DB it's self, is simply not something that should be done on a shared server.
    ---END OF THEIR MOST RECENT RESPONSE---


    I have had this problem since January.

    Question 1: Does anybody have any idea of how to resolve this?

    Question 2: Is it true that shared hosts do not set up maintenance plans to backup MS SQL transaction logs so that they can automatically truncate?

    Any input and/or info that I can give my host would be appreciated.

    Thank you,

    Larry M.

  2. #2
    Join Date
    Mar 2003
    Location
    sf
    Posts
    350
    yes, read this article http://support.microsoft.com/default...b;EN-US;256650

    ask them to create a maintainance job for you.

    I used to have a db that had a log file ~9Gb in size. and after follow this help, i was able to reduce it to 10mb.
    Last edited by amzhost; 04-18-2004 at 11:02 PM.

  3. #3
    Join Date
    May 2002
    Posts
    52
    thanks. :-)

    Sorry, I should have mentioned that it is SQL 2000.

    I asked them to set up a plan and they said "allowing daily backups of the transaction log, on top of the backups already in place of the DB it's self, is simply not something that should be done on a shared server.".

    They apparently will not set up a plan to backup so that the logs will truncate. I have been asking them to resolve this since January.

    hmmm. ?

    Thanks

  4. #4
    Join Date
    Mar 2003
    Location
    sf
    Posts
    350
    yes, i works for both 2000 and 7. I have it setup on my sql server 2000 server and works well.

  5. #5
    Join Date
    Dec 2003
    Location
    UK
    Posts
    652
    As an MCDBA, I find it unbelievable that a host would choose to allow an end user's system to grind to a halt due to tran log backups. If they're not going to do tran log backups, then they should be advising you which recovery model to use (or setting it themselves)

    As a support person, I find that this however is the default config for most SQL Servers I have to troubleshoot.

    If you don't need "incremental backup" capability, then set the database to "Simple Recovery", this will regularly truncate the log.

    If you have daily full database backups, then there's probably no benefit in having daily log backups. Log backups should ideally be hourly (or more frequent).

    You should however set your backups based on your actual recovery needs.

  6. #6
    Join Date
    May 2002
    Posts
    52
    This is very useful information. Thank you.

    Does anybody else have any more info or stuff that I can tell my host?

    I do not think that I am off-base here; I am just attempting to get as much info as I can to give to my host.

    My database file would probably only be 30mb or less if they could/would truncate the transaction files.

    I am hoping to hear from some hosts that understand what is happening here.

    Any and all suggestions appreciated.

    Thanks!

  7. #7
    Join Date
    Dec 2003
    Location
    UK
    Posts
    652
    If you have DBO rights to your database (I would hope you do), then you should be able to do all this yourself. Refer to SQL Books On-line which you would have installed when you installed SQL Enterprise Manager on your local PC.

    I would, however, expect that your host whould do this as a one off, it'll take them 5-10 mins and get you off their back (in the nicest possible way !)

    I would for my customers, except I don't offer MS hosting :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •