Shrink Database Task

The Shrink Database task reduces the size of SQL Server database data and log files. By using the Shrink Database task, a package can shrink files for a single database or multiple databases. Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system.

Step by Step shrink Database Task

In Control flow take Shrink Database Task.

Double click on Shrink Database Task. You will get the below screen. Click on new

You will get the below screen. Write the connection name.

Write the server name.

write the user name and password or select Windows NT and click OK.

Click on Data Base you will get four option

Ø  All Database

Ø  System database

Ø  All user database (excluding master , model ,msdb and tempdb)

Ø  These database (List of databases)

According to your need you can select. Click ok.

Before running the package just I have open the SSMS and see the size of that database which one I am going to shrink through package. You can see the size of database is 4.00 MB.

Now I am going to insert some data. I have inserted 50k records in my table. Now the size of my database is become around 120.75 MB.

Now going to delete data from that table..

Still I am getting the size of database is 120.75MB.

Now I am executing the package. Its executed successfully.

Now see the size of the database is 4.00MB.