shared server issue?

[inline_four]

We've been running a database on a shared server. The traffic on our
site has been picking up steadily and it seems as though we're running
into sporadic downtime on the database. Sometimes when we do large
data imports, the transaction log fills up and the schema somehow
fails to truncate it when asked to back it up. So we run the explicit
truncate statement and that usually seems to fix the problem. A few
days ago a single stored procedure started to block, but inexplicably
only when called by our .NET server. When called by hand from
Enterprise Manager, it executed fine. I was at my wits end when I
just recreated it with the same exact code and it magically started
working again. What gives? Is this at all indicative of a shared
database, or are these known SQL Server issues?

[Erland Sommarskog]

(in*********@ya hoo.com) writes:

We've been running a database on a shared server. The traffic on our
site has been picking up steadily and it seems as though we're running
into sporadic downtime on the database. Sometimes when we do large
data imports, the transaction log fills up and the schema somehow
fails to truncate it when asked to back it up. So we run the explicit
truncate statement and that usually seems to fix the problem. A few
days ago a single stored procedure started to block, but inexplicably
only when called by our .NET server. When called by hand from
Enterprise Manager, it executed fine. I was at my wits end when I
just recreated it with the same exact code and it magically started
working again. What gives? Is this at all indicative of a shared
database, or are these known SQL Server issues?

There is not much information in your post to determine what the problem
might be.

When you say "shared server", I suppose you mean that here are other
databases on the same server, used by other applications. What kind
of server is this? Is in this an in-house server at some corporation,
or do you rent space at a service provider? Not that the answers to
these questions are pertinent to the problem, but it could your
affect your possibilities to diagnose the problems.

When you say that the log faile to truncate, what commands do you use?
And which recovery mode are you using?

One reason for the log not truncating could be that there is an open
transaction in the database. SQL Server never truncates the long
past the oldest open transaction.

Indeed, an open transaction could also be the answer to the blocking
problem. But you need to diagnose the blocking situations better, to
find out what is blocking. A simple variant is to use sp_who and look
at the Blk column. A non-zero value in the column for a spid, means
that that spid is blocked by the spid in the column. Then you can
use DBCC INPUTBUFFER on that blocking spid to get see what it is up to.

A more elaborate tool is aba_lockinfo, available on my website,
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html.

The likelyhood that you run into problems because of other applications
on the same server, but in other databases, is not particularly big.
(Unless you are running SQL 6.5.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[inline_four]

Thank you for your response. I will look into the techniques you
mention. Like you said, I doubt the answers to your questions will
shed much light, but maybe they will, so here it goes:

There is not much information in your post to determine what the problem
might be.
Unfortunately we don't have a whole lot of information as to what's
actually going on in the database ourselves when problems arise. The
best I can do is see if the server is responding to pings, if we are
connecting to the schema properly, if certain stored procedures return
incorrect results or block given particular parameters. Typically our
woes have to do with our web app not connecting to the schema or the
transaction log filling up and not truncating.
When you say "shared server", I suppose you mean that here are other
databases on the same server, used by other applications. What kind
of server is this? Is in this an in-house server at some corporation,
or do you rent space at a service provider?
Yes, we are one schema out of many set up on one big SQL Server 8
machine. Our .NET runs in IIS on a Win 2000 server and connects to
the database to run the site. Nothing fancy here. Both machines are
hosted by a hosting company. our IIS server is dedicated and the
database server, as I mentioned, is shared.
When you say that the log faile to truncate, what commands do you use?
And which recovery mode are you using?

Typically our transaction log fills up when we do large data imports.
When that happens, I use Enterprise Manager to back up its transaction
log with the idea that it normally truncates it after the back-up is
successful. Sometimes it backs up, but the transaction log is not
truncated. I believe what you said about an open transaction makes a
lot of sense. Next time this happens, I will look at what's open.
Our band-aid solution to force the log to truncate has been to run
DBCC SHRINKFILE command, which normally does the trick.

[John Bell]

Hi

To add to Erlands response

When you call the procedure though your application, there is probably a
transaction already in progress, this will not be the case when you call it
from Query Analyser. You may want to look at using profiler to see when
transactions are started and finished.
John

<in*********@ya hoo.com> wrote in message
news:15******** *************** ***@posting.goo gle.com...

Thank you for your response. I will look into the techniques you
mention. Like you said, I doubt the answers to your questions will
shed much light, but maybe they will, so here it goes:

There is not much information in your post to determine what the problem
might be.

Unfortunately we don't have a whole lot of information as to what's
actually going on in the database ourselves when problems arise. The
best I can do is see if the server is responding to pings, if we are
connecting to the schema properly, if certain stored procedures return
incorrect results or block given particular parameters. Typically our
woes have to do with our web app not connecting to the schema or the
transaction log filling up and not truncating.

When you say "shared server", I suppose you mean that here are other
databases on the same server, used by other applications. What kind
of server is this? Is in this an in-house server at some corporation,
or do you rent space at a service provider?

Yes, we are one schema out of many set up on one big SQL Server 8
machine. Our .NET runs in IIS on a Win 2000 server and connects to
the database to run the site. Nothing fancy here. Both machines are
hosted by a hosting company. our IIS server is dedicated and the
database server, as I mentioned, is shared.

When you say that the log faile to truncate, what commands do you use?
And which recovery mode are you using?

Typically our transaction log fills up when we do large data imports.
When that happens, I use Enterprise Manager to back up its transaction
log with the idea that it normally truncates it after the back-up is
successful. Sometimes it backs up, but the transaction log is not
truncated. I believe what you said about an open transaction makes a
lot of sense. Next time this happens, I will look at what's open.
Our band-aid solution to force the log to truncate has been to run
DBCC SHRINKFILE command, which normally does the trick.

[Erland Sommarskog]

(in*********@ya hoo.com) writes:

Yes, we are one schema out of many set up on one big SQL Server 8
machine. Our .NET runs in IIS on a Win 2000 server and connects to
the database to run the site. Nothing fancy here. Both machines are
hosted by a hosting company. our IIS server is dedicated and the
database server, as I mentioned, is shared.

You may need to work with the hosting company to resolve these problems.
I guess that have dbo privieges in your database, but outside it you
are just a plain user. You can still use sp_who and sp_who2 to examine
blocking. You might even be able to install aba_lockinfo in your database
and run it, but the hosting company may not appreciate that. In any case,
you will see a lot of white noise from the other applications running
on the server.

What could be the root to all evil is a stored procedure that times
out for some reason. This timeout is defined in the client layer, not
in SQL Server itself. Say that this stored procedure starts a transaction.
This transaction will not roll back when the time-out sets in, but
you need to have code to handle this.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp