December 6, 2012

Replication Related Dynamic Management Views

Se more view version maps here: Microsoft Sql System View Version Maps

sys.dm_repl_articles

Returns information about database objects published as articles in a replication topology.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
artcache_db_address • • • • varbinary(8) In-memory address of the cached database structure for the publication database.
artcache_table_address • • • • varbinary(8) In-memory address of the cached table structure for a published table article.
artcache_schema_address • • • • varbinary(8) In-memory address of the cached article schema structure for a published table article.
artcache_article_address • • • • varbinary(8) In-memory address of the cached article structure for a published table article.
artid • • • • bigint Uniquely identifies each entry within this table.
artfilter • • • • bigint ID of the stored procedure used to horizontally filter the article.
artobjid • • • • bigint ID of the published object.
artpubid • • • • bigint ID of the publication to which the article belongs.
artstatus • • • • tinyint Bitmask of the article options and status, which can be the bitwise logical OR result of one or more of these values:
1 = Article is active.
8 = Include the column name in INSERT statements.
16 = Use parameterized statements.
24 = Both include the column name in INSERT statements and use parameterized statements.
For example, an active article using parameterized statements would have a value of 17 in this column. A value of 0 means that the article is inactive and no additional properties are defined.
arttype • • • • tinyint Type of article:
1 = Log-based article.
3 = Log-based article with manual filter.
5 = Log-based article with manual view.
7 = Log-based article with manual filter and manual view.
8 = Stored procedure execution.
24 = Serializable stored procedure execution.
32 = Stored procedure (schema only).
64 = View (schema only).
128 = Function (schema only).
wszartdesttable • • • • nvarchar(514) Name of published object at the destination.
wszartdesttableowner • • • • nvarchar(514) Owner of published object at the destination.
wszartinscmd • • • • nvarchar(510) Command or stored procedure used for inserts.
cmdtypeins • • • • int Call syntax for the insert stored procedure, and can be one of these values.
1 = CALL
2 = SQL
3 = NONE
7 = UNKNOWN
wszartdelcmd • • • • nvarchar(510) Command or stored procedure used for deletes.
cmdtypedel • • • • int Call syntax for the delete stored procedure, and can be one of these values.
0 = XCALL
1 = CALL
2 = SQL
3 = NONE
7 = UNKNOWN
wszartupdcmd • • • • nvarchar(510) Command or stored procedure used for updates.
cmdtypeupd • • • • int Call syntax for the update stored procedure, and can be one of these values.
0 = XCALL
1 = CALL
2 = SQL
3 = NONE
4 = MCALL
5 = VCALL
6 = SCALL
7 = UNKNOWN
wszartpartialupdcmd • • • • nvarchar(510) Command or stored procedure used for partial updates.
cmdtypepartialupd • • • • int Call syntax for the partial update stored procedure, and can be one of these values.
2 = SQL
numcol • • • • int Number of columns in the partition for a vertically filtered article.
artcmdtype • • • • tinyint Type of command currently being replicated, and can be one of these values.
1 = INSERT
2 = DELETE
3 = UPDATE
4 = UPDATETEXT
5 = none
6 = internal use only
7 = internal use only
8 = partial UPDATE
artgeninscmd • • • • nvarchar(510) INSERT command template based on the columns included in the article.
artgendelcmd • • • • nvarchar(510) DELETE command template, which can include the primary key or the columns included in the article, depending on the call syntax is used.
artgenupdcmd • • • • nvarchar(510) UPDATE command template, which can include the primary key, updated columns, or a complete column list depending on the call syntax is used.
artpartialupdcmd • • • • nvarchar(510) Partial UPDATE command template, which includes the primary key and updated columns.
artupdtxtcmd • • • • nvarchar(510) UPDATETEXT command template, which includes the primary key and updated columns.
artgenins2cmd • • • • nvarchar(510) INSERT command template used when reconciling an article during concurrent snapshot processing.
artgendel2cmd • • • • nvarchar(510) DELETE command template used when reconciling an article during concurrent snapshot processing.
finreconcile • • • • tinyint Indicates whether an article is currently being reconciled during concurrent snapshot processing.
fpuballowupdate • • • • tinyint Indicates whether the publication allows updating subscription.
intpublicationoptions • • • • bigint Bitmap that specifies additional publishing options, where the bitwise option values are:
0x1 - Enabled for peer-to-peer replication.
0x2 - Publish only local changes.
0x4 - Enabled for non-SQL Server Subscribers.

TSQL

