Configuring and Starting the Transact-SQL Debugger

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. To meet your requirements, you can customize how the debugger runs by setting options.

Configuring the Transact-SQL Debugger

The Transact-SQL debugger includes both server-side and client-side components. The server-side debugger components are installed with each instance of the SQL Server 2008 Database Engine. The client-side components are installed when you install the SQL Server 2008 client-side tools.

There are no configuration requirements to run the Transact-SQL debugger when SQL Server Management Studio is running on the same computer as the instance of the SQL Server Database Engine. However, to run the Transact-SQL debugger when SQL Server Management Studio is running on a different computer from the instance of the Database Engine, you must enable program and port exceptions by using the Windows Firewall Control Panel application on both computers.

On the computer that is running the instance of the Database Engine, in Windows Firewall, specify the following information:

  • Add TCP port 135 to the exceptions list.

  • Add the program sqlservr.exe to the exceptions list. By default, sqlservr.exe is installed in C:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Binn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance.

  • If the domain policy requires network communications to be done through IPsec, you must also add UDP port 4500 and UDP port 500 to the exception list.

On the computer that is running SQL Server Management Studio, in Windows Firewall, specify the following information:

  • Add TCP port 135 to the exceptions list.

  • Add program ssms.exe (SQL Server Management Studio) to the exceptions list. By default, ssms.exe is installed in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE.

Starting and Stopping the Debugger

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

  • 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 SQL Server 2008 Database Engine. 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.

Controlling the Debugger

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 command

Editor shortcut command

Toolbar button

Keyboard shortcut

Action

Windows/Breakpoints

Not available

Breakpoints

CTRL+ALT+B

Display the Breakpoints window in which you can view and manage breakpoints.

Windows/Watch/Watch1

Not available

Breakpoints/Watch/Watch1

CTRL+ALT+W, 1

Display the Watch1 window.

Windows/Watch/Watch2

Not available

Breakpoints/Watch/Watch2

CTRL+ALT+W, 2

Display the Watch2 window.

Windows/Watch/Watch3

Not available

Breakpoints/Watch/Watch3

CTRL+ALT+W, 3

Display the Watch3 window.

Windows/Watch/Watch4

Not available

Breakpoints/Watch/Watch4

CTRL+ALT+W, 4

Display the Watch4 window.

Windows/Locals

Not available

Breakpoints/Locals

CTRL+ALT+V, L

Display the Locals window.

Windows/Call Stack

Not available

Breakpoints/Call Stack

CTRL+ALT+C

Display the Call Stack window.

Windows/Threads

Not available

Breakpoints/Threads

CTRL+ALT+H

Display the Threads window.

Continue

Not available

Continue

ALT+F5

Run to the next breakpoint. Continue is not active until you are focused on a Query Editor window that is in debug mode.

Start Debugging

Not available

Start Debugging

ALT+F5

Put 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 All

Not available

Break All

CTRL+ALT+BREAK

This feature not used by the Transact-SQL debugger.

Stop Debugging

Not available

Stop Debugging

SHIFT+F5

Take a Query Editor window out of debug mode and return it to regular mode.

Detach All

Not available

Not available

Not available

Stops debug mode, but executes the remaining statements in the Query Editor window.

Step Into

Not available

Step Into

F11

Run 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 Over

Not available

Step Over

F10

Same as Step Into, except that no functions, stored procedures, or triggers are debugged.

Step Out

Not available

Step Out

SHIFT+F11

Execute 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 available

Run To Cursor

Not available

CTRL+F10

Execute all code from the last stop location to the current cursor location without stopping at any breakpoints.

QuickWatch

QuickWatch

Not available

CTRL+ALT+Q

Display the QuickWatch window.

Toggle Breakpoint

Breakpoint/Insert Breakpoint

Not available

F9

Position a breakpoint on the current or selected Transact-SQL statement.

Not available

Breakpoint/Delete Breakpoint

Not available

Not available

Delete the breakpoint from the selected line.

Not available

Breakpoint/Disable Breakpoint

Not available

Not available

Disable the breakpoint on the selected line. The breakpoint remains on the line of code, but will not stop execution until it is reenabled.

Not available

Breakpoint/Enable Breakpoint

Not available

Not available

Enable the breakpoint on the selected line.

Delete All Breakpoints

Not available

Not available

CTRL+SHIFT+F9

Delete all breakpoints.

Disable All Breakpoints

Not available

Not available

Not available

Disable all breakpoints.

Not available

Add Watch

Not available

Not available

Add the selected expression to the Watch window.