MSSQL Timeout Issue

Posted on 2011-10-09
Last Modified: 2012-05-12
Hi There,

I have an issue which i cannot seem to find a solution to. I have a .NET console application that runs at 11pm each night and is scheduled to run on a dedicated Win 2003 server using Scheduled Tasks.

The application makes a connection to a MSSQL database on another Win 2008 server. When I run the application by running the executable (double click on icon) the application runs perfectly. However when it is executed by the scheduled task the System.Data.SqlClient.SqlException is fired alerting me that the Timeout has expired.

If anyone has experienced this or has any ideas on what to do to fix the issue that would be great!

Question by:pawnit
    LVL 40

    Accepted Solution

    Increase the ConnectionTimeout property of the SqlConnection. You can also specify the ConnectionTimeout in the connection string if prefered.

    The default is 15 seconds and might not be sufficient when the application starts in different ways that being started by the user.
    LVL 5

    Expert Comment

    1. A value of 0 indications no limit, and should be avoided in a CommandTimeout because an attempt to execute a command will wait indefinitely.

    2. Either You can check you Connection setting and
    connection string property

     Dim conn As New SqlClient.SqlConnection("Data Source=serverName; User  ID=userName; Password=password; Connect Timeout=9999")

    You can set the select command's timeout value in SqlDataSource's Selecting

    Example :-

    protected void SqlDataSource1_Selecting(object sender,
    SqlDataSourceSelectingEventArgs e)
    e.Command.CommandTimeout = 30;

    and See this thread help you
    LVL 83

    Expert Comment

    Are you using Windows Authentication on SQL Server? Task scheduler service runs under System Account and your exe would run under the same account when launched by scheduler.

    Author Comment

    CodeCruise, I am using SQL Authentication.
    LVL 83

    Expert Comment

    How long does your query take in management studio to run? Do you have firewalls installed?
    LVL 25

    Assisted Solution

    If you run that at another time is there a problem?
    Something what could happen
    1. Jobs exactly at 11pm. Most people start 'normal' jobs at exactly the hour, also jobs that run every hour
    -> start it at 11pm15 and possible you will not be bothered anymore by other jobs running at 11pm
    2. 11pm could be an hour that is problematic because or your Sql is doing maintenance (backup, defrag, reindex .....) or your network is just slow because for example backup-traffic.

    Author Closing Comment

    Did a combination of changing the time of the script and the timeout interval and all is working well now

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now