Notes about SQL Server

SQL Commands for SQL Server

• Retrieve all databases

SELECT * FROM SYS.DATABASES

• Select a database for following commands in same session

USE [<DATABASE_NAME>]  
GO

Transaction Log

• Query transaction log

SELECT 
[Current LSN], 
[Transaction ID], 
[Operation], 
[Transaction Name], 
[CONTEXT], 
[AllocUnitName], 
[Page ID], 
[Slot ID], 
[Begin Time], 
[End Time], 
[Number of Locks], 
[Lock Information] 
FROM sys.fn_dblog(NULL,NULL) 
WHERE Operation IN  
   ('LOP_INSERT_ROWS','LOP_MODIFY_ROW', 
    'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')   
ORDER BY [Begin Time] DESC

• Enable Snapshot Transaction Isolation

ALTER DATABASE <DATABASE_NAME> SET ALLOW_SNAPSHOT_ISOLATION ON 
GO

== Change Data Capture (CDC)

• Disable CDC

EXEC sys.sp_cdc_disable_db  
GO

• Enable CDC

EXEC sys.sp_cdc_enable_db 
GO

• Enable CDC for a given table

EXEC sys.sp_cdc_enable_table   
@source_schema = N'<SCHEMA_NAME>',
@source_name   = N'<TABLE_NAME>',
@role_name     = NULL;

• Check CDC

EXEC sys.sp_cdc_help_change_data_capture 
GO

• Query CDC

SELECT * from sys.dm_cdc_log_scan_sessions with (nolock) order by start_time DESC

• Get all jobs related to CDC

SELECT * FROM msdb.dbo.cdc_jobs

• Create all jobs related to CDC

EXEC sys.sp_cdc_add_job @job_type = 'capture'; 
EXEC sys.sp_cdc_add_job   
@job_type = 'cleanup',
@start_job = 0,
@retention = 5760;