How to run the SQLdiag utility on a clustered instance of SQL Server or on virtual SQL Server (233332)

MORE INFORMATION

SQL Server 7.0 Enterprise

To retrieve SQLDIAG and WINMSD information from a clustered SQL Server 7.0 server, follow these steps:

1. Connect to the virtual server using Query Analyzer.
2. Run the following command to obtain the SQL Server information:

   xp_cmdshell 'shared drive\mssql7\binn\sqldiag -E -O local drive\SQLdiag.txt'
   					

3. Run the following from a command prompt to get the system information:

   winmsd \\<nodename> /a /f

   Executing the code creates a file named Nodename.txt in the directory from which WINMSD was executed. For more information about WINMSD parameters, run the following from a command prompt:

   winmsd /?

   On a computer that is running Microsoft Windows 2000, the correct syntax for WINMSD is:

   winmsd /computer mycomputer /report myreport.txt
   					

SQL Server 2000 Enterprise Edition

This articles presents three options for for running the SQLDIAG utility on a clustered SQL Server 2000 instance.

Option 1

To run the SQLDIAG utility on a clustered SQL Server 2000 instance, follow these steps:

1. Connect to the virtual SQL Server by using Query Analyzer.
2. Run the following command to obtain the SQL Server information:

   xp_cmdshell 'full path for the Binn folder\sqldiag.exe -Iinstancename -Ooutputfile -E -C'

   Note Omit the -I option to connect to the default clustered instance of SQL Server.
   
   For more information about the SQLDIAG utility and its arguments, see the "sqldiag Utility" topic in SQL Server Books Online.
   
   The advantage to running the SQLDIAG utility by using the xp_cmdshell extended stored procedure is that you do not have to be logged on locally to the active node of the cluster. For example, assume that you have a named virtual SQL Server 2000 server: VSQL2\INST2 (where the SQL Network Name is VSQL2 and the SQL Server instance name is INST2). The full path to the BINN folder is C:\Program Files\Microsoft SQL Server\MSSQL$INST2\Binn. To run the SQLDIAG utility, open Query Analyzer. Connect to VSQL2\INST2, and then run the following command:

   exec master..xp_cmdshell ' "C:\Program Files\Microsoft SQL Server\MSSQL$INST2\Binn\sqldiag.exe" -I INST2 -E -C'

If you are logged on locally to the active node of the cluster, you can follow these steps to run the SQLDIAG utility from the DOS command window:

1. Open a DOS command window on the node on which the clustered SQL Server 2000 instance is running.
2. Move to the BINN folder. By default, the BINN folder for a clustered SQL Server 2000 default instance is C:\Program Files\Microsoft SQL Server\MSSQL\BINN. The default BINN folder for a named instance is C:\Program Files\Microsoft SQL Server\MSSQL$NAMEDINSTANCE\BINN.
3. Type set _CLUSTER_NETWORK_NAME_=SQL NETWORK NAME, and then press ENTER.
   
   Note The SQL NETWORK NAME is the SQL Server virtual name for a named instance. This is only the first part of the name. For example, if the instance name is VSQL2\INST2, the SQL NETWORK NAME is VSQL2.
4. Type sqldiag -Iinstancename -Ooutputfile -E -C, and then press ENTER. If your Windows account is not mapped to the sysadmin role in SQL Server, you can replace -E with -Uuser_name -Ppassword and pass in an account that is mapped to the sysadmin role.
   
   Note Omit the -I option to connect to the default clustered SQL Server 2000 instance. For more information about the SQLDIAG utility and its arguments, see the "sqldiag Utility" topic in SQL Server Books Online.
   
   EXAMPLE
   
   For this example, assume that your SQL Network Name is VSQL2, and that the SQL Server instance name is INST2. To run the SQLDIAG utility on your clustered SQL Server 2000 named instance, follow these steps:
   1. Open a DOS window.
   2. Type the following commands on separate lines:

      CD C:\Program Files\Microsoft SQL Server\MSSQL$INST2\Binn
      
      set _CLUSTER_NETWORK_NAME_=VSQL2
      
      sqldiag -I INST2 -Oc:\sqldiag_INST2.txt -E -C

