DBCC CloneDatabase - sys.partitions 行值錯誤

我正在使用DBCC CLONEDATABASE創建模式的外殼。

新的 shell 複製數據庫的副作用之一是我在sys.partitions留下了錯誤的值。事實上，行列的值是我在源數據庫中的值。

1. 你知道這是一個錯誤還是“按設計”？
2. 您是否知道任何其他“刷新” sys.partitions DMV 而無需對所有表執行TRUNCATE的方法？

重現程式碼 -

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 

我相信這是設計使然，因為DBCC CLONEDATABASE 文件指定複製系統統計資訊。我的解釋是，這不僅包括統計數據塊，還包括行數。

要更正行數，請使用以下選項執行DBCC UPDATEUSAGE ：COUNT_ROWS

DBCC UPDATEUSAGE('CloneDBIssue_Clone') WITH COUNT_ROWS;

如果您打算將複製數據庫用於生產用途，請指定 VERIFY_CLONEDB 選項，該選項也會清除複製數據庫中的統計資訊。

VERIFY_CLONEDB

驗證新數據庫的一致性。如果複製的數據庫用於生產用途，則需要此選項。啟用 VERIFY_CLONEDB 還會禁用統計資訊和查詢儲存收集，因此它等效於執行 WITH VERIFY_CLONEDB、NO_STATISTICS、NO_QUERYSTORE。此選項從 SQL Server 2014 (12.x) SP3、SQL Server 2016 (13.x) SP2 和 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

這是該命令歷史的神器。最初它只是用於故障排除，但後來得到了增強，使您能夠將複製的數據庫用於生產。

引用自：https://dba.stackexchange.com/questions/282367