Database Shrinking in SQL server 2005

Shrinking a Database in SQL server 2005

Introduction:

One important function of a database maintenance plan is to keep an eye on the size of your database. Database administrators use different techniques to maintain the size of the database.

One of the techniques is known as database shrinking.

In this article we will discuss how to shrink a database in SQL server 2005 and what are the different techniques of reducing primary and secondary databases, and log file size. Plus, what are the features introduced in SQL server 2005 that has helped in reducing the size of databases.

Database Shrinking:

Database shrinking is one of the processes to reduce the size of the database when it grows to a size where it requires more space than available to carry its tasks smoothly.

Many database administrators do not appreciate this way of reducing the size of the database. We will talk about it later when we discuss the disadvantages of database shrinking.

For now we will discuss the facility of database shrink that SQL server 2005 provides us to reduce the size of our database. But before that, we need to understand the size of our database and the disk on which it resides.

Normally SQL server database files are very large; this is why we need to protect our disk from getting filled up.

There are different opinions of experts on this; some believe that when the disk reaches its last five or ten percent of the available space then only its performance are decreased but some believe that it starts to happen even before that.

Broadly speaking, when the size of the disk crosses the 50 percent of its available space, the I/O starts to slow down. When it reaches 75 percent it becomes noticeably slow and when it reaches 90 percent you can notice the disk struggling to function properly.

If your database has a big file, it consumes a good portion of the drive to take hold of its fragmentation; and when you go on defragmenting it, the free space becomes limited.

That is why it is necessary to have plenty of free space on disk and make sure it remains intact by performing defragmentation on a regular basis.

Checking the Database Size:

It is very important to check your database size before you go on making any decision about reducing its size.

Let’s see how we can check the size of our Database:

Select your Database in Object Explorer as shown below:

Now click the new query button to open up new query windows shown below:

Now write the command in the new query window as shown in the image below:

Press Ctrl + r or click on the execute button and you will see the following result:

Here, you can see the following fields:

Database name: showing the name of the database.

Database size: tells the size of the database – 3 MB.

Unallocated space: tells the unallocated space – 0.84 MB.

Reserved: tells the space reserved for the database – 1192 KB.

Data: tells the size of the data file – 520 KB.

Index size: tells the size of index file – 529 KB.

Unused: shows the space which is unused – 80 KB.

This is just a demonstration of how to get the database size information.

Let’s now analyze different file types that a SQL server database has:

Primary data files:

Primary data file is the beginning or basic file of a database and it connects a database to other database files. A database can have only one primary data file. The extension used for primary data file is .mdf.

Secondary files:

These are the data files that are other than the primary data files. Many databases might not have secondary data files, while some databases have more than one secondary data files. The extension used for these files is .ndf.

Log files:

The log files contain all the information that is related to and used for database recovery. Every database always has at least one log file. There are possibilities that a database can have more than one log file. The extension used for log files is .ldf.

Different ways to shrink a database:

There are different ways to shrink a database. We will be discussing a few of them here.

First of all, let’s see how a SQL server database can be shrunk using Object Explorer in the SQL server management studio.

In the Object Explorer, right-click the database that you want to shrink.

Click on tasks, then click shrink, and select databases.

The window will appear as follows:

Now in the above image you can see the database name, below that you can see the size of the database and the available space.

If you see below, there is a check box saying “Recognize files before releasing unused space. Selecting this option may affect performance”.

Check on this option and the box below will be enabled as follows:

Enter the value between 0 – 99. This option specifies how much space you want for the data files after the database gets shrunk.

Click the Ok button and here you got your database shrunk already.

Now, besides shrinking a database, you can also shrink data and log files

Right-click on the database, click Tasks, Shrink, and select Files.

Clicking on the files will show you the following window:

Here you can see a drop-down in which you will find two file types (data and log).

Select the one you want to shrink.

Beneath that, we have file group and database name, and then we have the location where the file resides.

Then we have the current size of the file and the free space available. Then we have shrink actions: if you want to release unused space in the files select release unused space, if you want to specify the size of the file to which you want to shrink it select option two and if you want the data to be migrated in to some other file click the third option.

Now click on the Ok button and your file will be shrunk.

Another way of shrinking the database is by using commands.

You can use DBCC SHRINKDATABASE command. This command shrinks all the file on a database.

You can also use DBCC SHRINKFILE this will only shrink the database file leaving the log and other files as it is.

1. Before using DBCC SHRINKDATABSE you need to determine the space used by the database using the sp_spaceused stored procedure as shown previously.

Assume that techno is the name of a database having two files; the primary data file (techno.mdf), whose size is 150MB and the log file with a name (techno.ldf), which is 40 MB. Let’s say that sp_spaceused tells us that the primary data file has 80 MB amount of data. Also suppose that we want to shrink the primary data file to 100 MB. Now let’s calculate the percentage of free space that will be left after the shrink, 150 MB – 100 MB = 50 MB. Divide 50 MB by 150 MB = 30% which is our target percentage.

1. Now connect the SQL query analyzer and run the following command:
2.    dbcc shrinkdatabase (technodb, ‘target percent’)
3.    The whole techno Database will be shrunk

Second method to shrink database

Now let’s use DBCC SHRINKFILE to shrink the individual techno database files. DBCC SHRINKFILE is more flexible than DBCC SHRINKDATABASE because we can use it only on the desired files of our database without affecting the other files used by the same database. DBCC SHRINKFILE needs a parameter called target size parameter; it describes the final size of the database file.

1. First of all find out the size of the primary data file (technop.mdf), the log file (technolog.ldf), or any other file.
2. Open SQL server query analyzer and run the following command to shrink the desired database

use techno  Go  dbcc shrinkfile (technop, ‘target size in MB’) – go

this will shrink the primary data file

dbcc shrinkfile (technolog, ‘target size in MB’) – go

this will shrink the log file

Disadvantages of shrinking a database:

In the beginning, I told you that there are many experts who don’t agree with shrinking a database. Let’s see what the disadvantages are:

Shrinking a database causes a lot of fragmentation on the disk which as a result creates a bad impact on the system’s performance.

Shrinking a database is an intensive operation and if you are performing it on a large amount of data in a production environment it can cause blocking and your server might crash.

Conclusion:

In this article we have talked about the scenarios of shrinking a database in SQL server 2005. Also, we have highlighted the process of determining the data size, recognizing different database files, and their functions. Plus, we also mentioned some disadvantages of database shrinking. However, database shrink is a very useful option to overcome the size problem and its advantages overcome the disadvantages.