Sql-server – SQL Server returning empty result sets (to busy?)

sql serversql-server-2008sql-server-2008-r2windows-server-2008

My apologies from the start, I don't have much information to go on and I am just looking for some help debugging my issue.

I have 5 sites running on a Windows Server Web Edition 2008 with SQL Server 2008 RC2 machine. Periodically on all sites (not all at the same time) I will get some errors in my ASP.NET 2 website(s) saying Index was outside the bounds of the array. on an SQL cursor. This is happening in places where data is definitely present and if it wasn't I would get a lot more errors. It's as if the SQL server can't return result for some reason.

I have no idea how to start debugging this, I don't seem to get any timeout errors. My questions would be:

  1. How can I tell if the SQL server is under to much load? Can I enable something that allows me to track this?
  2. If I have a date and time, am I able to check some logs on SQL Server to show more information? If so, where?

Sorry about the vague description; I don't have much more to go on at the moment.

Edit to the answers so far:

Most of the time, the same code will work without a hitch. I managed to replicate this today by creating an endless loop and perform an SQL query each time, I then tried to load a page on the same website and it brought back this error. My point is that cursors are being returned empty when there should be no possible way for them to be.

Edit2
Ok, maybe I wasn't explaining properly. I understand the out of bounds error is down to coding. What I am saying is that is happens randomly on the same code that works 99.9% of the time. Yes the code raises the error BUT it shouldn't have an empty result set in the first place. Here is the full stack trace (again, this code works 99.9% of the time):

(I am using Delphi.NET so this is where the Borland part has come from) – Please also note that the error is occurring when it's clearing down it's own list internally …

Borland.Vcl.TDBBufferList.FreeHGlobal(IntPtr Ptr) at 
Borland.Vcl.TCustomADODataSet.FreeRecordBuffer(IntPtr& Buffer) at 
Borland.Vcl.TDataSet.SetBufListSize(Int32 Value) at 
Borland.Vcl.TDataSet.CloseCursor() at 
Borland.Vcl.TDataSet.SetActive(Boolean Value) at 
Borland.Vcl.TDataSet.Close() at 
WebCommon.TStockItemIntf.@13$Initialise$GetPLPrice(TStockItemIntf Self, String CCode, String SkCode, String PLStr) at 
WebCommon.TStockItemIntf.@23$Initialise$FinalPrice(TStockItemIntf Self, $Unnamed205& $frame_TStockItemIntf.Initialise, TKosCompany Company, TKosStock SkItem, TStockPriceCalcType PLCalcOption) at 
WebCommon.TStockItemIntf.Initialise(TKosStock SkItem, STOCK_ITEM SkCatItem, TAssignStockOptions AssignOptions, HttpSessionState Session, String DefaultPriceList) at 
WebCommon.TKosDropInProducerP.@68$AssignStockItem$AssignStockObjLst(TKosDropInProducerP Self, $Unnamed206& $frame_TKosDropInProducerP.AssignStockItem, TKosStock SkItem, String DefaultPriceList) at 
WebCommon.TKosDropInProducerP.AssignStockItem(TStringList StockCodeList, CATEGORY Category, HttpSessionState Session, TAssignStockOptions AssignOptions) at 
RecentlyViewedShortList.TRecentlyViewedShortList.Page_Load(Object sender, EventArgs e) at 
System.Web.UI.Control.OnLoad(EventArgs e) at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Best Answer

As Martin pointed out, it's a .Net error.

You should check your code, it could be anything but I'll give an example of how this can happen in pseudo-code:

Item[] myItems = new Item[sql.queryshot("select count(*) form myTable")];
int counter=0;

Cursor MyCursor = GetData();
foreach(Data data in MyCursor){
  myItems[counter] = new Item(data);
  counter++;
}

If someone inserted a record between the initialization of the array Item[] and the GetData() method, you will see an Index out of bounds exception because you will pass the end of the array. This will not happen frequently though since there is very little time between the two operations.

If it were an overloaded server, you would certainly get other errors, not this one.

Related Topic