(in*********@yahoo.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