Run the Transact-SQL Debugger

 

Applies To: SQL Server 2016

You can start the Transact-SQL debugger after you open a Database Engine Query Editor window. Then, you can run your Transact-SQL code in debug mode until you stop the debugger. You can set options to customize how the debugger runs.

The requirements to start the Transact-SQL debugger are as follows:

  • If your Database Engine Query Editor is connected to an instance of the Database Engine on another computer, you must have configured the debugger for remote debugging. For more information, see Configure firewall rules before running the TSQL Debugger.

  • SQL Server Management Studio must be running under a Windows account that is a member of the sysadmin fixed server roll.

  • The Database Engine Query Editor window must be connected by using either a Windows Authentication or SQL Server Authentication login that is a member of the sysadmin fixed server role.

  • The Database Engine Query Editor window must be connected to an instance of the Database Engine from SQL Server 2005 Service Pack 2 (SP2) or later. You cannot run the debugger when the Query Editor window is connected to an instance that is in single-user mode.

We recommend that Transact-SQL code be debugged on a test server, not a production server, for the following reasons:

  • Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server.

  • Debugging sessions often run for long periods of time while you investigate the operations of several Transact-SQL statements. Locks, such as update locks, that are acquired by the session might be held for extended periods, until the session is ended or the transaction committed or rolled back.

Starting the Transact-SQL debugger puts the Query Editor window into debug mode. When the Query Editor window enters debug mode, the debugger pauses at the first line of code. You can then step through the code, pause the execution on specific Transact-SQL statements, and use the debugger windows to view the current execution state. You can start the debugger by either clicking the Debug button on the Query toolbar or by clicking Start Debugging on the Debug menu.

The Query Editor window stays in debug mode until either the last statement in the Query Editor window finishes or you stop debug mode. You can stop debug mode and statement execution by using any one of the following methods:

  • On the Debug menu, click Stop Debugging.

  • On the Debug toolbar, click the Stop Debugging button.

  • On the Query menu, click Cancel Executing Query.

  • On the Query toolbar, click the Cancel Executing Query button.

You can also stop debug mode and allow for the remaining Transact-SQL statements to finish executing by clicking Detach All on the Debug menu.

You can control how the Transact-SQL debugger operates by using the following menu commands, toolbars, and shortcuts:

  • The Debug menu and the Debug toolbar. Both the Debug menu and Debug toolbar are inactive until the focus is placed in an open Query Editor window. They remain active until the current project is closed.

  • The debugger keyboard shortcuts.

  • The Query Editor shortcut menu. The shortcut menu is displayed when you right-click a line in a Query Editor window. When the Query Editor window is in debug mode, the shortcut menu displays debugger commands that apply to the selected line or string.

  • Menu items and context commands in the windows that are opened by the debugger, such as the Watch or Breakpoints windows.

The following table shows the debugger menu commands, toolbar buttons, and keyboard shortcuts.

Debug menu commandEditor shortcut commandToolbar buttonKeyboard shortcutAction
Windows/BreakpointsNot availableBreakpointsCTRL+ALT+BDisplay the Breakpoints window in which you can view and manage breakpoints.
Windows/Watch/Watch1Not availableBreakpoints/Watch/Watch1CTRL+ALT+W, 1Display the Watch1 window.
Windows/Watch/Watch2Not availableBreakpoints/Watch/Watch2CTRL+ALT+W, 2Display the Watch2 window.
Windows/Watch/Watch3Not availableBreakpoints/Watch/Watch3CTRL+ALT+W, 3Display the Watch3 window.
Windows/Watch/Watch4Not availableBreakpoints/Watch/Watch4CTRL+ALT+W, 4Display the Watch4 window.
Windows/LocalsNot availableBreakpoints/LocalsCTRL+ALT+V, LDisplay the Locals window.
Windows/Call StackNot availableBreakpoints/Call StackCTRL+ALT+CDisplay the Call Stack window.
Windows/ThreadsNot availableBreakpoints/ThreadsCTRL+ALT+HDisplay the Threads window.
ContinueNot availableContinueALT+F5Run to the next breakpoint. Continue is not active until you are focused on a Query Editor window that is in debug mode.
Start DebuggingNot availableStart DebuggingALT+F5Put a Query Editor window into debug mode and run to the first breakpoint. If you are focused on a Query Editor window that is in debug mode, Start Debugging is replaced by Continue.
Break AllNot availableBreak AllCTRL+ALT+BREAKThis feature not used by the Transact-SQL debugger.
Stop DebuggingNot availableStop DebuggingSHIFT+F5Take a Query Editor window out of debug mode and return it to regular mode.
Detach AllNot availableNot availableNot availableStops debug mode, but executes the remaining statements in the Query Editor window.
Step IntoNot availableStep IntoF11Run the next statement, and also open a new Query Editor window in debug mode if the next statement runs a stored procedure, trigger, or function.
Step OverNot availableStep OverF10Same as Step Into, except that no functions, stored procedures, or triggers are debugged.
Step OutNot availableStep OutSHIFT+F11Execute the remaining code in a trigger, function, or stored procedure without pausing for any breakpoints. Regular debug mode resumes when control is returned to the code that called the module.
Not availableRun To CursorNot availableCTRL+F10Execute all code from the last stop location to the current cursor location without stopping at any breakpoints.
QuickWatchQuickWatchNot availableCTRL+ALT+QDisplay the QuickWatch window.
Toggle BreakpointBreakpoint/Insert BreakpointNot availableF9Position a breakpoint on the current or selected Transact-SQL statement.
Not availableBreakpoint/Delete BreakpointNot availableNot availableDelete the breakpoint from the selected line.
Not availableBreakpoint/Disable BreakpointNot availableNot availableDisable the breakpoint on the selected line. The breakpoint remains on the line of code, but will not stop execution until it is reenabled.
Not availableBreakpoint/Enable BreakpointNot availableNot availableEnable the breakpoint on the selected line.
Delete All BreakpointsNot availableNot availableCTRL+SHIFT+F9Delete all breakpoints.
Disable All BreakpointsNot availableNot availableNot availableDisable all breakpoints.
Not availableAdd WatchNot availableNot availableAdd the selected expression to the Watch window.

Transact-SQL Debugger
Step Through Transact-SQL Code
Transact-SQL Debugger Information
Database Engine Query Editor (SQL Server Management Studio)
Live Query Statistics

Community Additions

ADD
Show: