SQL stress test: Simulate full database

Simulating a full database is a beautiful stress test, but how the heck can this be done quickly with SQL Server?

A full database is likely never encountered during development and probably completely forgotten during testing (it was in our case!).

It will likely cause numerous issues for your software under test, since it's usually forgotten.

Here's 4 easy steps to simulate a full SQL Server database without spending loads of time and resources filling up your database. This test is designed to mimic a full disk drive.

1. Prepare the database
2. Create a TrueCrypt volume
3. Copy the database to the TrueCrypt volume
4. Bring the database back online

The idea is to put a copy of your database on a TrueCrypt volume just big enough to hold your database.

As you add records to this database, it fills up quickly. Below is each step in detail.

Step 1: Prepare the database

• Take the database offline. The simplest way to do this is to run the following SQL.

• Make a copy of the original database.
  To find the location of your database in SQL Server Management Studio, simply right-click on your database and select Properties.
• Make a copy of all files associated with your database and store them in a separate directory. The idea is to keep a copy of your database in case something goes wrong. This is highly recommended.
• Bring the database back online. Running the following SQL will do just that.

• Shrink the database.
  Right-click on your database in SQL Server Management Studio. Then select Tasks > Shrink > Database.
• Database files are frequently bigger than the space the records occupy. The goal of this step is to make the database files about the same size as the space needed to store the records.
• This step also makes the database files a bit easier to deal with, since the shrunken files will be smaller. In addition, it means you'll waste less time creating a TrueCrypt volume as a container for a gazillion GB of database files.

Step 2: Create a TrueCrypt volume

• Determine the size of your shrunken database files. This size determines the size of your TrueCrypt volume.
• Launch TrueCrypt.
• Click Create Volume.
• In the TrueCrypt Volume Creation Wizard, select Create an encrypted file container. Click Next.
• Choose Standard TrueCrypt volume. Click Next.
• Enter any filename for the Volume Location. Click Next.
• Use the defaults for the Encryption Options. Click Next.
• For the Volume Size, enter a size nearest to the size of the database files. Click Next.
• For the Volume Password, enter a password. Click Next.
• Click Format.
• Using TrueCrypt, mount the volume.

Step 3: Copy the database to the TrueCrypt volume

• Take the database offline. Use the SQL from Step 1.
• Detach the database.
  In SQL Server Management Studio, right-click on your database. Select Tasks > Detach... Click OK in the Detach Database dialog. Detaching the database allows you to re-attach the database at a different location.
• Copy the shrunken database files to the TrueCrypt volume.

Step 4: Bring the database back online

• I got errors when trying to attach a database using my own username. To work-around this, run SQL Server Management Studio as Administrator.
• Attach the database in the TrueCrypt volume.
  In SQL Server Management Studio, right-click on Databases, select Attach... In the Attach Databases dialog, click Add. Select the MDF file for the shrunken database you copied to your TrueCrypt volume.
• Bring the database online. Use the SQL from Step 1.

There you have it!

You now have an identical copy of the database running in a volume with just enough space. Adding just a few more records will make the database full.