Re: Backing up transaction logs gives poor performance.

Did you do what I suggested in my prior post?

Make sure that the service account for SQL Server and Agent has appropriate permissions on the share and then type in the UNC name.

You can try to login interactively using the same account as the SQL Server service is using and see if you can create (etc) a file on that share.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"SW" <anonymousgravy@xxxxxxxxxxx> wrote in message news:9E792D0C-2702-45D3-A38C-288C9DDC3226@xxxxxxxxxxxxxxxx
Hi Tibor,

It's taken me a while but I've tried to implement what you suggested.
I cannot get the SQL Maintenance plan to save the transaction log files any where over the network. It records an "Access denied" error in event log, EVEN if I try to save to a FAT32 disk over the network!

How would I ensure that the service account has the correct permissions?

Regards
SW

"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote in message news:85EEEC3F-D20C-46D2-A981-F6F9D3E52E67@xxxxxxxxxxxxxxxx
Make sure that the service account for SQL Server and Agent has appropriate permissions on the share and then type in the UNC name. Yes, the "verify" has some effect, but it shouldn't be drastic (especially in 2000 where this doesn't really do a proper verify).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"SW" <anonymousgravy@xxxxxxxxxxx> wrote in message news:97A446EE-ED74-4F2E-8F60-13CBC31816E8@xxxxxxxxxxxxxxxx
Hi,

It won't allow me to save the transaction logs to anywhere other than to a disk drive on the server.
While there (the maintenance plan dialog box), I noticed that I have the verify log backup option ticked. Do you know if this is a processor-intensive activity?

SW

"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote in message news:B4B698F4-E5B9-4AE6-976E-3AD9D9A27299@xxxxxxxxxxxxxxxx
Try the network drives first and see if it makes a difference.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"SW" <anonymousgravy@xxxxxxxxxxx> wrote in message news:3AD369AF-EBA6-4505-B7F4-B0E298BFAF4A@xxxxxxxxxxxxxxxx
Hi,

The only option I have at the moment is saving them to a network drive, but I haven't checked to see if this is even possible.
Do you think it would make so much difference that it would be worth installing new disks?

Thanks,
SW

"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote in message news:0C271F78-637B-4E18-AFB3-C56C79EE2937@xxxxxxxxxxxxxxxx
Do you have everything on the RAID5 array? RAID5 is famous for being slow for writes. First step would be to write the log backups to some other drive. And perhaps even separating the log file(s) so an isolated RAID1 or RAID10.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"SW" <anonymousgravy@xxxxxxxxxxx> wrote in message news:7D564181-5EC5-4B6F-A0A0-2B9B0D04E6A8@xxxxxxxxxxxxxxxx
Hi,

I have a problem with SQL server being very slow to access and use while I'm backing up transaction logs. I'm backing up the logs every half hour, and it makes the whole thing run so slowly it's driving the users nuts. It puts the application into a spin while the logs are being backed up and it's being used by a lot of people. If there's less people its still noticeable but not as severe.
Its SQL 2000, on a W2003 server, XP clients. Server has twin Xeon 2.8 cpus with 4GB RAM and a RAID 5 array. There's 2 instances of SQL server hosting 2 separate, different applications. The database sizes are varied. On one of the apps there's a database with a 11.5GB .mdf file, and a 6GB .ldf file. Transaction log backup files from this database are typically about 20MB while the application is busy. The users of both apps complain of the same problem - intermittent slowness so bad it sometimes makes the app unusable.
The log backup seems to cripple the entire machine.
Where do I start to cure this problem?
Is there a way I can get the maintenance plan to use less cpu? Or is it more likely a memory problem?
Any help appreciated, Thanks in advance,

SW








.

Relevant Pages

  • Re: problem with query moving data from 1 dbase to another.
    ... You register a SQL Server instance, where such an instance can have several databases. ... Tibor Karaszi, SQL Server MVP ... > you know how to connect to an existing database, ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Auto Exiting Store Procedures
    ... "Peter" wrote in message ... SQL Server will terminate the batch. ... >>Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Maintenance Plan "fails"..
    ... > The only thing in the report file is that it was unable to delete one of the ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Inside SQL Server
    ... You could continue reading, check differences and ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Return Key (identity) on INSERT operation (SQL/ASP)
    ... hopefully he can expedite processing your full subscription. ... SQL Server MVP ... Columnist, SQL Server Professional ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)
Loading