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.
To run SQLDIAG on a clustered SQL Server 2000 instance, follow these steps:
- Open Cluster Administrator.
- Create a New Resource. To create a New Resource, follow these steps:
- Right-click the Disk Group that has the SQL Server instance, point to New, and then click Resource.
- Type a name in the Name box.
- You can type a description in the Description box. However, the description is optional.
- In the Resource Type box, click to select Generic Application.
- In the Group box, click to select the Disk Group that owns the SQL instance.
- Clear the Run this resource in a separate Resource Monitor check box.
- Click Next.
- 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.
- 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.
- In the Generic Application Parameters dialog box, type Cmd.exe in the Command line box.
- Type the following in the Current Directory text box:
Current Directory = %WINDIR%\System32Note You must enter the actual path of the windows directory.
- Click to select the Allow application to interact with desktop check box.
- Check the Use Network Name for computer name check box.
- Click Next.
- In the Registry Replication dialog box, click Finish.
- After you save the resource, you can edit the properties for the resource. On the Advanced tab for the resource, click Do not restart.
- 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.
- 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.
- Run SQLDIAG.exe. To run SQLDIAG.exe, in the command shell window, type the following, and then press ENTER:
sqldiag -Iinstancename -Ooutputfile -Uuser –PpasswordNote Omit the -I option to connect to the default clustered SQL Server 2000 instance.
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:
- 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.
- 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%
- 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.