Enable DBCC trace flags on SQL Server - ApsaraDB RDS

ApsaraDB RDS for SQL Server instances running SQL Server 2012 or later support a subset of Database Consistency Checker (DBCC) trace flags. Use the sp_rds_dbcc_trace stored procedure with a privileged account to enable these flags and monitor deadlocks, storage usage, and cache data.

Supported trace flags

Trace flag	Purpose
1204	Reports the resources and types of locks involved in a deadlock, and the current command affected.
1211	Disables lock escalation when memory pressure occurs or when the number of locks exceeds the threshold.
1222	Returns deadlock resources and lock types in XML format (does not comply with any XSD schema).
1224	Disables lock escalation based on the number of locks.
1117	Grows all files in a filegroup simultaneously when an autogrowth event occurs.
1118	Forces uniform extent allocations instead of mixed pages, reducing contention on the SGAM page.
3604	Sends DBCC output to the client. Required when using certain DBCC commands in a session.

For more information about trace flags and usage notes, see Microsoft documentation.

To check whether a trace flag is currently enabled, run DBCC tracestatus(-1).

Enable a trace flag

The following example shows how to enable trace flag 1222 to capture deadlock information.

Prerequisites

An ApsaraDB RDS for SQL Server instance running SQL Server 2012 or later
A privileged account with permission to run stored procedures

Procedure

Connect to the instance using your privileged account.

Verify the database engine version, create a test database, and check the current trace flag status.

USE master
GO
-- Query the version of the current database engine.
SELECT SERVERPROPERTY('edition')
GO
-- Create a database.
CREATE DATABASE testdb
GO
DBCC tracestatus(-1)

Enable trace flag 1222.
```
-- Enable the trace flag.
EXEC sp_rds_dbcc_trace 1222,1
WAITFOR DELAY '00:00:10'
DBCC tracestatus(-1)
GO
```
The sp_rds_dbcc_trace stored procedure accepts two parameters: the trace flag number, and 1 to enable or 0 to disable. After the script runs, DBCC tracestatus(-1) confirms whether trace flag 1222 is active.