MS SQL - Reclaiming physical disk space for a database

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.  

TedgClAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DBAduck - Ben MillerPrincipal ConsultantCommented:
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.
0
Eugene ZCommented:
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
0
TedgClAuthor Commented:
DBAduck - What series of commands would you recommend?

Thanks!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>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.
0
Eugene ZCommented:
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
0
Eugene ZCommented:
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
0
Anthony PerkinsCommented:
>>But you are right  it is a bit confusing:<<
You certainly confused me.
0
Eugene ZCommented:
all confusions are gone .. it's time to finalize this request
0
AnujSQL Server DBACommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.