Database Query Failure

[Jack_Dorsey]

Greetings All,

I am receiving a "database query failure" error while attempting to backup all of our databases on a MSSQL server.  There are approx. 100 databases with approx. 105GB's of data.  Backing up to DLT-V4 (320GB compressed).  The error code is 0xe0008492.  When I click on the link for the error I also see this message:  "Insufficient resources to create UMS scheduler" under each of the databases I was attempting to backup in the Job Log.  While the backup is taking place all connections to the MSSQL server are dropped with TCP/IP errors and no other connections are allowed.  I thought that with AOFO and the SQL Agent connections should be allowed to be made while a backup was taking place?  Is this not correct?

Here's what I know...

MSSQL Server Stats:
Windows 2003 Server Enterprise SP2
MSSQL 2K5 Standard SP2 (AWE enabled)
Quad-Core Xeon 2.0 GHz
4GB RAM
RAID5 4TB 10k SAS HD's

So with that info I think we can all throw out any idea that there are insufficient resources available considering this server can run NORAD while at DefCon6.

Backup Exec Server Stats:
Windows 2003 Server Enterprise SP2
Backup Exec 11d SP2 with all latest hotfixes applied
P4 2.8GHz HT
2GB RAM
80GB 10k SATA HD
Dual Quantum SATA DLT-V4 tape drives in a rackmount Quantum enclosure

No it's not terribly powerful but it's a dedicated Backup Exec Server so it doesn't need to be, not to mention it's plenty powerful enough to run Backup Exec, which hardly uses any resources on the BEX Server itself in the first place.

The way I see it, with the SQL Agent and AOFO I should be able to backup databases during the day whether they're in use or not.  That's what the SQL Agent and AOFO Agent are for, right?  So why is it when I attempt to backup a lot of databases, it effectively shuts the server down for operations?

When I try the same backup job with the same settings on three of the databases it performs fast and furiously with a 3,500 MB/min job rate and finishes successfully.

Any ideas?  Anyone else have problems like this? 

Thanks in advance, Jack

[Jack_Dorsey]

*UPDATE*

Just in case anyone is interested, I found a resolution to the issue I experienced while attempting to backup multiple databases.

This MS KB:  http://support.microsoft.com/kb/943471 has the information in it.

Microsoft recommends backing-up fewer than 35 databases at a time (per SQLServer.exe instance, of course) due to the amount of threads that the snapshot process on large numbers of databases requires.

I can't imagine I'm the first person to try to (or to NEED TO) backup over a hundred databases at once...is there a better workaround for this?  Does anyone have any tips I could use to backup all of the databases I need to, all at once (or somewhat close together)?

Right now I have a good idea of how I might do it, and this backup job is only needed for a short amount of time while we're in a transitional period.  I think I might create several smaller jobs in order to backup what I can during the day.  I still think this whoel thing stinks considering my MSSQL server I'm backing-up is PLENTY powerful enough to handle a snapshot process on all databases while still serving other threads/requests.  Shouldn't I be able to manipulate the number of threads the SQLServer.exe instance can handle?

Thanks, Jack

[Jack_Dorsey]

So I looked into our MSSQL server I'm attempting to backup all of the databases on and the 'max worker threads' was set to the default value of 'MINIMUM 128' and 'MAXIMUM 32767'

Would increasing the MAXIMUM hinder performance?  Does anyone here have experience with manipulating this value for the purpose of backing-up a large number of databases at once during normal operations?

Thanks, Jack