| Groups | Blog | Home |
sql server (alternate) : shared server issue?
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
(inline_four@yahoo.com) writes:
[quoted text, click to view] > 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, sommar@algonet.se Books Online for SQL Server SP3 at
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: [quoted text, click to view] > 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. [quoted text, click to view] > 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. [quoted text, click to view] > 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
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 [quoted text, click to view] <inline_four@yahoo.com> wrote in message news:1555d69f.0309132245.483b7dbb@posting.google.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.
(inline_four@yahoo.com) writes:
[quoted text, click to view] > 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, sommar@algonet.se Books Online for SQL Server SP3 at
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |