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

I created two T-SQL utilities

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.

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

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:

Here's how to run the Candidate Commands utility:

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:

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.

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).

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.