Using Volume Shrink with Database files (Part 1)

Windows 2008 and the new LUN Shrink feature

-by Michael Doherty

With the introduction of Microsoft Windows Server 2008 comes a new Disk Manager feature which gives the ability to ‘shrink’ a Logical Volume!

This can be beneficial in situations where a volume has been oversized and as part of a consolidation or maintenance program a realization of space saving on the storage system is requied. This new flexibility allows an organization to reclaim potentially large amounts of storage space.

The functionality to reduce the size of a LUN has been around for some time on storage systems such as the HP StorageWorks EVA, and using tools such as HP StorageWorks Command View EVA this task is extremely simple.

Now this functionality can be used in tandem with the operating system to achieve practical space saving benefits.

Shrink Database

If the oversized volumes were created to host database files, and these files can be also reduced in size, true space saving activities and now be conducted to realize worthwhile benefits.

There are many situation where a database has grown beyond the ideal size.

For example when a database has been created and the default setting of "Enable Autogrow' turned on have been permitted, and if the volume size can accommodate this, the grow of the database can sometimes be alarming!

Situations like this can now be easily addressed by starting with the reduction in size of the database.

The size of a database can be reduced in one of two ways:-

• Shrink the database
• Shrink the database files

When it come to considering the shrinking the database as a single entity you need to be aware of some limitations.

The database cannot be made smaller than the defined minimum size of the database. The minimum size is the size specified when the database is originally created, or the last size explicitly set by using a file size changing operation such as DBCC SHIRNKFILE or ALTER DATABASE. For example, if a database is originally created with a size of 10 MB in size and grows to 100 MB, the smallest the database can be reduced to is 10 MB, even if all the data in the database has been deleted.

So a usage case when combining logical volume shrink and database shrink would be if a storage LUN of 10GB had been created for an originally defined database size of 1GB disk and a transaction log of 200MB. Over time the database has grown to 2.5 GB and the desire is to now shrink the database due to the fact that a of data has been changed and remove.

The basic approach to achieve this would be as follows: -

1. Reduce the database size via the SQL Server Management Studio (SSMS) or by T-SQL (see figure 1)

2. Shrink the logical volume via Windows Disk Manager

3. Reduce the size of the LUN via Command View

Figure1, using SSMS to ‘shrink’ the database.

Shrink Database Files

For situations where it is practical to reduce the size of the database beyond the original defined size, there is a second technique which will accommodate this.

The actual database files can be targeted individually and their size can be reduced independently of each other so resulting in a reduced footprint of the overall database. Once again after this is done the space saving can be realized on the storage system.

The basic process to achieve this would therefore be as follows: -

1. Execute the DBCC SHRINKFILE command on the database data files.

2. Execute the DBCC SHRINKFILE command on the database transaction log files

3. Shrink the logical volume via Windows Disk Manager

4. Reduce the size of the LUN via Command View

The actual commands to be executed which target the database files are as follows:-

For the data files the following command works: -

DBCC SHRINKFILE (Shrinker, 2000)

‘Shrinker’ is the database name and ‘2000’ is of the required new file size in MBs.

As for the Transaction Log, the procedure is slightly different as the Recovery model has to be altered in the process so as to allow the shrink.

The following code will achieve this: -

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE Shrinker

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (Shrinker_Log, 1000);

GO

-- Reset the database recovery model.

ALTER DATABASE Shrinker

SET RECOVERY FULL;

GO