Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ITTrucker
Yak Posting Veteran
64 Posts |
 Posted - 2012-03-28 : 15:51:17
|
| Short: I deleted 40 million rows from a database and I'd like to reclaim the Hard Drive space, the database will not get that large again.Info: Recovery Model is Full, we do nightly full backups and tlog backups every 30 min. A nightly job checks for heavily fragmented Indexes and reorganizes them. (Tara Kizer's Feb 27, 2007 alter index sp) and Auto Shrink is off.Long: We have a software package that has a SQL Server 2008 back end with a bunch of audit tables. We're not running out of space, but I am going to need to restore from our production db into our test environment multiple times over the next month so the smaller the better. The MDF file is about 40GB but it contains millions of records of audit data from 7 years ago. So I created an "audit" database and inserted about 40 million records from our 4 big audit tables (in increments) and then deleted those 40 million records from the tables (in increments) Now I want to reclaim that space from the main database.I've tried doing some reading on the DBCC shrinkfile and DBCC shrinkdatabase but there's some dissenting opinions out there. I was thinking if I ran a DBCC SHRINKDATABASE without any options specified, the pages would get reordered and the space at the end of the file would be reclaimed and if the index's got too fragmented, the job would reorder them to eliminate performance issues. Am I thinking about this correctly? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-03-28 : 15:54:31
|
| Run DBCC SHRINKFILE in small shrink increments until you reach your desired free space inside the data file. I typically shrink down until I hit about 10% free space. Free space is also needed to rebuild the indexes, so it might need more than 10%. Yes there are opinions out there about not running shrinks, but those are regular shrinks that we speak of. A one-time shrink is different and is okay in this situation.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
Deleting records procedure