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
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.