Sql 2005
SELECT [artcache_db_address], [artcache_table_address], [artcache_schema_address], [artcache_article_address], [artid], [artfilter], [artobjid], [artpubid], [artstatus], [arttype], [wszartdesttable], [wszartdesttableowner], [wszartinscmd], [cmdtypeins], [wszartdelcmd], [cmdtypedel], [wszartupdcmd], [cmdtypeupd], [wszartpartialupdcmd], [cmdtypepartialupd], [numcol], [artcmdtype], [artgeninscmd], [artgendelcmd], [artgenupdcmd], [artpartialupdcmd], [artupdtxtcmd], [artgenins2cmd], [artgendel2cmd], [finreconcile], [fpuballowupdate], [intpublicationoptions] FROM sys.dm_repl_articles
Sql 2008
SELECT [artcache_db_address], [artcache_table_address], [artcache_schema_address], [artcache_article_address], [artid], [artfilter], [artobjid], [artpubid], [artstatus], [arttype], [wszartdesttable], [wszartdesttableowner], [wszartinscmd], [cmdtypeins], [wszartdelcmd], [cmdtypedel], [wszartupdcmd], [cmdtypeupd], [wszartpartialupdcmd], [cmdtypepartialupd], [numcol], [artcmdtype], [artgeninscmd], [artgendelcmd], [artgenupdcmd], [artpartialupdcmd], [artupdtxtcmd], [artgenins2cmd], [artgendel2cmd], [finreconcile], [fpuballowupdate], [intpublicationoptions] FROM sys.dm_repl_articles
Sql 2008 R2
SELECT [artcache_db_address], [artcache_table_address], [artcache_schema_address], [artcache_article_address], [artid], [artfilter], [artobjid], [artpubid], [artstatus], [arttype], [wszartdesttable], [wszartdesttableowner], [wszartinscmd], [cmdtypeins], [wszartdelcmd], [cmdtypedel], [wszartupdcmd], [cmdtypeupd], [wszartpartialupdcmd], [cmdtypepartialupd], [numcol], [artcmdtype], [artgeninscmd], [artgendelcmd], [artgenupdcmd], [artpartialupdcmd], [artupdtxtcmd], [artgenins2cmd], [artgendel2cmd], [finreconcile], [fpuballowupdate], [intpublicationoptions] FROM sys.dm_repl_articles
Sql 2012
SELECT [artcache_db_address], [artcache_table_address], [artcache_schema_address], [artcache_article_address], [artid], [artfilter], [artobjid], [artpubid], [artstatus], [arttype], [wszartdesttable], [wszartdesttableowner], [wszartinscmd], [cmdtypeins], [wszartdelcmd], [cmdtypedel], [wszartupdcmd], [cmdtypeupd], [wszartpartialupdcmd], [cmdtypepartialupd], [numcol], [artcmdtype], [artgeninscmd], [artgendelcmd], [artgenupdcmd], [artpartialupdcmd], [artupdtxtcmd], [artgenins2cmd], [artgendel2cmd], [finreconcile], [fpuballowupdate], [intpublicationoptions] FROM sys.dm_repl_articles

Back to Top


sys.dm_repl_schemas

