Purge the records and shrink the database using SSIS

We have a requirement that we need to Purse the records from the table. Records are in huge. We can’t use the truncate command. We are using Delete command to delete the records from the table. When we are deleting the records, even there is no record in the table but it occupies space in db.

See the below demo.

We have a database Name Sales with one table Sales_Archive as Below

  

There is no records in this table.

See the size of this database.  

Now we are loading 1500K records in the table.

 

See the size of the database now.

  

Now we are deleting the all records.

  

Now see the size of the database after deleting the all records.

  

See there are no records in the table but it occupies space in db.

After shrink the database size of the db.

   

In this case we need to shrink the database.

Where are creating the packages which run on monthly and shrink the database.

Taking execute SQL Task to delete the Specific records.

We are having 3000K records as of now

  

These records for two months.  Size of the database.

  

Now we are deleting the records form the table for last month.

  

Running this package now.

  

Package executed successfully.

See the size of the database.

 

Now we need to shrink the database.

We can do it by two ways.

·         Using Shrink Database Task

·         Using Execute SQL task (Using DBCC SHRINKDATABASE)

Read more about :  Shrink Database Task

                https://bageshkumarbagi-msbi.blogspot.com/2014/12/shrink-database-task.html

We are taking the Execute SQL Task.   

Package is ready to run

  

Now running this package.  

Package is executed successfully.

See the size of the database.

  

Currently records in the table.  

Thanks for reading!