permanent connections?

jrandall · March 14, 2008 12:16PM

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.

petey · March 14, 2008 1:50PM

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.

jrandall · March 14, 2008 1:55PM

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.

petey · March 16, 2008 8:46PM

Can you identify the application(s) that created the connections e.g. using sp_who2?

Thanks.

jrandall · March 16, 2008 9:42PM

The activity monitor indicates SQL Backup (actually the SQL Backup executable). What other application could possibly be running it?

petey · March 16, 2008 10:11PM

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.

jrandall · March 17, 2008 7:45AM

SQL Server 2005 Activity Monitor.

permanent connections?

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk