1

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 
2

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;
1
  • Yep, true that sys.partitions.rows in clone same as source is by design. one of the benefits of the clone database is reproducing and exploring plans based on row estimates. if all row counts get forced to zero in the clone, won’t be able to reproduce many plans as they would appear in the source database. – sqL_handLe Dec 30 '20 at 23:50
3

If you intend to use the cloned database for production use, specify the VERIFY_CLONEDB option which also clears the statistics in the cloned database.

VERIFY_CLONEDB

Verifies the consistency of the new database. This option is required if the cloned database is intended for production use. Enabling VERIFY_CLONEDB also disables statistics and query store collection, thus it is equivalent to running WITH VERIFY_CLONEDB, NO_STATISTICS, NO_QUERYSTORE. This option is available starting with SQL Server 2014 (12.x) SP3, SQL Server 2016 (13.x) SP2, and SQL Server 2017 (14.x) CU8.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-ver15

This is an artifact of the history of this command. Initially it was just for troubleshooting, but was later enhanced to enable you to use the cloned database for production.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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