As a DBA or Database Developer, you may have encountered situations where you need to optimize the performance of your SQL Server. One common scenario is dealing with database fragmentation. In this blog post, we will discuss the impact of shrinking databases and explore alternative solutions to address the business need for smaller backup files.

Recently, during a SQL Server Performance Tuning consultancy, I came across an interesting situation. No matter how much effort I put into reducing fragmentation, the server continued to experience heavy fragmentation. After thorough investigation, I discovered that one of the jobs was continuously shrinking the database – a practice that is generally discouraged.

I have previously shared my experience in a blog post titled “SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server”. Upon removing the incorrect shrinking process, the server started functioning as expected. However, to my surprise, a few days later, I learned that one of the DBAs had reinstated the same DBCC process. When I inquired about the reason behind this decision, the Senior DBA simply stated, “Business Requirement.”

Curious to understand the need behind this practice, I engaged in conversations with the relevant stakeholders. They explained their “business need” in their own words:

“We shrink the database because if we take a backup after shrinking it, the size of the backup file is smaller. As per our business requirement, we need to ensure that the file is as small as possible when we transfer it to our remote server.”

It is important to note that shrinking the database does not affect the size of the backup file. The size of the backup remains the same, regardless of whether the database is shrunk or not. To validate this, we conducted several tests, and the results confirmed our findings.

So, what was the real solution to address their business need for smaller backup files? We introduced them to the concept of compressed backups. I had previously written about this feature in detail in a blog post titled “SQL SERVER – 2008 – Introduction to New Feature of Backup Compression”. Compressed backups not only create smaller file sizes but also improve the overall performance of the database.

By implementing compressed backups, the organization was able to achieve their desired outcome without compromising the integrity and performance of their SQL Server. This solution has been successfully utilized to date.

In conclusion, it is crucial to understand the impact of shrinking databases in SQL Server. While it may seem like a quick fix to reduce file sizes, it can introduce heavy fragmentation and negatively affect performance. Instead, consider alternative solutions such as compressed backups to meet your business requirements for smaller backup files.