Today, I would like to tell you the pain story that I have encountered on LIVE environment. A couple of days ago, I received a request from my client. They needed to restore a database and then run some updates on the database. The database contained data of 8 years from 2009 to 2016. However, they wanted to update data from 2014 to 2015.

The LIVE environment information as below:

OS: Window Server Standard 2012

SQL Server: SQL Server Standard 2014

RAM: 64GB

CPU: 8 cores

D drive: 2TB contains log files

E drive: 2TB contains data files

Data size: 250GB data

Log size: 80GB

Because they wanted to update data from 2014 to 2015, SQL Server Edition is Standard so we could not use logical partition tables. Instead we used physical partition tables, there were 8 tables for 8 years of data and created a view to work on those data. We determined to delete all data from 2009 to 2013 and run SHRINK statement to reduce size of our database. It was excited that data size was only 116 GB and total volume of transaction data was 24 million rows. Then we updated data that we wanted but the updates took more than five hours. It was our nightmare and our client complained this too much obviously.

This is because we run SHRINK statement caused the fragmentation issue and the data was disorderly in data pages. The lesson learn  that I would like to mention in this article we should be carefully when running SHRINK statement even we should avoid running it on LIVE environment.

 

There are many people who experienced on this issue and I am interested in discussing with all of you on how to reduce size of a big database and delete data of large tables.

 

 

Advertisements

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