permanent connections?

jrandalljrandall Posts: 35
edited March 17, 2008 7:45AM in SQL Backup Previous Versions
Does SQL Backup always maintain active connections to the server, even when it is not performing any activity? I've noticed an open, idle connection for each thread specified in the backup command running from a job.

Comments

  • peteypetey Posts: 2,358 New member
    Could you pls clarify? If you were to run a backup with 10 threads, do you mean that SQL Backup creates 10 connections to SQL Server?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Yes, that's what it looks like to me. When I open the Activity Monitor there are the same number of Process IDs as the number of threads I specified for the SQL Backup task. When SQL Backup is not actively executing a backup, the processes have a status of sleeping.
  • peteypetey Posts: 2,358 New member
    Can you identify the application(s) that created the connections e.g. using sp_who2?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • The activity monitor indicates SQL Backup (actually the SQL Backup executable). What other application could possibly be running it?
  • peteypetey Posts: 2,358 New member
    The SQL Backup Agent service should not hold any open connections to SQL Backup if it is not active.

    If you are using sp_who to monitor the activity, there should only be 1 to 2 connections (indicated by unique SPID values) created by SQL Backup. There will indeed be a large number of threads matching the number of backup threads you selected, and they are linked to the connection creating the backup, and are not unique connections.

    Could you please use Profiler to trace the sequence of events that SQL Backup is doing durng the entire backup, and match it up with the times when you think nothing is happening?

    When you mention Activity Monitor, which application are you referring to?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • SQL Server 2005 Activity Monitor.
Sign In or Register to comment.