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.
- Do you know if this a bug or "as designed"?
- 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: