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.
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.
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.
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 :-)