SQL Server Performance

"-2147217871, [Microsoft][ODBC SQL Server Driver]T

Discussion in 'General DBA Questions' started by nviggiani, Apr 14, 2006.

  1. nviggiani New Member

    TimeOut expired
    I'm running sql server 2000 and I'm getting this error inconsistancy.
    I change the timeout to 0 and nothing
    any ideas?
  2. Adriaan New Member

    The error is clear enough in itself, but it seems to be generic - it doesn't say what is causing the time-outs, so you have to look at what exactly it is that you're doing.

    What kind of statement you are executing? Is it just one statement that's timing out? Are all connections to the server timing out?

    etc. etc.
  3. nviggiani New Member

    It's a insert statement into a huge table with millions of rows...
    I'm trying right now with the rowlock option
    let me know thanks
  4. joechang New Member

    i think that means there is a table lock on the table,
    look for this
    specifying a rowlock on the insert does not help if the table has a tablock on it
  5. nviggiani New Member

    there is No TabLock on that table and it happens sometimes only like 9:30AM and 4PM
    mostily
  6. satya Moderator

  7. architsureka New Member

    Hi,
    I have the following code, which is giving the timeout error. The SP being called in calculation intensive but I have tried increasing all possible values. Please suggest


    *************************************************************
    On Error GoTo errHandler

    ' Create the ADO objects
    Dim RS As ADODB.Recordset
    Dim cmd As ADODB.Command

    Set RS = New ADODB.Recordset
    Set cmd = New ADODB.Command

    'Init the ADO objects
    cmd.ActiveConnection = gsConnectionString
    cmd.CommandTimeout = 0
    cmd.CommandText = strSP
    cmd.CommandType = adCmdStoredProc

    'Create the stored proc parameters
    CreateParams cmd, varParams

    ' Execute the query for readonly
    RS.CursorLocation = adUseClient
    RS.CacheSize = 500
    RS.Open cmd, , adOpenForwardOnly, adLockReadOnly

    ' Disconnect the recordset
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
    Set RS.ActiveConnection = Nothing

    ' Return the disconnected recordset
    Set gdbrsRunSPReturnRS = RS
    Set RS = Nothing
    Exit Function

    errHandler:
    Select Case Err.Number
    Case -2147217871 'Timeout Expired - error number
    Resume
    End Select
    *************************************************************

  8. satya Moderator

    You've posted a vbscript and you must check the execution plan for that SP used in this code, try to recompile the stored procedure and make sure proper indexes are assigned to the tables that are used here.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  9. MohammedU New Member

    Run the sql trace to find long running queries/procedures and make sure you add all error events.

    Check any procedure takes more than your timeout settings and tune them. And also find the procedures called from timeout page to narrow the sql trace filter.

    Mohammed.
  10. architsureka New Member

    I am posting the stored proc. for your referece...


    ALTER Procedure dbo.usp_Opr_Productivity_Fetch
    @PUserIdvarchar(30),
    @PFromDatedatetime,
    @PToDatedatetime
    as
    Begin
    IF @PFromDate is null
    Set @PFromDate = dateadd(day,-1,getdate())

    IF @PToDate is null
    Set @PToDate = getdate()
    select count(al_id) No_of_Forms,al_formgroup FormGroup,AL_DE1Processing Processing_Flag,al_action Operator_Action,AL_OPERATORID UserID, UL_UserName FullName
    , sum(isnull(OE_ErrorCount,0)) error_Count
    from userlogin u,auditlog a left outer join operatorError o
    on o.oe_batchname = al_functionname and o.oe_operatorid =AL_OPERATORID and o.oe_de1processingflag =al_de1processing and oe_datachangedto not like 'Other'
    where al_operatorid = ul_userid and al_action in ('dataentry','hold') and
    al_createdon > @PFromDate and al_Createdon <= @PToDate
    group by al_formgroup,AL_DE1Processing,al_action,AL_OPERATORID,UL_UserName
    order by al_operatorid,al_formgroup,AL_ACTION
    End


    ****
    The execution plan shows the cost of 60% for accessing table AuditLog
  11. satya Moderator

    Try to recompile the stored procedure and make sure proper indexes are assigned to the tables that are used here.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  12. architsureka New Member

    I already have Index defined for the table.

    However I have recompiled the SP and will seek your help if the problem is still not resolved.

    Thanks
  13. satya Moderator

    ...try to reindex that specified index too in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  14. anbeshivam New Member

    quote:Originally posted by satya

    ...try to reindex that specified index too in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

    Yeah its also a fine way..
    Because i did the same thing when i got that error..

    Regards,
    Shiva
    (+91-98948-45445)
    [Looking For a Job in India]

Share This Page