Sql-server – DBCC CloneDatabase – sys.partitions rows value wrong

sql serversql-server-2016sql-server-2019t-sql

I'm using DBCC CLONEDATABASE to create a shell of a schema.

One of the side-effects of the new shell clone database is that I left with wrong values at sys.partitions.
as a matter of fact, the values of the row column are the ones I had in the source database.

  1. Do you know if this a bug or "as designed"?
  2. Do you know any other way to "refresh" the sys.partitions DMV without running TRUNCATE to all tables?

Code to reproduce –

USE [master];
GO
SET NOCOUNT ON;
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.databases D WHERE D.name = 'CloneDBIssue')
BEGIN
    DROP DATABASE [CloneDBIssue];
END
GO
IF EXISTS(SELECT TOP 1 1 FROM sys.databases D WHERE D.name = 'CloneDBIssue_Clone')
BEGIN
    DROP DATABASE [CloneDBIssue_Clone];
END
GO 
CREATE DATABASE [CloneDBIssue];
GO
USE [CloneDBIssue];
GO
--13213
SELECT  M.message_id,
        M.language_id,
        M.severity,
        M.is_event_logged,
        M.text
INTO    dbo.CDB_messages
FROM    sys.messages M
WHERE   M.language_id = 1033;
GO
CREATE CLUSTERED INDEX CIX ON dbo.CDB_messages(message_id);
GO 
SELECT  'After Insert - Source DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO
DBCC CLONEDATABASE (CloneDBIssue, CloneDBIssue_Clone);
GO
USE master
GO
DROP DATABASE CloneDBIssue;
GO
USE [master]
GO
ALTER DATABASE [CloneDBIssue_Clone] SET  READ_WRITE WITH NO_WAIT;
GO
USE [CloneDBIssue_Clone];
GO
SELECT  'dbo.CDB_messages' [Table],COUNT(*)[Rows]
FROM    dbo.CDB_messages;

SELECT  'After Clone - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO 
INSERT dbo.CDB_messages
VALUES (0,0,0,0,N'');
GO
SELECT  'After Insert - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);

GO 
DELETE TOP(3214) FROM dbo.CDB_messages;
GO
SELECT  'After DELETE - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO 
TRUNCATE TABLE dbo.CDB_messages;
GO
SELECT  'After TRUNCATE - Target DB' [Title],
        object_id,
        index_id,
        rows
FROM    sys.partitions
WHERE   OBJECT_ID('dbo.CDB_messages') = OBJECT_ID
        AND index_id IN (0,1);
GO 

Best Answer

I believe this is by design because the DBCC CLONEDATABASE documentation specifies system statistics are copied. My interpretation is that includes not only stats blobs but row counts as well.

To rectify the row counts, execute DBCC UPDATEUSAGE with the COUNT_ROWS option:

DBCC UPDATEUSAGE('CloneDBIssue_Clone') WITH COUNT_ROWS;