Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Unanswered: weird sql2008 problem (timeouts)

    hi
    recently we transferred our db from sql 2003 to 2008, using backup and restore
    the 2008 is now on new server with windows 2008
    our sites are still on ii6 server 2003 on another machine
    for few weeks everything worked fine but the last days we get timeout errors every couple of hours
    [Microsoft][ODBC SQL Server Driver]Timeout expired

    the error contained a table name, same every time, i checked the table and it looked like it doesnt have a key , i fixed that but after a while same error and the key was not set again , after few times , i just created a new table with same colms and change the code to use it

    now we get the same error but with different table name, it looks like the same thing happens and the previous table is ok now

    the timeout only happens when trying to use recordset update
    until i fix the key of the table , any update command on this db fail no matter what table it tries to update

    this is very urgent, any ideas?
    thanks

  2. #2
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    Probably a good idea to start with making sure the compatibility level is correct for the database(s) you migrated from 2003, also run dbcc updateusage once, correct syntax can be found here: DBCC UPDATEUSAGE (Transact-SQL)

    Also not a bad idea to update statistics, otherwise wait for a better response from one of the more knowledgeable people here

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    also, update statistics.
    It also may be Parameter Sniffing.


    The “fix” for parameter sniffing is to create local variables within the stored procedure that match the parameters being passed in. So for example,

    Create Procedure myProc @name varchar(30), @age int

    As

    Declare @name_local varchar(30), @age_local int

    Set @name_local = @name

    Set @age_local = @age

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •