Recently, we've been having some kind of memory leak in SQL Server. We do have a .NET assembly running to audit our tables, but this has ran for a few years without problems, so I doubt it's the cause. It may be coincidence, but along with this memory leak, we've seen some data integrity issues.
We have a table (AccrualLedgerEntries) that has a unique index (IX_AccrualLedgerEntries) on 4 fields (AccrualTransaction, EmployeesID, AccrualDate, AccrualBanksID). Our client application is well tested and will not insert duplicates. This has all worked fine for the last few years.
My boss decided to do some integrity checking this weekend and found issues with IX_AccrualLedgerEntries. Here is a short snippet of the log:
DBCC results for 'AccrualLedgerEntries'. Msg 8951, Level 16, State 1, Line 1 Table error: table 'AccrualLedgerEntries' (ID 1637580872). Data row does not have a matching index row in the index 'IX_AccrualLedgerEntries' (ID 2). Possible missing or invalid keys for the index row matching: Msg 8955, Level 16, State 1, Line 1 Data row (1:1317579:64) identified by (AccrualLedgerEntriesID = 457276) with index values 'AccrualTransaction = 'User Deposit' and EmployeesID = 348 and AccrualDate = '2012-07-23 00:00:00.000' and AccrualBanksID = 1'.
He then tried rebuilding the index. Here is the message:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.AccrualLedgerEntries' and the index name 'IX_AccrualLedgerEntries'. The duplicate key value is (User Deposit, 348, Jul 23 2012 12:00AM, 1).
Correct me if I'm wrong, but isn't this supposed to mean that there are two rows in the AccrualLedgerEntries table with AccrualTransaction of User Deposit, EmployeeID of 348, AccrualDate of Jul 23 2012, and AccrualBankID of 1? I wrote a query that I think should show these rows, and I get no results. Here is my query:
select count(*), accrualtransaction, employeesid, accrualdate, accrualbanksid from accrualledgerentries group by accrualtransaction, employeesid, accrualdate, accrualbanksid having count(*) > 1;
As best I can tell, there are some kind of hidden rows??? Has anyone ever seen this behavior? Also, doing a count(*) from this table does not match the row count in SQL Server Management Studio.
And if anyone has any ideas on fixing the memory issue, I'm all ears. We don't know what the cause is, but it's crashing SQL Server every 3 weeks or so. We get client errors similar to this:
AppDomain EVE.dbo[runtime].4 was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.
We're thinking about setting up a new install of SQL Server 2012 and migrating the databases over. Maybe that will fix everything, but I'm skeptical. If these "hidden" rows get copied over, I think we're still going to have problems.