Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Rebuild all your indices and try that query again. –  joshudson Aug 27 '12 at 21:09
    
I'm not sure how that would fix these hidden rows, but I can give it a shot. –  Dan Aug 27 '12 at 21:33
    
As I expected, that did nothing to solve the problem. –  Dan Aug 28 '12 at 0:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.