Question for the SQL gurus!

Inane baanter or words of wisdom - you decide...
Post Reply
User avatar
Wudzi
Baaaaa!
Baaaaa!
Posts: 1146
Joined: Wed Aug 23, 2006 7:34 pm 0
Location: Southampton
Contact:

Question for the SQL gurus!

Post by Wudzi »

I've got a database of about 400GB that used to take 5 hours to backup through the scheduler in MS SQL Enterprise manager. One day, it just jumped about to 20 hours, even though no system changes were made that I'm aware.

It still completes OK, and I can't find anything obvious errors in the event log, but this is causing a problem in that I'm being pressed for system upgrades which I don't want to do if a restore is going to take a whole day.

I've asked our resident DBA to have a look, but he hasn't come back with anything (you know what DBA's are like ;))

Anyone know any possible causes?

This within SQL 2000 enterprise
Clustered 2003 server environment (problem occurs on both nodes)
Quad Xeon 3.2 ghz/8gb RAM
User avatar
tufty
Sheepie Whore
Sheepie Whore
Posts: 12524
Joined: Fri Jan 07, 2005 4:52 pm 0
Location: French Alps

Post by tufty »

My guess would be that something is accessing the system whilst the backup is going on, and that the backup is thus having to lock or snapshot everything. What else is running against the DB in and around the original 5 hour window?
"massive retard™", "full of shit™", "tiny bitch™", "retarted™", "a bit of a one trick pony™", "petty™", "simple minded idealistic twat™", "miserable humourless fucker™"
User avatar
rowan
Baaaaaaa!
Baaaaaaa!
Posts: 7965
Joined: Tue Nov 25, 2003 11:53 pm 0
Location: Notts.

Post by rowan »

So.. Where's the SQL question ? :)

To what do you back it up? Is it possible the device has something else
now running at the same time thrashing it?
User avatar
disciple
Sheepie Whore
Sheepie Whore
Posts: 13941
Joined: Mon Oct 27, 2003 11:33 pm 0
XBL Gamertag: LiberMentis
PSN: Prime_Heretic
Location: Greenfield
Contact:

Post by disciple »

I'm not much on SQL Admin side of things but a shot in the dark that has an easy fix, it's not got a stupidly large transaction log has it?
PS4 : Prime_Heretic
Steam: bitstorm
Elite Dangerous: Cmdr Britain
User avatar
bcass
Baaaaa!
Baaaaa!
Posts: 1932
Joined: Mon Apr 22, 2002 7:54 pm 0
Location: Island of Dots
Contact:

Post by bcass »

Someone hasn't created a Schedule that clashes with the existing one have they?
User avatar
Wudzi
Baaaaa!
Baaaaa!
Posts: 1146
Joined: Wed Aug 23, 2006 7:34 pm 0
Location: Southampton
Contact:

Post by Wudzi »

Thanks for the responses so far!

To answer a few questions, This is a backup to file (which is then in turn backed up to tape), but the problem is the backing up to file, so it isn't a problem with any devices as such.

Transaction logs was my first idea as well, but it isn't that.

tufty - That sounds promising. The database is up 24/7 and there will be activity against it even in the early hours of the morning. One thing I have noticed in the Maintainance Plan history is that although the start time and end time of the backup are correct, the duration is miles out (see the attached screenshot)

http://www.onelouder.co.uk/images/sqlwierdness.jpg
User avatar
rowan
Baaaaaaa!
Baaaaaaa!
Posts: 7965
Joined: Tue Nov 25, 2003 11:53 pm 0
Location: Notts.

Post by rowan »

Is it possible it's to do with that V shield (antivirus isn't it?) thing I see in
your systray having some sig update that's now caning it during the file
write?
User avatar
Wibble
Baaaaaa!
Baaaaaa!
Posts: 3317
Joined: Fri May 02, 2003 10:13 pm 0
XBL Gamertag: misterwibble
Location: The Hague - The Netherlands
Contact:

Post by Wibble »

Backup to an UNC path? Is it a dedicated network segment, or could something else be putting a high load on the network as well (though 15 hours is a long time to keep a network busy)? Any changes on the network level that might have impacted this, like switching network adapters (I noticed one's disconnected), changing port speed from gigabit to megabit speeds, full to half duplex?

Doesn't explain the odd duration, though. Could the system time be off?

The only other thing I can think of is that there's a lot of unused (and unfreed) space in the database, which is processed, but not backed up.

-W
User avatar
Wudzi
Baaaaa!
Baaaaa!
Posts: 1146
Joined: Wed Aug 23, 2006 7:34 pm 0
Location: Southampton
Contact:

Post by Wudzi »

Wibble wrote:Backup to an UNC path? Is it a dedicated network segment, or could something else be putting a high load on the network as well (though 15 hours is a long time to keep a network busy)? Any changes on the network level that might have impacted this, like switching network adapters (I noticed one's disconnected), changing port speed from gigabit to megabit speeds, full to half duplex?

Doesn't explain the odd duration, though. Could the system time be off?

The only other thing I can think of is that there's a lot of unused (and unfreed) space in the database, which is processed, but not backed up.

-W
Although its a UNC path, the network name is resource of the cluster, so it is backing up to an attached drive, not along the network. The system time is ok - Interesting, it is set to create a txt file logging when the job has finished, and the duration is correct in there.

That V shield is McAfee VirusScan. I'll try disabling it and seeing if that helps - Thanks for the suggestion.
davetsmith
Posts: 7
Joined: Wed Jan 22, 2003 10:58 pm 0
Location: Southampton

Post by davetsmith »

Does it not have to go through the network redirector when backing up to a UNC path?

As the unc path is a resource on the cluster could yo back up to the drive it's on instead and see if that's any quicker?
User avatar
Wudzi
Baaaaa!
Baaaaa!
Posts: 1146
Joined: Wed Aug 23, 2006 7:34 pm 0
Location: Southampton
Contact:

Post by Wudzi »

davetsmith wrote:Does it not have to go through the network redirector when backing up to a UNC path?

As the unc path is a resource on the cluster could yo back up to the drive it's on instead and see if that's any quicker?
Thanks - I'll give that a go. It has always been backed up through the UNC path, but it's definately worth a go. I didn't think it went through the network adapter, but I could well be wrong.
User avatar
Wudzi
Baaaaa!
Baaaaa!
Posts: 1146
Joined: Wed Aug 23, 2006 7:34 pm 0
Location: Southampton
Contact:

Post by Wudzi »

Right, bit of an update.

I kicked off a backup manually through query analyser rather than enterprise manager backing up directly to a path.

It was a little quicker (18 hours) but still a lot slower than it used to be. One thing I noticed is the rate was about 6.5mb/sec so it isn't that the backup is freezing at certain points, the transfer rate is generally slow.
Post Reply