Tables are empty but catalog views show that they have rows

  • 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

     

     

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

  • 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)
    Intro to Tally Tables and Functions

  • 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.

     

  • 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)
    Intro to Tally Tables and Functions

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

     

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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)
    Intro to Tally Tables and Functions

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • 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)
    Intro to Tally Tables and Functions

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • 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)
    Intro to Tally Tables and Functions

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

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