Re: SQL Timeout problem

• Previous message: Steen Persson: "Re: HELP - backup file not being created but back completes "successfu"
• In reply to: Chris: "RE: SQL Timeout problem"
• Next in thread: Matt Relf: "Re: SQL Timeout problem"
• Messages sorted by: [ date ] [ thread ]

Date: Thu, 9 Dec 2004 08:49:27 -0000

Hi Chris,
Thanks for this. I have the code you have suggested poised for action, but
since your post the system is going through a long period without failure.
This of course is great but doesn't allow me to get any closer to a
permenant solution.

I'll get back once something has happened!

Thanks again,
    Matt

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:73978FFC-5B29-436E-BA28-F7131C67A95E@microsoft.com...
>
> During the period you are experiencing slowness, analyze the server for
any
> locking/blocking spids.
>
> -- will return any blocked spid, the blocking spid (blocked), the waittime
> and command of the blocked spid
> select spid, blocked, waittime, cmd from master.dbo.sysprocesses (nolock)
> where blocked <> 0
> order by blocked desc, spid
>
> -- Who and what they are doing
> sp_who2 1074
> dbcc inputbuffer(1074)
>
> -- Locks acquired by blocking spid. The first 2 params will be different
on
> your server. I captured this undocumented sp by running a trace against
SQL
> Enterprise Manager and selecting Locks/Process ID under Current Activity.
If
> you see TAB locks as a result - this is bad and is most likely a
> table/clustered index scan, look at optimizing code to use indexes
> sp_MSget_current_activity 801,4,@spid=72
>
> If you do not see any blocking spids you should look at system resources.
> How is CPU, RAM usage, is Paging occurring (need to run perm mon for
this.)
> Or Spotlight by Quest is a good tool for resource usage.
>
> Hope this helps.
>
> Chris
> MCSE MCDBA OCP
> MyDBA2000.com
>
> "Matt Relf" wrote:
>
> > Hi All,
> > I have a problem with a VB Script ASP site using a 2000 SQL server. This
set
> > up is a little complicated so I'll explain the setup before I explain my
> > problem.
> > This is a ticket booking system which runs many different sites on three
> > different web servers (2 x 2000 Server and 1 x 2003 Server Standard). At
the
> > core of this is a single SQL 2000 Server running on a 2000 Server box.
On
> > this runs three DBs; one DB is a store of core client information, the
other
> > two (A and B) are identical in structure but store ticket information
> > specific to the end vendors.
> >
> > Webserver 1 - 2003 Server
> > Runs a single ASP site which connects to the core DB.
> >
> > Webserver 2 - 2000 Server
> > Runs multiple ASP sites which all connect to DB (A).
> >
> > Webserver 3 - 2000 Server
> > Runs multiple ASP sites which all connect to DB (B).
> >
> > All sites connect to the DBs via an ODBC DSN. All DB interactions are
via
> > stored procedures.
> >
> > All stored procedures return an XML result. This may be simply the
identity
> > of a newly inserted item wrapped in tags, or may be a complicated
structure
> > of data.
> >
> > All the websites on Webservers 2 and 3 use identical source code with
> > different styling applied.
> >
> > In addition to the sites are various VB6 applications which handle
> > communication to credit card clearing services and also to the end
ticket
> > vendors. These applications also use ODBC DSN connections and XML
results.
> >
> > The Problem!
> > ---------------
> > At what would appear to be random intervals (between 3 hours and 5 days)
> > certain communications with the database fail. This initially affects
the
> > sites on Webserver 2 and the ticket vendor communication applications.
If
> > left unchecked the problem speads to Webserver 3 and also areas of the
site
> > on Webserver 1 (but not the entire site). This appears to not affect the
> > stored procedures which return very small amounts of XML (e.g. create
> > session) but anything more will eventually become an SQL Timeout error.
> >
> > We have found we can "fix" the problem by various methods; restart the
> > affected webserver, restart the database server, restart the database
> > service. The problem we have is that Nothing is logging an error. Event
> > viewer on all the servers show no events which would suggested that
> > something was broken.
> >
> > Since the problem can be resolved by restarting either the webserver of
the
> > database server or service my gut feeling is that the problem must lie
in
> > ODBC since this is the only part which "bridges" between servers. But
I've
> > checked the set up against best practice and the only thing which was
not
> > setup was connection pooling. But enabling this has had no impact on the
> > problem.
> > Some of our older stored procedures use tempory tables (later ones use
table
> > variables) so I've checked through all of these to check tables are
getting
> > correctly dropped and all is well there too.
> >
> > I'm now stuck on what to try next. With no error message (other than my
> > sites and applications timing out) to tell me what is wrong I'm stuck.
HELP!
> >
> > Many thanks in advance,
> > Matt James
> >
> >
> >
> >

• Previous message: Steen Persson: "Re: HELP - backup file not being created but back completes "successfu"
• In reply to: Chris: "RE: SQL Timeout problem"
• Next in thread: Matt Relf: "Re: SQL Timeout problem"
• Messages sorted by: [ date ] [ thread ]