Job execution times

Question

Job execution times

Brian Brown-204626

Hall of Fame

Points: 3211
More actions
January 3, 2008 at 8:38 am

#185334

We have recently upgraded several servers from SQL Server 2000 to 2005. We have noticed an issue with job execution. Jobs created as part of maintenance plans in 2005 are running much longer than jobs created with maintenance plans in 2000, or jobs created outside of maintenance plans.
For example, we cycle the SQL Server error logs weekly, using a job to execute the sp_cycle_errorlog procedure. If this job is just a SQL Server job, is runs in less than a 10 seconds. If we use a maintenance plan "Execute T-SQL Statement" task to create a job, the job runs for longer than a minute.
Also, in checking the event log and the job logs for our database backups, we have found that even after the 'SSIS package started" message, everything just sits for ten to twenty minutes before logging any backup have occurred.
I don't know if this is also part of this issue, but we occasionally recieve the message
"Sqlcmd: Error: Microsoft SQL Native Client : Unable to complete login process due to delay in opening server connection." when executing jobs from our scheduling software. Could this delay be due to the same issues as our other errors?
Are these delays in job execution normal for 2005, or is there a patch we can apply? I have searched Microsoft support and could not find anything about this issue.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

I think you have something else going on. The delays are not normal.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

I found a blog entry over at MSDN that talks about this:

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

If you scroll down to Message #10:

Reason:

1) There are spaces after Instance name in the connection string eg. osql /S” \Instance “ /E, to resolve this, you need to remove the trailing space.

2) Connect through 127.0.01.

3) Remote connection and WINS was disabled on the client machine and you connect using FQDN as server name. To resolve this, One way, turn on “File and Printer Sharing” and explicitly use name pipe protocol. Another is enlarge the connect timeout to around 30 secondes.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Brian Brown-204626 Hall of Fame Points: 3211 More actions · Answer 3

I have seen this blog entry. Even though we do execute some jobs from a scheduling package using sqlcmd, I am seeing this problem even when I execute the jobs directly on the server.