Returns information about table columns published by replication.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
artcache_schema_address • • • • varbinary(8) In-memory address of the cached schema structure for the published table article.
tabid • • • • bigint ID of the replicated table.
indexid • • • • smallint ID of a clustered index on the published table.
idsch • • • • bigint ID of the table schema.
tabschema • • • • nvarchar(510) Name of the table schema.
cctabschema • • • • smallint Character length of the table schema.
tabname • • • • nvarchar(510) Name of the published table.
cctabname • • • • smallint Character length of the published table name.
rowsetid_delete • • • • bigint ID of the deleted row.
rowsetid_insert • • • • bigint ID of the inserted row.
num_pk_cols • • • • int Number of primary key columns.
pcitee • • • • binary(8000) Pointer to the query expression structure used to evaluate computed column.
re_numtextcols • • • • int Number of binary large object columns in the replicated table.
re_schema_lsn_begin • • • • binary(8000) Beginning log sequence number (LSN) of schema version logging.
re_schema_lsn_end • • • • binary(8000) Ending LSN of schema version logging.
re_numcols • • • • int Number of columns published.
re_colid • • • • int Column identifier at the Publisher.
re_awcname • • • • nvarchar(510) Name of the published column.
re_ccname • • • • smallint Number of characters in the column name.
re_pk • • • • tinyint Whether the published column is part of a primary key.
re_unique • • • • tinyint Whether the published column is part of a unique index.
re_maxlen • • • • smallint Maximum length of the published column.
re_prec • • • • tinyint Precision of the published column.
re_scale • • • • tinyint Scale of the published column.
re_collatid • • • • bigint Collation ID for published column.
re_xvtype • • • • smallint Type of the published column.
re_offset • • • • smallint Offset of the published column.
re_bitpos • • • • tinyint Bit position of the published column, in the byte vector.
re_fnullable • • • • tinyint Specifies whether the published column supports NULL values.
re_fansitrim • • • • tinyint Specifies whether ANSI trim is used on the published column.
re_computed • • • • smallint Specifies whether the published column is a computed column.
se_rowsetid • • • • bigint ID of the rowset.
se_schema_lsn_begin • • • • binary(8000) Beginning LSN of schema version logging.
se_schema_lsn_end • • • • binary(8000) Ending LSN of schema version logging.
se_numcols • • • • int Number of columns.
se_colid • • • • int ID of the column at the Subscriber.
se_maxlen • • • • smallint Maximum length of the column.
se_prec • • • • tinyint Precision of the column.
se_scale • • • • tinyint Scale of the column.
se_collatid • • • • bigint Collation ID for column.
se_xvtype • • • • smallint Type of the column.
se_offset • • • • smallint Offset of the column.
se_bitpos • • • • tinyint Bit position of the column, in the byte vector.
se_fnullable • • • • tinyint Specifies whether the column supports NULL values.
se_fansitrim • • • • tinyint Specifies whether ANSI trim is used on the column.
se_computed • • • • smallint Specifies whether the columnis a computed column.
se_nullbitinleafrows • • • • int Specifies whether the column value is NULL.

TSQL

Sql 2005
SELECT [artcache_schema_address], [tabid], [indexid], [idsch], [tabschema], [cctabschema], [tabname], [cctabname], [rowsetid_delete], [rowsetid_insert], [num_pk_cols], [pcitee], [re_numtextcols], [re_schema_lsn_begin], [re_schema_lsn_end], [re_numcols], [re_colid], [re_awcname], [re_ccname], [re_pk], [re_unique], [re_maxlen], [re_prec], [re_scale], [re_collatid], [re_xvtype], [re_offset], [re_bitpos], [re_fnullable], [re_fansitrim], [re_computed], [se_rowsetid], [se_schema_lsn_begin], [se_schema_lsn_end], [se_numcols], [se_colid], [se_maxlen], [se_prec], [se_scale], [se_collatid], [se_xvtype], [se_offset], [se_bitpos], [se_fnullable], [se_fansitrim], [se_computed], [se_nullbitinleafrows] FROM sys.dm_repl_schemas
Sql 2008
SELECT [artcache_schema_address], [tabid], [indexid], [idsch], [tabschema], [cctabschema], [tabname], [cctabname], [rowsetid_delete], [rowsetid_insert], [num_pk_cols], [pcitee], [re_numtextcols], [re_schema_lsn_begin], [re_schema_lsn_end], [re_numcols], [re_colid], [re_awcname], [re_ccname], [re_pk], [re_unique], [re_maxlen], [re_prec], [re_scale], [re_collatid], [re_xvtype], [re_offset], [re_bitpos], [re_fnullable], [re_fansitrim], [re_computed], [se_rowsetid], [se_schema_lsn_begin], [se_schema_lsn_end], [se_numcols], [se_colid], [se_maxlen], [se_prec], [se_scale], [se_collatid], [se_xvtype], [se_offset], [se_bitpos], [se_fnullable], [se_fansitrim], [se_computed], [se_nullbitinleafrows] FROM sys.dm_repl_schemas
Sql 2008 R2
SELECT [artcache_schema_address], [tabid], [indexid], [idsch], [tabschema], [cctabschema], [tabname], [cctabname], [rowsetid_delete], [rowsetid_insert], [num_pk_cols], [pcitee], [re_numtextcols], [re_schema_lsn_begin], [re_schema_lsn_end], [re_numcols], [re_colid], [re_awcname], [re_ccname], [re_pk], [re_unique], [re_maxlen], [re_prec], [re_scale], [re_collatid], [re_xvtype], [re_offset], [re_bitpos], [re_fnullable], [re_fansitrim], [re_computed], [se_rowsetid], [se_schema_lsn_begin], [se_schema_lsn_end], [se_numcols], [se_colid], [se_maxlen], [se_prec], [se_scale], [se_collatid], [se_xvtype], [se_offset], [se_bitpos], [se_fnullable], [se_fansitrim], [se_computed], [se_nullbitinleafrows] FROM sys.dm_repl_schemas
Sql 2012
SELECT [artcache_schema_address], [tabid], [indexid], [idsch], [tabschema], [cctabschema], [tabname], [cctabname], [rowsetid_delete], [rowsetid_insert], [num_pk_cols], [pcitee], [re_numtextcols], [re_schema_lsn_begin], [re_schema_lsn_end], [re_numcols], [re_colid], [re_awcname], [re_ccname], [re_pk], [re_unique], [re_maxlen], [re_prec], [re_scale], [re_collatid], [re_xvtype], [re_offset], [re_bitpos], [re_fnullable], [re_fansitrim], [re_computed], [se_rowsetid], [se_schema_lsn_begin], [se_schema_lsn_end], [se_numcols], [se_colid], [se_maxlen], [se_prec], [se_scale], [se_collatid], [se_xvtype], [se_offset], [se_bitpos], [se_fnullable], [se_fansitrim], [se_computed], [se_nullbitinleafrows] FROM sys.dm_repl_schemas

