jump to navigation

Why is shrinkdatabase not releasing space to the drive? May 7, 2008

Posted by msrviking in DB Administration.
trackback

Back again with something very peculiar. My day started off
with one one of my fellow mates coming up to me.

Here is the conversation

My Buddy: "There is a 19GB table sitting out in one of the
environments instance, and I want to get rid of it."
Me: "Why do you want that table to out of the database, something
wrong?" (I was having several questions, but kept myself drawn into limits
so that I don’t bug him with my questions.)

My Buddy: "I need to release space to one of the drives on which
the database is sitting? Can I shrink only that table after deleting or
truncating the data?"
Me: "No, you can’t shrink a table to reclaim space for the
drive as long as you have other objects sitting in the same filegroup. You
could shrink the table alone if you have only that table in the
filegroup. The other way to reclaim the space could be by dropping the table
and shrinking the files of the filegroup."

Alright, now what! The database physical structure is that there are other
objects along with this 19GB table. So, my buddy went ahead and dropped the
table, and started shrinking the database to reclaim the space. Surprise, it
didn’t work. The database didn’t give back the space to the drive. After
several attempts, some service restarts, and txn log backups my buddy got back
to me with the issue that "It is not happening". I took few minutes
to think thro’ the sequence of shrinking I would do and sent another salvo of
questions "What are the commands you have run? What time did you run? Did
you see any errors after shrinking? What is the current size of the database
and database files? Were there anyone else on the instance during this
maintenance work…” (I am restricting the questions, so that you guys don’t
get psyched of the kind of naive questions I keep asking all the time, to everyoneWink). 
Well here are the answers in the order of questions asked "dbcc
shrinkdatabase(databasename, truncateonly), evening (5 PM – 8 PM), No, ‘x GB –
DB, used space – 45 GB, free space – 28 GB (after dropping the table) – Data
file, used space – 3 GB, free space – 300 MB- txn log’, No".

After I had my fill, I went ahead running this command
DBCC SHRINKFILE (N’dbname’ , 0, TRUNCATEONLY)
GO

and bingo, I have had my best shot. The drive space had gained 18.5 GB space
which didn’t happen thro’ dbcc shrinkdatabase. My buddy was elated and asked me
how did I do it? I just had run this command and it worked out, and I was asked
why dbcc shrinkdatabase didn’t work. OK, now it is turn of others to fire
salvo’s at meCrying.

I did little search on my blog roll and found an interesting article by
PSS team member (Bob Dorr – SQL Server Senior Escalation Engineer) – http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-dbcc-shrinkdatabase-shrinkfile-tabular.aspx

Though this article was on a different issue but it revealed few things for me
(an insight) and here is quick stuff I learnt and thought of sharing with you
guys.

  • The shrinkdatabase command is based on a percentage
    where as shrinkfile is based on a target.  
  • Shrinkdatabase uses the percentage to calculate a
    target for each file but can’t override the minimum file size.
  • ShrinkFile allows shrinking below the minimum file
    size.
  • dbcc shrinkdatabase(‘db name’, tabular) gives you a
    report of a dbcc shrinkdatabase without invoking the shrink actions.
    (report only)

WARNING: As always any undocumented syntax can
change and may not be supported.  This should be used under the guidance
of Microsoft SQL Server Support.

 
The shrinkdatabase command results
in a loop over each of the files in the database.  Using the current free
space of the file and the percentage the shrink target is calculated for each
file.   If the calculated target is less than the minimum size of the
file the error is produced and the shrink attempt aborted for that file.

Based on the last bunch of lines from the PSS blog, I understand that when dbcc
shrinkdatabase was run the file couldn’t shrink because calculated target was
less than the minimum size of the file
(but returned no errors, and this is
a surprise). I am leaving "why no errors didn’t come up?" to other
experts to explain or maybe I shall discover myself someday.

I hope you guys have had a good reading. Let me know your thoughts!

Advertisements

Comments»

No comments yet — be the first.

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

%d bloggers like this: