Having restored a client's SQL 2014 EE DB onto our Developer Edition instance, without issue, then running a DBCC CHECKDB that found zero errors, and a clustered columnstore index alleging over 1Bn rows, a simple SELECT TOP 1 FROM TheBaseTable returns no data - having taken 6m 38s to run, using 3845 reads, 155 physical reads, and over 400K ms CPU, so it gives the appearance of doing something.

Oddly, compatibility mode is at 2008 upon restore. Changed it to 120 and page verification to CHECKSUM.

Fixed the SQL Login SID (only 1 orphan). The login has db_owner, db_accessadmin, and db_securityadmin role membership, so it doesn't appear to lack privileges, but belts-and-braces-style it now also has db_datareader and db_datawriter memberships.

Ran sp_updatestats because the DMVs show rows but SELECT statements return zero.

Is it possible that the client's having set the compatibility down to 2008 with there being a CCSI in place has messed up the metadata?

Has anyone heard of anything like this, please?

Any ideas on how to see the data?

Here's the table - I see nothing fishy...

CREATE TABLE dbo.tbl_HistoryByType(
    InventoryID int NOT NULL,
    SalesTypeID int NOT NULL,
    ReturnFlag bit NOT NULL,
    SalesDate datetime NOT NULL,
    SeasonID int NULL,
    SalesQuantity float NOT NULL,
    TotalRevenue float NOT NULL,
    TotalCost float NOT NULL,
    TotalOriginalRetail int NULL

Hope someone can lend a hand as I've never seen anything like this in over 10 years with SQL Server...!

I have absolutely no idea why compatibility would be set to 2008, especially when we are to evaluate the DB code and performance.

Microsoft SQL Server 2014 - 12.0.4416.0 (X64)
Jun 11 2015 19:18:41
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Sadly, the original system is behind iron bars, so to speak. I'll inquire about any kind of select-only access. Strangely enough, I ran an ALTER TABLE...REBUILD and after DBCC CHECKTABLE scored zero rows. 1Bn+ "disappeared" Will look at applying the latest CU, too - this server is heavily shared, so will have to schedule it.

  • Heh, ok, to eliminate any environmental or other issues, have you considered trying another restore on a server that is less heavily shared? Sounds like there are a lot of things going on here that could be simplified... – Aaron Bertrand Jan 27 '16 at 2:42
  • That's a possibility - we have another 2014, but that's being pounded by some analytics. I may resort to my laptop as it has enough space and the correct version. Thanks for the suggestion. – SAinCA Jan 27 '16 at 2:52
  • 2
    @SAinCA it sounds like you got a statistics only copy of the database. The index rebuild updated stats and that's why the rows are gone now. How big was the backup file you got? – Erik Darling Jan 27 '16 at 3:50
  • And that's our concern - the data file is 200GB and Log 140GB, but the unzipped .BAK.is only 20GB. Previewing data file shrink shows 45% free. It's possible a 5:1 expansion is realistic if the backup was compressed at creation. Thanks for joining the thread, Erik. – SAinCA Jan 27 '16 at 18:15
  • 1
    DB is unreadable at the source site, too. They're calling Premium Support. – SAinCA Feb 3 '16 at 22:26

As there's no information available about the cause and cure, sadly this will be a closed thread with no resolution.

Sadness all around.

| improve this answer | |
  • There was a bug in SQL 2008 which the ghost records were actually not cleaned up. After applying the fix and doing a select * or something else to get it into memory it would then run the ghost cleanup wizard on it. Have you been able to do a DBCC PAGE or confirm the ghost record count? – Ali Razeghi Jul 28 '16 at 20:03
  • @AliRazeghi the question is tagged sql server 2014 ! – Kin Shah Jul 28 '16 at 20:13
  • @Kin nvm he said it's compatibility mode 2008. I'd still be curious in a DBCC PAGE output though. – Ali Razeghi Jul 28 '16 at 20:14
  • @All: DB no longer accessible in its broken state. Customer unwilling to share DB with us. No word at all from them on what MS may have done to fix this. Hence "sadness all around" because we cannot learn anything from this. The source and restored-to SQL instances were both 2014, BTW. The question on 2008(R2) compatibility will also go unanswered. – SAinCA Jul 29 '16 at 23:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.