We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment.
I have a very strange error in our application and I was wondering whether anyone has seen a condition that could result in the following condition:
Table structure is very simple
CREATE TABLE [dbo].[TableName]( [PKCol] [nvarchar](8) NOT NULL, [DataCol] [nvarchar](100) NOT NULL, CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED ( [PKCol] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [FK_OtherTable] FOREIGN KEY([PKCol]) REFERENCES [dbo].[OtherTable] ([PKCol]) GO ALTER TABLE [dbo].[TableName] CHECK CONSTRAINT [FK_OtherTable] GO
SELECT PKCol, DataCol FROM TableName WHERE PKCol = 'SomeString'
This query, for the last 5 years has returned the row for the tested strings. For a very short period of time (1 second in 2 weeks) this query is returning 0 rows (for a query string that exists 1 second before and 1 second after the query returns zero rows). If it helps there are a few dozen rows in this table and a search for ANY of them returns zero rows when the "condition" is occurring (as in the select query for different strings returns zero rows).
There are no inserts, deletes or updates being executed against the table. It is possible that a transaction is occurring on another table where this table is the foreign key.
The query is NOT returning any form of SQL error, it is a valid response from SQL which appears to indicate zero rows, which then affects the behaviour of the application code.
Any clues where I should start to look and/or KB/Hotfixes I should be looking at?