What is the recommended action you should take to start global trace flags?

You manage an instance of SQL Server 2008. You discover some deadlocks on one of the databases stored inthe instance.
You want to start the global trace flag 1204 to obtain information about locks and resourcesparticipating in these deadlocks.
What is the recommended action you should take to start global trace flags?

You manage an instance of SQL Server 2008. You discover some deadlocks on one of the databases stored inthe instance.
You want to start the global trace flag 1204 to obtain information about locks and resourcesparticipating in these deadlocks.
What is the recommended action you should take to start global trace flags?

A.
Use the sqlservr.exe -T command.

B.
Use the sqlservr.exe -m command.

C.
Use the DBCC TRACEON Transact-SQL statement.

D.
Use the sp_trace_setstatus system stored procedure.

Explanation:

You should use the sqlservr.exe -T command to start global trace flags. Trace flag 1204 is a global trace flag.
You must enable a global trace flag globally. Otherwise, the trace flag will not have any effect. The recommendedmethod of enabling global trace flags is to start the instance by using the sqlservr.exe -T command. The -T parameter is used to specify the trace number that should be enabled at startup. When you enable the deadlockflags globally in SQL Server 2008, the deadlock reporting information is recorded in the SQL Server error log. You should not use the sqlservr.exe -m command because the -m parameter is used to start an instance of SQLServer in single-user mode. The -m parameter cannot be used to start global trace flags. You should not use the DBCC TRACEON Transact-SQL statement. This statement is used to enable a specifiedtrace flag, but it is not the recommended method for starting global trace flags. You should not use the sp_trace_setstatus system stored procedure because this stored procedure does notallow you to start global trace flags. The sp_trace_setstatus system stored procedure is used to start, stop, orclose a trace created by using the sp_trace_create system stored procedure.

Objective:
Optimizing SQL Server Performance

Sub-Objective:
Collect trace data by using SQL Server Profiler.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > Trace Flags (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > DBCC (Transact-SQL) > DBCC TRACEON (Transact-SQL)



Leave a Reply 0

Your email address will not be published. Required fields are marked *