Back to Top


sys.dm_repl_tranhash

Returns information about transactions being replicated in a transactional publication. column_namedata_typedescription buckets bigint Number of buckets in the hash table. hashed_trans bigint Number of committed transactions replicated in the current batch. completed_trans bigint Number of transactions competed so far. compensated_trans bigint Number of transactions that contain partial rollbacks. first_begin_lsn nvarchar(64) Earliest begin log sequence number (LSN) in the current batch. last_commit_lsn nvarchar(64) Last commit LSN in the current batch.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
buckets • • • • bigint Number of buckets in the hash table.
hashed_trans • • • • bigint Number of committed transactions replicated in the current batch.
completed_trans • • • • bigint Number of transactions competed so far.
compensated_trans • • • • bigint Number of transactions that contain partial rollbacks.
first_begin_lsn • • • • nvarchar(64) Earliest begin log sequence number (LSN) in the current batch.
last_commit_lsn • • • • nvarchar(64) Last commit LSN in the current batch.

TSQL

Sql 2005
SELECT [buckets], [hashed_trans], [completed_trans], [compensated_trans], [first_begin_lsn], [last_commit_lsn] FROM sys.dm_repl_tranhash
Sql 2008
SELECT [buckets], [hashed_trans], [completed_trans], [compensated_trans], [first_begin_lsn], [last_commit_lsn] FROM sys.dm_repl_tranhash
Sql 2008 R2
SELECT [buckets], [hashed_trans], [completed_trans], [compensated_trans], [first_begin_lsn], [last_commit_lsn] FROM sys.dm_repl_tranhash
Sql 2012
SELECT [buckets], [hashed_trans], [completed_trans], [compensated_trans], [first_begin_lsn], [last_commit_lsn] FROM sys.dm_repl_tranhash

Back to Top


sys.dm_repl_traninfo