The next two options require you to add resources to the Disk Group that owns the instance of SQL Server. Microsoft recommends that you take the resource that owns the instance off line, and that you delete it when the SQLDIAG output has been collected. If the resource is not removed, then when a failover occurs, or when the group is moved, the resource will be brought online and a SQLDIAG.txt file will be created. If you do not delete the resource, service pack installations may fail.

To run SQLDIAG and connect to a specific virtual server, you must add the program to the disk group that owns that instance of SQL Server. Additionally, because SQLDIAG runs in a command window, the resource is started on the node that owns it. If no output is generated, SQLDIAG might be connecting through a local pipe. If the cluster has failed over to another node before running SQLDIAG, the local pipe connection fails and you do not get any SQL Server information.

Option 2

To run SQLDIAG on a clustered SQL Server 2000 instance, follow these steps:

1. Open Cluster Administrator.
2. Create a New Resource. To create a New Resource, follow these steps:
   1. Right-click the Disk Group that has the SQL Server instance, point to New, and then click Resource.
   2. Type a name in the Name box.
   3. You can type a description in the Description box. However, the description is optional.
   4. In the Resource Type box, click to select Generic Application.
   5. In the Group box, click to select the Disk Group that owns the SQL instance.
   6. Clear the Run this resource in a separate Resource Monitor check box.
   7. Click Next.
   8. The Possible Owners dialog box appears. By default, both nodes are available to bring the new resource on line. If this is not the case, it is a possible indicator of a problem node. When satisfied with possible owners, click Next.
   9. In the Dependencies dialog box, select SQL Network Name (Desired Instance) from the Available Resources list. Click Add to add the selected resource as a dependency, and then click Next.
   10. In the Generic Application Parameters dialog box, type Cmd.exe in the Command line box.
   11. Type the following in the Current Directory text box:

       Current Directory = %WINDIR%\System32

       Note You must enter the actual path of the windows directory.
   12. Click to select the Allow application to interact with desktop check box.
   13. Check the Use Network Name for computer name check box.
   14. Click Next.
   15. In the Registry Replication dialog box, click Finish.
3. After you save the resource, you can edit the properties for the resource. On the Advanced tab for the resource, click Do not restart.
4. Bring the generic application online. To bring the resource online, right-click and then click Bring Online. A command shell (command prompt) will open in the background. If the New Generic Application starts online without a command window, make sure that you are on the node that owns the SQLDIAG resource, and that the Allow application to interact with desktop option is selected in the Properties of the generic application.
5. Locate SQLDIAG.exe. To do so, in the command shell window, change to the %SQL_DIR%\BINN folder. By default, the path is Program Files\Microsoft SQL Server\MSSQL$%INSTANCE_NAME%\BINN.
6. Run SQLDIAG.exe. To run SQLDIAG.exe, in the command shell window, type the following, and then press ENTER:

   sqldiag -Iinstancename -Ooutputfile -Uuser -Ppassword

   Note Omit the -I option to connect to the default clustered SQL Server 2000 instance.

Option 3

Terminal services does not currently support the remote execution of a Microsoft Windows NT command shell that interacts with the desktop. Therefore, you must use additional measures to generate the SQLDIAG output from a remote location. One solution is to create a command file to run SQLDIAG by using the options you want, and to then host it as a generic application on the cluster. Use these steps to create and host a command file to run SQLDIAG from a remote source:

1. Create a directory and a new text file that is named SQLDIAG.cmd on the clustered drive that hosts the instance of SQL Server you want. For example, S:\sqldiag.
2. Include the following contents, and edit as appropriate (any paths or options) that are required for your environment:

   Sqldiag.exe /I%instancename% /E /O%output path and filename%

3. In cluster administrator, create the generic application by using steps 1through 2j under "Option 2." For step 2j, use the following parameters:
   • Command line = cmd.exe /k s:\sqldiag\sqldiag.cmd
   • Current directory = C:\Program Files\Microsoft SQL Server\MSSQL$VINST01\Binn
     
     After you save the resource, edit the properties for the resource. To do so, on the Advanced tab for the resource, click to select Do not restart.

Bringing the resource online runs the SQLDIAG command as specified in the SQLDIAG.cmd text file. The /k option keeps the virtual command shell open so that it can taken off line again gracefully.

SQL Server 2005 Enterprise Edition

For information about how to connect to and run SQLDIAG.exe against clustered SQL Server 2005 instances locally and remotely, search for "SQLdiag Utility" in SQL Server 2005 Books Online.