Here's how to run the Candidate Commands Plus utility:
- Log in to one of your SQL Server machines.
- Save CC.sql to a location that the SQL Server service can access (e.g., C:\dpg).
- Open SSMS and paste the contents of CandidateFileSizeMgmtCommandsPlus.sql into a new query window.
- Modify the target free-space percentage if desired. The default value is 30 percent. If you want a different percentage, find the line
SET @target = 30
and replace 30 with the desired target.
- Modify the location of the CC.sql file. Find the line
SET @file = N'c:\dpg\CC.sql'
replace c:\dpg\CC.sql with the location in which you placed CC.sql. It must be a fully qualified pathname. (Leave in the letter N and the single quotes.)
- Modify the list of servers to be inserted into the @Servers variable. Find the code
INSERT @Servers
SELECT N'Server1' UNION ALL
SELECT N'Server2' UNION ALL
SELECT N'Server3' UNION ALL
SELECT N'Server4' UNION ALL
SELECT N'Server5'
Replace Server1, Server 2, and so on with the names of your servers. (Leave in the letter N and the single quotes.) You can increase or decrease the number of servers as desired. (No matter the number, the last SELECT statement should not include the UNION ALL clause.)
- Execute CandidateFileSizeMgmtCommandsPlus.sql.
The output generated by the Candidate Commands Plus utility is similar to the output generated by the Candidate Commands utility. However, the candidate commands generated by Candidate Commands Plus are designed to be executed in SQLCMD mode, so they all start with :connect followed by the appropriate server name, as Figure 2 shows.
Figure 2: Sample candidate commands from the Candidate Commands Plus utility (click to enlarge) |
 |
After you decide on which commands to use, copy and paste the desired commands into a new query window, adding carriage returns where appropriate (see Figure 3).
Figure 3: Running commands in a query window in SQLCMD mode (click to enlarge) |
 |
Execute the commands in SQLCMD mode. By running the commands this way, you can execute multiple commands against multiple servers without needing to constantly change server connections.
The Candidate Commands and Candidate Commands Plus utilities let you quickly assess your available disk space and available data-file space. These scripts decrease human error by calculating the appropriate new data-file size value based on the target you provide. Further, with Candidate Commands Plus, you can quickly review disk and data-file usage on hundreds of SQL Server machines and execute as many commands as you desire against multiple servers in one query window. You can download CandidateFileSizeMgmtCommands.sql, CandidateFileSizeMgmtCommandsPlus.sql, and CC.sql by clicking the 102295.zip hotlink near the top of the page. The scripts have been tested on SQL Server 2008 and SQL Server 2005 machines.