Utilities Assess Data-File Usage and Provide Commands So You Can Do Something About It

I created two T-SQL utilities

• Candidate Commands (CandidateFileSizeMgmtCommands.sql); and
• Candidate Commands Plus (CandidateFileSizeMgmtCommandsPlus.sql)

that you can use to manage the size of your data files. They provide not only information about disk and data-file usage but also commands you can use to shrink or grow the size of data files. The commands are based on a target (i.e., percentage of free space) that you specify, so they're tailored to your particular situation.

For example, suppose that your SQL Server machine has multiple databases. You receive a notice that the amount of free disk space on that machine is low. You also receive a notice that the amount of database free space is low for the database named DB. The limited amount of free disk space hampers your ability to quickly grow DB's data file.

In order to grow the data file for DB without depleting all available disk space, you must first free up some disk space by shrinking other data files. To identify which data files are good candidates to shrink, you can use the Candidate Commands utility. Figure 1 shows sample output from this tool.

Figure 1: Sample output from the Candidate Commands utility (click to enlarge)

Although all the columns provide useful information, the key columns are:

• %DataFeeSpace. Specifies the current amount of free space in the data file as a percentage.
• DiskFreeSpace. Indicates the amount of free space (in megabytes) on the physical drive.
• SmallestForTarget. Specifies smallest file size necessary to meet your target percentage.
• CandidateResult. Indicates how much the existing file would increase or decrease if you ran the command in the CandidateCommand column.
• CandidateCommand. Provides the command to produce the desired result based on your target percentage.

Which databases are good candidates for shrinking and growing depends on your goal. For example, suppose you ran the Candidate Commands utility and received the results shown in Figure 1. Which commands you'd want to run depends on what you're trying to accomplish:

• If you're making sure your data files have enough free space, the results show that the free space for the Products database's data files is quite low. There's plenty of disk space on the H and I drives, so you'd want to execute the commands for the Products database to increase the amount of space allocated to its data files.
• If you know there will be a large insertion of data in the Sales database and you want to ensure there's adequate space, the results show that there's plenty of available space for its data files. So, in this case, the best course of action is to not run any of the commands.
• If you need to add a really large database to the server and you want to shrink data files to free up disk space, the results show that shrinking the Sales data files will result in the biggest amount of freed up disk space.

Here's how to run the Candidate Commands utility:

1. Log in to the SQL Server machine on which you want to check data-file usage.
2. Open SQL Server Management Studio (SSMS) and paste the contents of CandidateFileSizeMgmtCommands.sql into a new query window.
3. Modify the target free-space percentage if desired. The default value is 20 percent. If you want a different percentage, find the line
   SET @target = 20
   and replace 20 with the desired target.
4. Execute CandidateFileSizeMgmtCommands.sql.

The Candidate Commands utility doesn't make any data modifications. It merely proposes commands based on the specified free-space percentage target. After you pick a command to execute, you can copy and paste that command into a new query window and execute it. The script will produce a warning if there's insufficient disk space to increase the size of a data file for a given target.

The Candidate Commands Plus utility is similar to the Candidate Commands utility, but differs in two important respects. First, Candidate Commands Plus is designed to be executed against a list of servers. Second, the produced commands have the proper syntax to be executed in one query window against multiple remote servers. Because of these differences, it uses an additional file named CC.sql.

Here's how to run the Candidate Commands Plus utility:

1. Log in to one of your SQL Server machines.
2. Save CC.sql to a location that the SQL Server service can access (e.g., C:\dpg).
3. Open SSMS and paste the contents of CandidateFileSizeMgmtCommandsPlus.sql into a new query window.
4. 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.
5. 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.)
6. 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.)
7. 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.