Why would you back up the transaction log only once a day?
-
I HATE IT when the system eats my message whilst I'm writing it!
One of my database servers is running an ERP package called Munis, it's on SQL2K Enterprise/Win2k3. It's not a small system, around 3.6gig, and it's running a full recovery model. The thing that's odd is that the schedule set up by the vendor (before I started here) has it doing transaction log backups for this thing only once a night after hours!
I am not comfortable with this, it definitely goes against the grain and my better judgment. Personally, I'd be backing up the transaction log every five minutes then copying the backups to my SNAP server.
Does anyone have any idea what the rationale behind such a thing might be? I'm being very nervous about doing anything with this database because of how important it is, otherwise I'd be backing it up frequently to my SNAP server rather than relying on the vendor's methodologies.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
3.6GB doesn't seem large at all, especially for an ERP solution.
Five minutes might be a little excessive, but I'd agree that you probably want to back it up a little more frequently than nightly. Don't stress about deviating from what the vendor set it up as so long as there's no clause in your support contract stating that modifications of that type invalidate the contract (call your vendor). In my experience something like backups, which is so far removed from the application, is rarely something the vendor cares too much about.
-
It's only 3.6gig now, we haven't brought our utility billing online yet! Also it's only been in use about 18 months with payroll coming online only three months ago, so it's a pretty young system. The data partition on the server is 177gig, the database will grow something fierce once we migrate the utility billing data off the mainframe.
We're going to be talking to the vendor soon. Their system is multi-platform and also on Informix and Oracle, and we've found that they really don't know SQL Server. It's possible that this multi-platform design is part of the reason for their job scheduling.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
"....I HATE IT when the system eats my message whilst I'm writing it!...."
You mean when you type a long post, and then it gets wiped out for no reason before you finish !?!? I try to remember to cut & paste into another document temporarily to save it if it's rather long, because that bug does hit from time to time.
Couldn't you just increase the frequency of the t-log backup ? That seems pretty harmless. You can then create your own job to copy the t-logs to another server periodically.
-
Yep. I had to switch to a new program and make sure it ran while I was working on it. I frequently try to remember to do a Ctrl-A and a copy when I'm doing long posts, I'm used to some boards doing automatic draft copy saves while I'm writing stuff.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
There are a few other longstanding bugs on this forum that are annoying ..... oh well
-
Check with the vendor, they may actually have a reason for only doing one tlog backup that makes sense to them. One reason could be that they mistook tlog backups for differentials and really never intended to do tlog backups (shudder).
-SQLBill
-
I've seen this type of set up in two situations, both of which are attempts to maintain perormance during the working day.
1/ High IO disks
- endevouring to improve performance by ensuring that the logs are only ever being written to during business hours without a need to read out the logs to the archive logs. If this is so deal to the underlying IO issues.
2/ Backups are set to "Check Database Integrity, Perform thse checks before doing backups"
- this can lead to very high IO during the trans log backups as the DB gets validated. In this case unselect this option and either perform a validation of the DB out of hours as an independant job or off load the validation to another server (if your backup solution supports this).
Regards
Karl
-
Thanks, Karl. I think those are acceptable reasons in certain circumstances. Fortunately those circumstances don't apply here.
The server has a high-performance disk system, it's an IBM fiber channel system using Ultra320 drives. It is definitely very fast, that 3gig DB backs up to a local device in just over one minute. But it's tremendously underutilized: it's got quad 3GHz Xeon CPUs and 8gig of ram with tops 60-75 users. The transaction log backup is done in 0.05 seconds. I think it'll still be underutilized when we bring utility billing online, except when they're creating bills. That might generate some decent disk activity.
My preferred methodology is to back up the transaction log (append) to a local device, then command shell copy the backup to my SNAP server, done every 10 minutes during business hours. So my transaction log backup job has two steps. Likewise, my nightly database backup (now) has two jobs: back up locally then back it up again to my SNAP server. And at noon I do an incremental backup both locally and to SNAP.
So I think I'm fairly well covered, especially since the SNAP server is backed up twice an hour to our off-site Tivoli system.
What I need to do is finish up my automatic backup testing system...
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply