Ownership Chaininig concept in SQL Server

Schema Owner vs. Object Owner – Who wins?

The Ownership Chain – How the Securable Owner affects Permission Enforcement in SQL Server

Advertisements

Transactional Replication Troubleshooting basic commands

sp_replshowcmds 

Displays transactions pending in transaction log at publication database

sp_repldone 

  • Allows for skipping commands at Log Reader Agent
  • Large update you may not want propagated (e.g. millions of rows you don’t want replicated)
  • Situations where re-initialize would be too time consuming or costly
  • This procedure can be used in emergency situations to allow truncation of the transaction log when transactions pending replication are present.
  • Use as an exception, not as rule

sp_replflush
Flushes ‘article cache’ on transaction log and allows the Log Reader to resume work (only one log reading process can be used for a database at a time)

Extended Events: to track waits by session

Sample taken from Joe Sack’s Pluralsight course on Transactional Replication Fundamentals

CREATE EVENT SESSION ExtendedEventsSessionName
ON SERVER
ADD EVENT sqlos.wait_info(
ACTION (sqlserver.session_id)
WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(61—spid)) OR
[package0].[equal_uint64]([sqlserver].[session_id],(53—spid))))
ADD TARGET package0.asynchronous_file_target
(SET FILENAME = N’C:\TEMP\xet.xel’, — CHECK that these are cleared
METADATAFILE = N’C:\TEMP\xet.xem’);

ALTER EVENT SESSION ExtendedEventsSessionName
ON SERVER STATE = START;
GO

—-Let the workload run

ALTER EVENT SESSION ExtendedEventsSessionName
ON SERVER STATE = STOP;
GO

— Raw data into intermediate table
— (Make sure you’ve cleared out previous target files!)
SELECT CAST(event_data as XML) event_data
INTO #xet_Stage_1
FROM sys.fn_xe_file_target_read_file
(‘C:\TEMP\xet*.xel’,
‘C:\TEMP\xet*.xem’,
NULL, NULL);

— Aggregated data into intermediate table
SELECT
event_data.value
(‘(/event/action[@name=”session_id”]/value)[1]’, ‘smallint’) as session_id,
event_data.value
(‘(/event/data[@name=”wait_type”]/text)[1]’, ‘varchar(100)’) as wait_type,
event_data.value
(‘(/event/data[@name=”duration”]/value)[1]’, ‘bigint’) as duration,
event_data.value
(‘(/event/data[@name=”signal_duration”]/value)[1]’, ‘bigint’) as signal_duration,
event_data.value
(‘(/event/data[@name=”completed_count”]/value)[1]’, ‘bigint’) as completed_count
INTO #xet_Stage_2
FROM #xet_Stage_1;

— Final result set
SELECT session_id,
wait_type,
SUM(duration) total_duration,
SUM(signal_duration) total_signal_duration,
SUM(completed_count) total_wait_count
FROM #xet_Stage_2
GROUP BY session_id,
wait_type
ORDER BY session_id,
SUM(duration) DESC;
GO