Post reply

Simple queries intermittantly timing out.

  • November 7, 2002 at 2:20 pm

    #58140

    Hi all,

    I have a very curious problem. Simple queries on 2 of my tables time out, when run through the profiler or ASP/ADO. If use one of the columns (call it colA, which is a bittype) in a where clause as in select colA, colb from table Where colA = 1 it will occasionally timeout. Then once it has timed out once SQL starts to 'choke' and ends up with more and more connections open. After a while I can run the query successfully again.

    One of the tables it affects is a subscription table updating in a transactional replication from a remote server. I thought the replication would be a good candidate but the other table it affects is not part of the replication. Neither table is very large and the query when run successfully executes blindingly fast. Thw site that runs on top of this database is not hit more than 100-500 times an hour.

    Anyone have any ideas about tracking down this error..?

    Thanks for any help. Rolf

  • November 7, 2002 at 2:41 pm

    #440746

    Does it happen in EM or QA. If not then can you post an example of the code behind the ASP page? Also, have you looked at the execution plan to see if anything odd is happening? Finally, what do you mean when run thru Profiler, are you talking about a replaying a trace of a bad run?

  • November 7, 2002 at 3:29 pm

    #440765

    Oringinally the query was running through an ado recordset object, but whilst I was trying to trap it down I ran the same query in EM and QA and it bombed on both.

    I'm stuck..!

  • November 7, 2002 at 3:43 pm

    #440768

    Have you try DBCC CHECKTABLE, could be damaged in some way. More info available in SQL BOL.

  • November 7, 2002 at 3:46 pm

    #440770

    Just a quick check. When you brought up Profiler to see what was going on, were the connections being made using TCP/IP or Named Pipes. We saw time out issues with Named Pipes. This is probably not the issue, but it's one of the first checks I do.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • November 7, 2002 at 4:09 pm

    #440780

    Tried DBCC;

    There are 1120 rows in 43 pages for object 'redcom.products'

    DBCC execution complete.

    I have since dropped the subscription and then the table and then recreated and reinitialised the replication. I noticed that the bulk insert commands took an age to complete on this fairly small table (~8 minutes!).

    Rolf

  • November 7, 2002 at 4:28 pm

    #440783

    The table is now refusing to open in any way..I cant get hte schema, I cant run a dbbc checktble on it...Tried dropping and renaming. Definately something criticalyl wrong with this table!

    Rolf

  • November 7, 2002 at 4:37 pm

    #440785

    Maybe the databse and not the tabl;e, try DBCC CHECKDB as well.

  • November 7, 2002 at 4:44 pm

    #440787

    cant do that, dont have the neccessary permissions on the remote server to run that check.

    Just tried exporting the table from my DB here to there and it crashed out doing that as well but I cant find any problems with the table at this end!

    Crazy problem this!

    Thanks for all your help

  • November 7, 2002 at 5:07 pm

    #440788

    Yeah, you obviously have an unstable table or db going on there. I would make sure you have a copy of the data and be prepared to have to drop the databse then recreate to correct.

  • November 7, 2002 at 5:09 pm

    #440789

    data isnt a problem as its all replicated stuff except for one or two tables..no DTS or jobs or SPs.

    I will get the sysadmin to drop and recreate the table tomorrow.

    Thanks

    Rolf

  • November 7, 2002 at 5:25 pm

    #440793

    Let us know if anything changes.

  • November 10, 2002 at 1:28 pm

    #440910

    Hi,

    Well I stopped all the jobs that update the local database from source data. Deleted the remote tables then exported them form the local to the remote DB. Then recreated replication without any snapshot and so far so good. Have no idea what caused the problem though!

    Thanks for all the help.

    Rolf.

  • November 11, 2002 at 8:13 am

    #440955

    OK problem is back!

    Dont know if this is related but when I watch the replication every now and then it comes up with a 3 transaction change that involves over 5000 commands and this seems to lock it all up for a bit. Cant imagine where it is getting 5000 commands from as all it is doing is updated stock figures and adding the occasional new product.

    During the transactional update the web site timesout trying to get READ only access to those tables.

    Thanks

    Rolf

  • November 11, 2002 at 9:39 am

    #440961

    Another update.

    Well it definately appears to be the subscription that is causing the problems. If I stop synchronising the subscriber the timeouts stop. I cannot trace down a list of the commands the log reader agent is using so I cant at the minute check to see what its doing.

    Could this be an error at the subscriber or is it most likely an error at the distributor..?

    Thanks

    Rolf

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply