Home > SSIS > Shrink database using SSIS

Shrink database using SSIS


Dear SSIS team,

In SQL Server environment data plays a major role for input, process and output. I addition to that, whenever we insert a row in to a table we need disk space to accommodate that value. Any data in the computer is in the form of files. It is another vast subject to handle files with operating system that is called file management.
I am going to talk about two files called data and transaction log file. Shrinking database is the process of reducing the size of those files. It is not compression. But, it release the free space occupied by the SQL Server database.
In the technical term for the database shrink operation can be used to remove unused pages.
There are different occasions we may have to reduce the size of the file. For instance, assume that you are having a database with 10GB data within. During maintenance period you have moved 5GB to other server for archival or historical data movement from the transactional server and deleted those records from it. In this case, the database file has already been grown up to 10GB. Now, 5GB is filled with data and another 5GB is free. So, you have two options in your hand. You can leave the database as it is or you can reduce the size of the database and give it to operating system.
First task requires no action. But, Second option requires command to do it.
Here is the Shrink Database task in SSIS; you can use this task to shrink the database data and transaction log files.
Implementation
Step 1: Create a package, add Shrink Database task from the tool box to the control flow layout.
Step 2: Click New button to create connection to a database instance.
Step 3: select database(s) that you want to shrink. SSIS will send separate command for each database(s). There are many simplified options available in this dialogue box and have the option to eliminate the databases which are not online. Click ok when you are ready.
Step 4: Configure the rest of the option before completing the process.
Option 1: Check the database size given in the textbox and choose the database for shrinking
Option 2: Give the percentage of free space of the database should have after shrink.
Option 3: Retain freed space within database – Click this option to do so
Option 4: Return the freed space to operating system – Click this option to do so.
Step 5: Click View T-SQL button to see the script
Step 6: Finally, click ok and execute the package
Thank you for reading this article. Visit again to learn new task tomorrow.
Advertisements
Categories: SSIS Tags: ,
  1. John Waclawski
    July 13, 2012 at 12:25 pm

    Is there a way to get the results of the shrink? If so, is there a way to pass them to a Notify Operator Task?

    • July 16, 2012 at 11:17 am

      If you mean the successful completion of Shrink task, then you can use the Green (onsuccess) pipline and connect with Send email task or the Notify Operator Task to send alerts.

      • cowski
        July 16, 2012 at 12:52 pm

        Well, that’s not exactly what I was looking for. I was looking to get the results of the shrink database. I’m familiar with the “Notify Operator Task”, but that just notifies me if it’s successful or not. It doesn’t send the results of the Shrink Database task.

  2. February 26, 2014 at 11:26 am

    shrink database task does not return any result. But DBCC SHRINKDATABASE will return the result set if the truncation happens.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: