Additional Ways to Monitor the MIIS Database

Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2

In addition to using performance counters and alerts, MIIS 2003 and SQL Server 2000 provide the following ways to monitor the MIIS database and SQL Server 2000 system databases:

  • Use the error log in SQL Server Enterprise Manager.

  • Use Event Viewer to monitor SQL Server events recorded in the Windows application log.

  • Use Current Activity in SQL Server Enterprise Manager for ongoing monitoring.

  • Use SQLDiag.exe to collect detailed information.

  • Use a Query Analyzer session if SQL Server Enterprise Manager is unavailable.

Use the Error Log in SQL Server Enterprise Manager

You can use the error log in SQL Server Enterprise Manager for ongoing performance monitoring. The SQL Server error log enables you to determine how well the SQL Server 2000 installation that hosts the MicrosoftIdentityIntegrationServer database is functioning.

To view the SQL Server error log

  1. In SQL Server Enterprise Manager, expand a server group, and then expand a server.

  2. Expand Management, and then expand SQL Server Logs.

  3. Click the SQL Server log that you want to view.

Error log details appear in the details pane.

Use Event Viewer to Monitor SQL Server Events

Event Viewer allows you to monitor SQL Server events recorded in the Windows application log. This log is separate from the SQL Server error log. SQL Server 2000 messages can be identified by their source, MSSQLSERVER or SQLSERVERAGENT.

To view messages in the Windows application log

  1. Open Event Viewer.

  2. In the console tree, click Application Log.

  3. In the details pane, right-click an event, and then click Properties.

Use Current Activity for Ongoing Monitoring

You can use Current Activity in SQL Server Enterprise Manager for ongoing monitoring. Current Activity displays a snapshot of information regarding processes, user activity, and locks held by processes and on objects. Because using this tool requires no overhead, it can be helpful to view Current Activity when you encounter a problem state. Note that the information in Current Activityis only a snapshot; for ongoing performance monitoring, you must refresh this snapshot regularly.

You can use Current Activity to do the following:

  • Monitor blocked and blocking transactions.

  • Monitor users that are currently connected and their last executed statement.

  • View all locks by database object.

  • Terminate a selected process or send a message to the user who is executing a problematic transaction to terminate the transaction.

Important

Keep in mind that if you decide to terminate a process, the command might require a lengthy rollback. Hence, use this feature sparingly.

To view the Current Activity window

  1. In SQL Server Enterprise Manager, expand a server group, and then expand a server.

  2. Expand Management, and then expand Current Activity.

  3. Click Process Info.

The current server activity is displayed in the details pane.

Use SQLDiag.exe to Collect Detailed Information

You can gather detailed information about the current state of SQL Server 2000 — including current user information, DLL versions, configuration information, and database size information — by using the Sqldiag.exe tool, which is located in drive:\ProgramFiles\Microsoft SQL Server\Mssql\Binn\.

To run this tool, type the following at the command line:

sqldiag -O filename -X

Table 6   Parameters for the Sqldiag Command

Parameter Description

-O

Directs all Sqldiag.exe output to a single file.

filename

Specifies the name of the file where you want to direct the command output.

-X

Excludes error logs.

For more information about Sqldiag.exe parameters, type sqldiag /? at the command line.

Sqldiag.exe can take up to a few minutes to complete. When Sqldiag.exe completes successfully, it writes a file named filename, which lists detailed SQL Server 2000 information, to the server.

Use a Query Analyzer Session if SQL Server Enterprise Manager Is Unavailable

There might be times when SQL Server Enterprise Manager cannot access SQL Server 2000 but a Query Analyzer session can. In these cases, you can use the SP_WHO SQL Server stored procedure to discover information about current connection count and connection details.

For more information, see “SP_WHO” in the Microsoft SQL Server 2000 Books Online, which you can install during SQL Server 2000 installation and then access from the Start menu through All Programs. Optionally, see “Getting Started with SQL Server Books Online” on the Microsoft MSDN Web site at https://go.microsoft.com/fwlink/?LinkID=7107.