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
