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

rrogersiiirrogersiii Posts: 6
edited April 20, 2006 3:34PM in SQL Backup Previous Versions
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.
Robert Rogers
Remedy Interactive, Inc.

Comments

  • peteypetey Posts: 2,358 New member
    If you run sp_who2, what is the value of ProgramName column for the 200+ connections?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
    Robert Rogers
    Remedy Interactive, Inc.
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • - 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
    Robert Rogers
    Remedy Interactive, Inc.
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • - 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
    Robert Rogers
    Remedy Interactive, Inc.
  • 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.
    Robert Rogers
    Remedy Interactive, Inc.
  • 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
    - Neil Davidson
    Red Gate Software Ltd
Sign In or Register to comment.