SQL Server Performance

Timeout Expired error while restoring database

Discussion in 'General Developer Questions' started by kumarvinodsha, Jun 18, 2007.

  1. kumarvinodsha New Member

    Hi,

    I am getting the Timeout Expired error when the tool try restoring the database from the backup file which is lying on the same DB server.

    The tool calls a stored procedure which does lots of actions.The tool is developed in VB6 and transactions are handled by COM+ servers. The data access layer is installed on Com+ server.

    When i execute this StoredPrfocedure thru query analyser it works fine and took max 4-5 minutes and it doesnt give me TimeOut expired error but when this SP is executed thru Data access layer it gives me this error.

    Here are the sequence of actions:

    1) Get the list of users in the existing database by opening a cursor.
    Loop through all the users records and calls 'EXEC sp_GrantDBAccess ' to grant access
    to each user.
    2) Get the role membership of users in the existing database by opening a cursor
    Loop through all the users records and calls ' EXEC sp_AddRoleMember '
    3) RESTORE the database using the SQL given below:

    BEGIN
    SET @SQL = 'RESTORE DATABASE ' + @DatabaseName + '
    FROM DISK = ''' + @BackupFile + '''
    WITH MOVE ''StandardTest_Dat'' TO ''' + @DataPath +
    @DatabaseName + '.mdf'',
    MOVE ''StandardTest_Log'' TO ''' + @DataPath +
    @DatabaseName + '_log.ldf'''
    EXEC (@SQL)
    SET @RetVal = @@ERROR
    END
    4) Finally it restores the users old access to databaseby opening the cursor and
    executing the sql e.g.the 'EXEC sp_RevokeDBAccess'


    The tool intercats with database through Data Access layer which is installed on COM+ server.

    The connection settings which tool uses to get connected to the Database server is
    <CommandTimeout>6000</CommandTimeout>
    <ConnectionTimeout>240</ConnectionTimeout>
    <LockTimeout>600000</LockTimeout>

    I have tried increasing the connectiontimout but it didnt help. I set it to 0(Unlimited) but result the same.

    The fast response would be appreciated.

    Many Thanks
  2. Adriaan New Member

    Setting all timeouts to 0 should solve the problem. Double-check if perhaps a class is invoked that sets timeout by itself.
  3. kumarvinodsha New Member

    Thanks Adriaan! for the fast response.

    I set all the values to 0 e.g.
    <CommandTimeout>0</CommandTimeout>
    <ConnectionTimeout>0</ConnectionTimeout>

    But No luck. I ma getting the same error.

    I have double checked and the application reads the settings as given above from the config file(XML) and its not changed inside code.

    Many Thanks.
  4. satya Moderator

    Can you set a error-step to see where exactly this is timing out, I guess if the defined location on the RESTORE statment is not reachable may be the process is waiting to confirm the resources.

    Also it helps to identify the processs from SQL Server side.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. kumarvinodsha New Member

    Thanks Satya for thr responce,

    I put some break points in the application code and the TimeOut error is coming when the Data Access Layer makes a call to the stored procedure which does all the actions which i have mentioned in the thread above.

    But as i already said the error is not occuring when we run the same SP thru query analyzer(it taked 2 mins approx). I dont know how can we step thru the Stored Procedure when it is being called by the client application.

    Many Thanks.

  6. satya Moderator

Share This Page