We help IT Professionals succeed at work.

MS SQL - Reclaiming physical disk space for a database

TedgCl
TedgCl asked
on
I have a IIS web server logging application that logs download activity is a MSSQL database.  We are typically only interested in the past 30 days of records and our MDF file has grown pretty huge over the years and we are running out of disk space.  I have archived all records older than 30 days making the record space 95% of what is used to be.  I would like to reclaim all of the disk space of the MDF.

I know there is a lot of talk about not shrinking a database.  So what's the best way to reclaim the physical disk space?  Also, almost all of the transactions on this database are INSERT transactions.  

Comment
Watch Question

Commented:
Well, you should not shrink the database as a matter of course, but there are reasons to shrink the database and it appears that this is one of the cases.

The problem with shrinking the database is that it tends to fragment a lot of the indexes and tables due to rearranging of pages to get the pages at the end of the files to shrink.
So the idea, is not to never shrink the database, but to understand what will happen and then to update your database for performance.

So what you can do in this case is to shrink the database and then rebuild all your indexes in the database so that they are defragmented and performance will be back to normal.

Hope that helps.

Commented:
Q:<what's the best way to reclaim the physical disk space?

A: shrink Database files: Data and/or trans log

also if you can have db in simple recovery mode - you can have trans log file smaller ... ot

if you have sql server 2005  or 2008
you may like to use new feature (started from sql 2005) -  Compressed Tables and Indexes ...and after this shtrink database

Creating Compressed Tables and Indexes
http://msdn.microsoft.com/en-us/library/cc280449.aspx

Author

Commented:
DBAduck - What series of commands would you recommend?

Thanks!
Top Expert 2012

Commented:
>>you may like to use new feature (started from sql 2005) -  Compressed Tables and Indexes<<
It is not available in SQL Server 2005 and only available in SQL Server 2008 if you are using the Enterprise or Developer Edition.

Commented:
acperkins

<if you have sql server 2005  or 2008 ...
started from sql 2005>
I did not tell in details all the evaluation path of this ..

But you are right  it is a bit confusing:

;let say in sql 2005 sp2  it is started for data compression
Reducing Database Size by Using Vardecimal Storage Format
http://msdn.microsoft.com/en-us/library/bb508963%28v=sql.90%29.aspx

http://blogs.msdn.com/b/manisblog/archive/2007/08/13/data-compression-with-sql-server-2005-sp2-and-katmai.aspx
http://sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2005-and-2008-Compression.aspx

and in sql server 2008 and up- it evolved in
Compressed Tables and Indexes..
http://msdn.microsoft.com/en-us/library/cc280449.aspx

Commented:
and we do not know what in this case sql server version..

BTW: another way to control index size
Fill Factor

http://msdn.microsoft.com/en-us/library/ms177459(v=SQL.90).aspx
Top Expert 2012

Commented:
>>But you are right  it is a bit confusing:<<
You certainly confused me.

Commented:
all confusions are gone .. it's time to finalize this request
AnujSQL Server DBA
Top Expert 2011

Commented:
I completely agree with @aceperkins,, Like to add a just a gentle note to it, After shrinking your DB dont forget to defragment your indexes and the statistics as shrinking your database makes your indexes fragmented. Also do this at off peak hours.