Question :
I have a database, that due to some silly code issues has grown hugely. It is now sitting at 24GB, most of it is very unnecessary logging information (system generated debug information).
My server lives on a cloud hosted server. I am now sitting with 2 issues because of the file size:
-
I pay for storage. I am happy to pay for my core business, but paying for silly data seems….silly
-
My backups are now huge also. I do nightly full backups, and then ship them off to an FTP server. This process is taking longer and longer.
Given my issues, is shrinking so bad? I will rebuild all my indexes.
I did a dry run, and brought my database down to 6GB.
This guy (who I have always trusted as my SQL guru), says it’s bad….mkay
“SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance”
Answer :
Looks like you are already aware of the risks associated with shrinking so I wont go over those again.
As a once off due to a bug or some form of large growth that will only happen once then doing a one time shrink is fine.
Do the shrink, rebuild / reorg the indexes and that should be it.
Is your database in SIMPLE or FULL recovery? If Full make sure you are taking frequent transaction log backups to keep the log files down in size.
Given my issues, is shrinking so bad? I will rebuild all my indexes. I did a dry run, and brought my database down to 6gb
Most of the advice given on the Internet is copied and is disseminated, while not reading the whole topic carefully. There is no doubt that shrinking of data files is bad, but if you ask any SQL guru he would always say, “yes I have shrunk the data file”. He will also tell you the scenario that “forced” him to do so. Like if you have a huge database and you are archiving old records. So you moved data out and deleted the old data from the database. Now a lot of free space is there and you need it immediately; in this case you have no option but to shrink.
In your case you would have ended up paying a large amount for files which grew because of a silly code issue. In your case you can shrink it. Its also good that you are aware that shrinking causes fragmentation and you need to rebuild the fragmented indexes.
The whole motto is do not make shrinking a habit, it should always be your last resort to reclaim space
I suggest you read Paul Randal’s Article on why shrinking is bad. He has pointed out in his article other way around to shrinking, which you can follow if you face similar issues later.