backup jobs failing - *LOTS* of DB connections lying around
rrogersiii
Posts: 6
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.
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.
Robert Rogers
Remedy Interactive, Inc.
Remedy Interactive, Inc.
Comments
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
- 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.
Remedy Interactive, Inc.
- 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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
Remedy Interactive, Inc.
- 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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
....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
Remedy Interactive, Inc.
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.
Remedy Interactive, Inc.
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
Red Gate Software Ltd