Tables are empty but catalog views show that they have rows

Question

Post reply

Tables are empty but catalog views show that they have rows

SQL Guy 1

SSCoach

Points: 15817

June 1, 2021 at 10:19 pm

#3890761

Hi all,

It appears that all our tables are empty for some database in a lower environment, but sys.dm_db_partition_stats and sys.partitions show otherwise. I used this query:

select  t.name,
        i.index_id,
        p.partition_id,
        in_row_mb = d.in_row_data_page_count/128,
        lob_mb = d.lob_used_page_count/128,
        used_mb = d.used_page_count/128,
        p.rows,
        d.row_count
from    sys.tables t
        join sys.indexes i on t.object_id = i.object_id
        join sys.partitions p on i.object_id = p.object_id
                            and i.index_id = p.index_id
        join sys.dm_db_partition_stats d on p.partition_id = d.partition_id

p.rows and d.row_count show the same number for each table, but if I run select * from table_name it shows nothing in results.

I applied dbcc updateusage (my_db_name) but it only reset page counts to zero. What else I can do? Or what is wrong in my query?

Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100949 More actions · Answer 1

Hmm, do you have tables under different schema names? Is it possible that dbo.same_table_name is empty, but some_other_schema.same_table_name has rows?

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

SQL Guy 1 SSCoach Points: 15817 More actions · Answer 2

All tables are in dbo schema. None of tables that I've checked so far with select count(*) have records.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

Shot in the dark... have you tried doing a manual CHECKPOINT in the database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Guy 1 SSCoach Points: 15817 More actions · Answer 4

Ran it now. Did not help. Also tried DBCC checkdb. Did not help either. In an output message it displayed There are 0 rows in 0 pages for object <table_name> for all tables. That proves that all tables are indeed empty.

I also tried sys.sysindexes in my record count scripting. It's outdated, but still shows same numbers as sys.partitions.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 5

SQL Guy 1 wrote:

Ran it now. Did not help. Also tried DBCC checkdb. Did not help either. In an output message it displayed There are 0 rows in 0 pages for object <table_name> for all tables. That proves that all tables are indeed empty.
I also tried sys.sysindexes in my record count scripting. It's outdated, but still shows same numbers as sys.partitions.

Doesn't seem possible. Are you sure you're looking at the same database on the same machine from all these different angles? I've seen people do things like swear they're looking at a prod box when they actually looking at the dev box and do so for days even though others tell them they need to be sure because of the impossibilities.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ken McKelvey SSCoach Points: 18768 More actions · Answer 6

Are there any Triggers, especially encrypted triggers, on the tables where select produces no rows?

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 7

One more shot in the dark. I'm actually following a thread of something kind of similar on another thread.

Since the tables are supposed to be empty and rowless, try doing a TRUNCATE on the table(s) and see what happens. The other thread is noticing some really strange stuff with page allocations when doing just DELETEs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Guy 1 SSCoach Points: 15817 More actions · Answer 8

Tried TRUNCATE - no errors. Tried DELETE - 0 rows affected.

But I probably found the reason, this is likely to be a cloned database, that was created with DBCC CLONEDATABASE. This server is not production, this is performance test server and preparation for production deployment.

I reproduced this situation, cloned another existing database, ran my queries that select from sysindexes/partitions/partition_stats/alloc_units, they all show that tables have records, but select * from actual tables show nothing.

What happened I assume is some of my colleagues cloned database from production, backed it up, restored to this server with replace and move to existing database, and this is where I got confused. I assumed that tables have records, and this is supported by all my queries from sys views, but they actually all empty.

But the bigger problem that there is no way to differentiate between real database and its clone. I tried to compare the entries in sys.databases and could not find any difference.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

SQL Guy 1 wrote:

Tried TRUNCATE - no errors. Tried DELETE - 0 rows affected.
But I probably found the reason, this is likely to be a cloned database, that was created with DBCC CLONEDATABASE. This server is not production, this is performance test server and preparation for production deployment.
I reproduced this situation, cloned another existing database, ran my queries that select from sysindexes/partitions/partition_stats/alloc_units, they all show that tables have records, but select * from actual tables show nothing.
What happened I assume is some of my colleagues cloned database from production, backed it up, restored to this server with replace and move to existing database, and this is where I got confused. I assumed that tables have records, and this is supported by all my queries from sys views, but they actually all empty.
But the bigger problem that there is no way to differentiate between real database and its clone. I tried to compare the entries in sys.databases and could not find any difference.

Lordy. There's one for the books! Thank you for the awesome post-back on what you found. You should write an article about what you found!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 10

If I recall - there are different methods that can be used when you clone a database. One of the purposes is to retain the statistics - so you can troubleshoot query optimizer issues.

I would assume that including statistics requires the system to know how many rows existed in the tables at the time the clone was performed.

If the purpose of cloning the database was to create a copy for dev/test - then the options used should be: VERIFY_CLONEDB, BACKUP_CLONEDB. Those options remove statistics and query store and creates a backup of the cloned DB ready for 'production use'.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 11

I wonder - what happens if you run DBCC UPDATEUSAGE on that cloned database?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 12

Jeffrey Williams wrote:

I wonder - what happens if you run DBCC UPDATEUSAGE on that cloned database?

I believe the OP said they tried that with no success.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 13

Jeff Moden wrote:

Jeffrey Williams wrote:
I wonder - what happens if you run DBCC UPDATEUSAGE on that cloned database?
I believe the OP said they tried that with no success.

Yeah - didn't see that and all it did was update the page counts. Either way - when the clone was created it included statistics and query store which requires row counts to be available. To remove those, then an index rebuild for all tables (alter table for heaps) - and update statistics for column statistics should resolve the issue - or perform the clone with the right parameters.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 14

Interesting. Thanks for the tips, Jeffrey.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)