SQL SERVER – Reclaiming Space Back from Database – Quiz – Puzzle – 28 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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 (http://blog.sqlauthority.com)

About these ads

2 thoughts on “SQL SERVER – Reclaiming Space Back from Database – Quiz – Puzzle – 28 of 31

  1. These were the posts I had done relating to this topic few yrs back. Not sure whether this is what is the answer expected :) Looking fwd to learn from you both on what else can be done to reclaim the space. Thanks in advance.

    1. Reclaiming a table space after dropping a column [With Clustered Index] – http://vadivel.blogspot.com/2007/10/reclaiming-table-space-after-dropping.html

    2. Reclaiming the table space after dropping a column [Without Clustered Index] – http://vadivel.blogspot.com/2007/10/reclaiming-table-space-after-dropping_01.html

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s