locked
Connection Timeout Expire after set connection.ConnectTimeout = 0 RRS feed

  • Question

  • Hi,

    I have database with 22.5 GB size. My database is filestream enable database. My database mdf and ldf file size is 113 MB but my file stream data folder is 22 gb size.  I take backup using following code and I get connection time out error..

     

     Dim connection As New ServerConnection(sqlInstanceName, "sa", pwd)
    Dim srv As Server
    srv = New Server(connection)
    connection.ConnectTimeout = 0
    Dim db As Database
    db = srv.Databases(DBName)
    Dim recoverymod As Integer
    recoverymod = db.DatabaseOptions.RecoveryModel
    bk.Action = BackupActionType.Database
    bk.Database = DBName
    Dim str As String
    str = full_path & ".bak"
    Dim bdi As BackupDeviceItem
    bdi = New BackupDeviceItem(str, DeviceType.File)
    bk.Devices.Add(bdi)
    bk.Incremental = False
    bk.SqlBackup(srv)
    bk.Devices.Remove(bdi)<br/>

     

    But after executing this it gives me error as following

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
    The backup or restore was aborted.
    Processed 65328 pages for database 'abc', file 'abc' on file 1.
    10 percent processed.
    20 percent processed.
    30 percent processed.
    40 percent processed.
    50 percent processed.
       at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
       at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
       at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)

     

    It works fine on small size database.. But not work on this 13 GB database.. I continuously see the backup file size. It created up to 8 GB but when after 8 GB it vanished. I have file stream enable database.

    What is the problem after set connection timeout to 0?

     



    • Moved by Bob Beauchemin Saturday, September 24, 2011 5:59 PM Moved to a more appropriate forum (From:.NET Framework inside SQL Server)
    • Edited by ankitshah Monday, September 26, 2011 9:47 AM
    Saturday, September 24, 2011 5:41 AM

Answers

  • Hi,

    I got the solution

    srv.ConnectionContext.StatementTimeout = 0

    It works for me..!!:)

    • Marked as answer by ankitshah Tuesday, September 27, 2011 1:02 PM
    Tuesday, September 27, 2011 1:02 PM

All replies

  • I think you need to set the connect timeout property in the connection string.

    Try this:

    Dim csb as SqlConnectionStringBuilder

    csb = new SqlConnectionStringBuilder()

    csb.Server = "specify your servername and instance here"

    csb.ConnectTimeout = 0

    csb.IntegratedSecurity=true

    Dim srv As Server
                srv = New Server(csb.ConnectionString)


    Matt
    • Marked as answer by Matt Neerincx [MSFT] Monday, September 26, 2011 4:02 PM
    • Unmarked as answer by ankitshah Tuesday, September 27, 2011 10:05 AM
    Monday, September 26, 2011 4:02 PM
  • Hi Matt,

    I still get the same error..

    and as per your suggestion

    csb.Server

    gives me an error. So I tried as follwoing

     

    Dim csb As SqlConnectionStringBuilder
    csb = New SqlConnectionStringBuilder()
    
    csb.ConnectionString = "Data Source=" & sqlInstanceName & ";User ID=& strUserName &;password=" & DVsa
    csb.ConnectTimeout = 0
    
    Dim connection As New ServerConnection(csb.ConnectionString)
                srv = New Server(connection)
                db = srv.Databases(DBName)
                recoverymod = db.DatabaseOptions.RecoveryModel
                bk.Action = BackupActionType.Database
                bk.Database = DBName
                bdi = New BackupDeviceItem(str, DeviceType.File)
                bk.Devices.Add(bdi)
                bk.Incremental = False
                bk.SqlBackup(srv)
                bk.Devices.Remove(bdi)

    This code is still give me an error of time out. It's now headache for me.. I unmark your answer because it is not solve my problem

    Regards,

    Ankit

     

    Tuesday, September 27, 2011 10:10 AM
  • Hi,

    I got the solution

    srv.ConnectionContext.StatementTimeout = 0

    It works for me..!!:)

    • Marked as answer by ankitshah Tuesday, September 27, 2011 1:02 PM
    Tuesday, September 27, 2011 1:02 PM