Re: Backing up transaction logs gives poor performance.

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: Cluster or Mirror
    ... it takes for the transaction log to be applied from the failed node to the ... Looking for a SQL Server replication book? ... The application only knows 1 SQL server connection. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Unable to Apply SP4 to SQL 2000 Cluster (new Node)
    ... Rebuild the node in the failover cluster. ... Scenario 1" in SQL Server 2000 Books Online. ... This setup process updates to SP4 only the binaries on the new ...
    (microsoft.public.sqlserver.clustering)
  • Re: Changing database size
    ... You need to use DBCC SHRINKFILE, ... How to Shrink the SQL Server 7.0 Transaction Log ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ...
    (microsoft.public.sqlserver.server)
  • Re: WSS 3.0 question
    ... I followed the advise given in removing WSS 3.0 etc, ... the server is complaining that the SQL service(?) was tempered with or corrupt. ... I may just instal the SQL server as I was going eventuall use it anyway. ... If WSUS 3.0 is installed, I would suggest you uninstall it and then you install WSS 3.0. ...
    (microsoft.public.windows.server.sbs)
  • Re: SQL Server 2005 Cluster Setup Quiz
    ... I did test and it did not install the client tools. ... http://www.clusterhelp.com - Cluster Training ... Microsoft SQL Server MVP ... Provide a template on how to read SQL Server 2005 setup log files. ...
    (microsoft.public.sqlserver.clustering)