Click here to get free chapters (PDF) in the mailbox
Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.
Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.
SQL Quiz
Recently, I was contacted by one of my friends who operated a large database. He told me he was running out of disk space because some tables with many indexes were unnecessarily created. Even after truncating the table, the space was not given back to the OS. When does SQL Server give the space back once data is deleted?
Link to participate in SQL Quiz
Notes of Vinod Kumar
There are multiple options, but to get the space back, but what are your other options to explore to reclaim the space and yet not get into fragmentation problems. Let me give you a hint.
Notes of Pinal Dave
There was a time when hard drives were expensive and space was the most important asset. I remember having my very first computer with only 2 GB of hard drive and 16 MB of RAM. At that time, space was truly a very important concept. File splitter was very important as we needed to fit every thing in multiple floppies. But in the modern age we have today, the once-expensive hard drives became so much cheaper, which led the importance of the space to become not as much as it was years back. In spite of this, I still see lots of efforts in the database world to reclaim the space back from the database. I often come across people become worried about keeping the database size as small as possible. Their worries made them attempt DBCC ShrinkDB and other tricks that are seriously not recommended. I think today’s question is in this similar line. We all have faced a situation when the table is deleted and yet the space is not released to OS by SQL Server. Any idea why this occurs?
Additional Notes
SQL Server Interview Questions and Answers ISBN: 1466405643 Page#136
SHRINKDATABASE For Every Database in the SQL Server
Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
Reclaim Space After Dropping Variable-Length Columns Using DBCC CLEANTABLE
Data and Page Compressions – Data Storage and IO Improvement
Prize
There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.
Link to participate in SQL Quiz
Note: SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.
Reference: Pinal Dave (https://blog.sqlauthority.com)
