Steve raised a very interesting question; every DBA and Database Developer has already faced this situation. When I read the topic, I felt that I can write several different examples here. Today, I will cover this scenario, which seems quite amusing. This blog post is written in response to T-SQL Tuesday hosted by Steve Jones. Let us discuss about What the Business Says Is Not What the Business Wants.
Shrinking Database
Earlier this year, I was working on SQL Server Performance Tuning consultancy; I had faced very interesting situation. No matter how much I attempt to reduce the fragmentation, I always end up with heavy fragmentation on the server. After careful research, I figured out that one of the jobs was continuously Shrinking the Database – which is a very bad practice. I have blogged about my experience over here SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server.
I removed the incorrect shrinking process right away; once it was removed, everything continued working as it should be. After a couple of days, I learned that one of their DBAs had put back the same DBCC process. I requested the Senior DBA to find out what is going on and he came up with the following reason: “Business Requirement.” I cannot believe this! Now, it was time for me to go deep into the subject.
Moreover, it had become necessary to understand the need. After talking to the concerned people here, I understood what they needed. Please read the exact business need in their own language.
The Shrinking “Business Need”
“We shrink the database because if we take backup after shrinking the database, the size of the same is smaller. Once we take backup, we have to send it to our remote location site. Our business requirement is that we need to always make sure that the file is smallest when we transfer it to remote server.”
The backup is not affected in any way if you shrink the database or not. The size of backup will be the same. After a couple of the tests, they agreed with me. Shrinking will create performance issues for the same as it will introduce heavy fragmentation in the database.
The Real Solution
The real business need was that they needed the smallest possible backup file. We finally implemented a quick solution which they are still using to date. The solution was compressed backup. I have written about this subject in detail few years before SQL SERVER – 2008 – Introduction to New Feature of Backup Compression. Compressed backup not only creates a small filesize but also increases the speed of the database as well.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
This is something that bugs me a lot. Or has bugged me, now I’m in a firm where they (bosses and co-workers) appreciate talent but in my previous job things were a lot different.
I’m a DBA and I’m a programmer, designer, whatever. I have over 10 years experience on the field. One might consider me as an expert on Sql Server, T/SQL and C#/.NET.
Now, why on every time when I say “No, you can’t do that becuase it will cause this” someone, who is most likely not even a DBA or programmer but someone who has “credibility” in the firm, comes and says “But we have always done it like this and this is how we keep doing it”? If one hires an expert why doesn’t one respect expert’s opinions?
If I hire a contractor to do my house wiring I don’t go and tell him how he/she should do his/her job. I know nothing about wiring so who am I to instruct an expert.
Of course I make mistakes from time to time and so does electricians make mistakes but I think our mistakes are a lot smaller ones :)
Thanks for the article. Please also elaborate major benefits of SHRINKDATABASE. And specially the situations when we have no other option other then SHRINKDATABASE .
A very good article. When one hires a skilled person for the job, the firm/business need to value his opinion based on understanding of the business and also share best practices in the industry.
As Pinal said, SHRINK DATABASE is not a best practice. In cases that you encounter no other option, for example, to reclaim space, it would be better to use DBCC SHRINKFILE, and specify the corresponding data or log file. I’d also use the option WITH TRUNCATE ONLY to minimize the time it would take to actually move around the data pages during this operation. It should also be done during non-peak time. Even so, SHRINKFILE on a continuous basis, also not a great option. If the issue pertains to the log file, and your not backing up the tlog regularly, better to keep it in SIMPLE recovery mode (truncate log on checkpoint), to maintain the size of the log. HTH – RP
In my humble opinion, the expert, as a consultant, should be able to translate technical consequences into business consequences. Explaining what his decision means in technical terms, will never ring a bell for someone that doesn’t understand the technique.