backup jobs failing - *LOTS* of DB connections lying around

rrogersiii

After two weeks of running SQL Backup 4.1, our backup jobs recently started failing.

Log backups run every 15 minutes - full backups of our production database run 1x/day - old backup files are deleted after 5 days.

Since starting to run log backups every 15 mins, we've seen a tremendous increase in the amount of database connections that seem to just be lying around. There used to be, say 15 connections around during normal operation of our system (when not running SQL backup) - now, there are over 200, all with a status of "AWAITING COMMAND" ... and the connections all suspiciously seem to have been created at 15 minute intervals ...

---

Here's the message I'm seeing from our full backups:


SQL Backup log file
4/9/2006 8:00:04 PM: Backing up remedy_v3 (full database) to:
x:\FULL_remedy_v3_20060409_200004.sqb

4/9/2006 8:00:04 PM: BACKUP DATABASE [remedy_v3] TO DISK = 'x:\FULL_remedy_v3_20060409_200004.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES = 5, COMPRESSION = 3, THREADS = 1

4/9/2006 8:00:04 PM: Thread 0 error:
Process terminated unexpectedly. Error code: -2139684860

4/9/2006 8:00:04 PM: Server: Msg 3013
BACKUP DATABASE is terminating abnormally.

---

Here's what I'm seeing from our log backups:
SQL Backup log file
4/10/2006 10:00:00 AM: Backing up remedy_v3 (transaction log) to:
R:\LOG_remedy_v3_20060410_100000.sqb

4/10/2006 10:00:00 AM: BACKUP LOG [remedy_v3] TO DISK = 'R:\LOG_remedy_v3_20060410_100000.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES = 3, COMPRESSION = 1, THREADS = 1

4/10/2006 10:00:00 AM: Thread 0 error:
Process terminated unexpectedly. Error code: -2139684860

4/10/2006 10:00:00 AM: Server: Msg 3013
BACKUP LOG is terminating abnormally.

---

petey

If you run sp_who2, what is the value of ProgramName column for the 200+ connections?

rrogersiii

they're all listed as SQLDMO_### - for example:
- SQLDMO_489
- SQLDMO_490
- SQLDMO_491
etc...

Any ideas about why the jobs are failing, in the first place? That's our primary concern, at this point.

petey

I would like to reproduce your error in our lab. Could you pls provide me the following details of your server where you are experiencing the error:

- is server a cluster node
- amount of memory on server, any memory switches used e.g. /3GB etc
- operating system version and service pack level (32-bit, 64-bit, Itanium?)
- SQL Server version and service pack level (32-bit, 64-bit, Itanium?)
- MDAC version (see http://support.microsoft.com/?kbid=301202)
- average size of trx log file
- scheduling agent (SQL Server Agent or others)
- aside from the backup script, are there any pre or post backup tasks performed?
- are the backups made to a local or remote drive

If you prefer, you can send the details to me via email.

Thanks.

rrogersiii

- is server a cluster node
NO

- amount of memory on server, any memory switches used e.g. /3GB etc
4GB RAM - no spectial switches

- operating system version and service pack level (32-bit, 64-bit, Itanium?)
win2k server ("normal" version - not enterprise, etc.), 32-bit

- SQL Server version and service pack level (32-bit, 64-bit, Itanium?)
32bit
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


- MDAC version (see http://support.microsoft.com/?kbid=301202)
MDAC 2.8 SP1 on Windows XP SP2

- average size of trx log file
avg size of SQL Backup files (after compression): 0.5-1MB (every 15 minutes)

- scheduling agent (SQL Server Agent or others)
(just SQL Server Agent)

- aside from the backup script, are there any pre or post backup tasks performed?
delete backup files older than 5 days

- are the backups made to a local or remote drive
files are actualy written to SAN, which appears as local drive to OS

petey

Thanks for the details. Some additional questions:

- is this a default or named instance
- is the SQL Backup Agent logging in to SQL Server using Windows authentication, or SQL Server authentication
- does the owner of the SQL Server Agent backup job have the SQL Server sysadmin fixed server role

Thanks.

rrogersiii

- is this a default or named instance
....default - the SQL Server instance is not named

- is the SQL Backup Agent logging in to SQL Server using Windows authentication, or SQL Server authentication
....can't remember how we set it up - is there any where I can look?

- does the owner of the SQL Server Agent backup job have the SQL Server sysadmin fixed server role
.....the owner for the both SQL Server Agent jobs that SQL Backup created is the administrator account for the local Win2K server (not SQL Server) ... in turn, SQL Server lists BUILTIN/Administrators as a login member of the fixed sysadmin role, so... yes: I'd say that the owner of the jobs have the sysadmin fixed server role

rrogersiii

Some follow-up information on this thread....

If we stop & start SQL Server, both of our SQL Backup jobs start working again ... but then the problem restarts after 2-3 days, jobs fail, and backups are not run.

Neil Davidson

Robert,

I'm fairly sure we've fixed this problem now. I've asked one of our support guys to get in touch with you with an updated extended stored procedure dll.

- Neil