I was about to recommend that you do backups nightly and transaction log backups during the day -- until I read your last sentence.
How about upgrading to SCSI disks?
We had a small 600MB database on a desktop box (with an IDE drive) for testing purposes a while back. When a backup was issued the CPU utilization jumped to 90% or better until the backup finished. Luckily, we were not running production on this box.
If purchasing a real disk subsystem (SCSI) is not possible, perhaps you could get another disk drive (and maybe an IDE controller card) and try backing up to the new disk.
If this still does not work for you, I recommend that you consider nightly backups and transaction log backups every x interval (30 or 60 minutes) during the day.
Yeah, you may have a training issue to deal with -- but this is easy enough. And if you can tell them that their options (1) are suffer every hour when a backup happens or (2) learn how to restore the transaction logs, I am guessing that they will choose option (2).
Finally, how often would they need to restore the database anyway? Shouldn't the answer be "never?"
Keith, SQL Server MVP
> Hi All
> Completely out of my depth on this so I thought I would ask the masters for
> Basically we had a user running 1 x SQL 6.5 DB for their accounts on a WinNT
> 4.0 Server with 1 x PII 733Mhz, 1 x 8GB IDE HD and 1GB of RAM. They used
> this server solely for SQL server and were happy for about 2 years until an
> accounting report took 20 mins to run (due to the amount of data in it -
> even though I didn't think it was a lot!!) so they wanted it fixing.
> Having tested, confirmed and 'dragged' them onto an upgrade, we souped their
> server up to 2 x PII 800, 1 x 40GB IDE HD, 1GB of RAM and upgraded their DB
> to SQL 2000.
> The accounting report isn't a problem anymore, but the problem is that now
> they have upgraded to SQL 2000 they are getting a monumental lag for about
> 5-6mins when a backup is taking place. They say it completely freezes their
> client machines whilst it is doing this and they only regain control say
> every 20 seconds for a split second or so.
> The backup is hourly, so you can imagine the problems it causes. Reason for
> the backup is that they are not a big company, only about 6 users on at a
> time, but they like the security of restoring the data without having to
> re-enter too much data.
> They say they never had this lag with SQL 6.5 and we believe them because
> they would have whinged straight away (like they are doing now) if it had.
> FYR, database is about 260MB in size (this includes data and log, as we
> don't use the log really), The maintenance plan that runs the backup
> performs no other task other than the 'Complete Backup' tab and is backing
> up the file to the same hard disk not tape.
> Could somebody please advise as to why this is happening and how we can get
> round it.
> Please note that these people can backup and restore a backup because it is
> one file. To train them to backup and restore transaction logs and backup
> files would take a year. This is not an option.
> Many thanks.