Returns information on each replicated or change data capture transaction.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
fp2p_pub_exists • • • • tinyint If the transaction is in a database published using peer-to-peer transactional replication.
db_ver • • • • int Database version.
comp_range_address • • • • varbinary(8) Defines a partial rollback range that must be skipped.
textinfo_address • • • • varbinary(8) In-memory address of the cached text information structure.
fsinfo_address • • • • varbinary(8) In-memory address of the cached filestream information structure.
begin_lsn • • • • nvarchar(64) Log sequence number (LSN) of the beginning log record for the transaction.
commit_lsn • • • • nvarchar(64) LSN of commit log record for the transaction.
dbid • • • • smallint Database ID.
rows • • • • int ID of the replicated command within the transaction.
xdesid • • • • nvarchar(64) Transaction ID.
artcache_table_address • • • • varbinary(8) In-memory address of the cached article table structure last used for this transaction.
server • • • • nvarchar(514) Server name.
server_len_in_bytes • • • • smallint Character length, in bytes, of the server name.
database • • • • nvarchar(514) Database name.
db_len_in_bytes • • • • smallint Character length, in bytes, of the database name.
originator • • • • nvarchar(514) Name of the server where the transaction originated.
originator_len_in_bytes • • • • smallint Character length, in bytes, of the server where the transaction originated.
orig_db • • • • nvarchar(514) Name of the database where the transaction originated.
orig_db_len_in_bytes • • • • smallint Character length, in bytes, of the database where the transaction originated.
cmds_in_tran • • • • int Number of replicated commands in the current transaction, which is used to determine when a logical transaction should be committed.
is_boundedupdate_singleton • • • • tinyint Specifies whether a unique column update affects only a single row.
begin_update_lsn • • • • nvarchar(64) LSN used in a unique column update.
delete_lsn • • • • nvarchar(64) LSN to delete as part of an update.
last_end_lsn • • • • nvarchar(64) Last LSN in a logical transaction.
fcomplete • • • • tinyint Specifies whether the command is a partial update.
fcompensated • • • • tinyint Specifies whether the transaction is involved in a partial rollback.
fprocessingtext • • • • tinyint Specifies whether the transaction includes a binary large data type column.
max_cmds_in_tran • • • • int Maximum number of commands in a logical transaction, as specified by the Log Reader Agent.
begin_time   • • • datetime Time the transaction began.
commit_time   • • • datetime Time the transaction was committed.
session_id   • • • int ID of the change data capture log scan session. This column maps to the session_id column in sys.dm_cdc_logscan_sessions1.
session_phase   • • • int Number that indicates the phase the session was in at the time the error occurred. This column maps to the phase_number column in sys.dm_cdc_errors2.
is_known_cdc_tran   • • • bit Indicates the transaction is tracked by change data capture.
0 = Transaction replication transaction.
1 = Change data capture transaction.
error_count   • • • int Number of errors encountered.

TSQL

Sql 2005
SELECT [fp2p_pub_exists], [db_ver], [comp_range_address], [textinfo_address], [fsinfo_address], [begin_lsn], [commit_lsn], [dbid], [rows], [xdesid], [artcache_table_address], [server], [server_len_in_bytes], [database], [db_len_in_bytes], [originator], [originator_len_in_bytes], [orig_db], [orig_db_len_in_bytes], [cmds_in_tran], [is_boundedupdate_singleton], [begin_update_lsn], [delete_lsn], [last_end_lsn], [fcomplete], [fcompensated], [fprocessingtext], [max_cmds_in_tran] FROM sys.dm_repl_traninfo
Sql 2008
SELECT [fp2p_pub_exists], [db_ver], [comp_range_address], [textinfo_address], [fsinfo_address], [begin_lsn], [commit_lsn], [dbid], [rows], [xdesid], [artcache_table_address], [server], [server_len_in_bytes], [database], [db_len_in_bytes], [originator], [originator_len_in_bytes], [orig_db], [orig_db_len_in_bytes], [cmds_in_tran], [is_boundedupdate_singleton], [begin_update_lsn], [delete_lsn], [last_end_lsn], [fcomplete], [fcompensated], [fprocessingtext], [max_cmds_in_tran], [begin_time], [commit_time], [session_id], [session_phase], [is_known_cdc_tran], [error_count] FROM sys.dm_repl_traninfo
Sql 2008 R2
SELECT [fp2p_pub_exists], [db_ver], [comp_range_address], [textinfo_address], [fsinfo_address], [begin_lsn], [commit_lsn], [dbid], [rows], [xdesid], [artcache_table_address], [server], [server_len_in_bytes], [database], [db_len_in_bytes], [originator], [originator_len_in_bytes], [orig_db], [orig_db_len_in_bytes], [cmds_in_tran], [is_boundedupdate_singleton], [begin_update_lsn], [delete_lsn], [last_end_lsn], [fcomplete], [fcompensated], [fprocessingtext], [max_cmds_in_tran], [begin_time], [commit_time], [session_id], [session_phase], [is_known_cdc_tran], [error_count] FROM sys.dm_repl_traninfo
Sql 2012
SELECT [fp2p_pub_exists], [db_ver], [comp_range_address], [textinfo_address], [fsinfo_address], [begin_lsn], [commit_lsn], [dbid], [rows], [xdesid], [artcache_table_address], [server], [server_len_in_bytes], [database], [db_len_in_bytes], [originator], [originator_len_in_bytes], [orig_db], [orig_db_len_in_bytes], [cmds_in_tran], [is_boundedupdate_singleton], [begin_update_lsn], [delete_lsn], [last_end_lsn], [fcomplete], [fcompensated], [fprocessingtext], [max_cmds_in_tran], [begin_time], [commit_time], [session_id], [session_phase], [is_known_cdc_tran], [error_count] FROM sys.dm_repl_traninfo

Back to Top
Indsendt af kl.
Etiketter: , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Total Pageviews