April 6, 2012 at 3:47 pm
I am a newbie!!! I have a 2008 R2 Standard Ed server with a database that is in Simple Recovery. The database had a large amount of data deleted from it. The current size is 85645M and Space Available is 59271M. After the delete, I was asked to recover the unused disk space to the server for other databases to use.
I was told to shrink the database and rebuild the indexes but that just made the database larger!!!
This is what I did after the records were deleted:
DBCC SHRINKDATABASE (My_DB);
USE [My_DB]
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"
This is what sp_spaceused shows now:
database_name database_size unallocated space
------------------- ------------------ ------------------
My_DB 85645.06 MB 59271.91 MB
reserved data index_size unused
------------- ------------------ ------------------ ------------------
23525664 KB 21828384 KB 1693824 KB 3456 KB
The mdf file now says the initial size is 82247MB and the ldf is 3399MB
I have read a gazillion articles about shrink vs dont shrink but I cant find any simple instructions on how to get unused disk space back from a database. Using the information above, can someone please explain to me what I can do? If you need more info, I will try to give it to you.
April 6, 2012 at 4:48 pm
chumphrey 12211 (4/6/2012)
I am a newbie!!! I have a 2008 R2 Standard Ed server with a database that is in Simple Recovery. The database had a large amount of data deleted from it. The current size is 85645M and Space Available is 59271M. After the delete, I was asked to recover the unused disk space to the server for other databases to use.I was told to shrink the database and rebuild the indexes but that just made the database larger!!!
This is what I did after the records were deleted:
DBCC SHRINKDATABASE (My_DB);
USE [My_DB]
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"
This is what sp_spaceused shows now:
database_name database_size unallocated space
------------------- ------------------ ------------------
My_DB 85645.06 MB 59271.91 MB
reserved data index_size unused
------------- ------------------ ------------------ ------------------
23525664 KB 21828384 KB 1693824 KB 3456 KB
The mdf file now says the initial size is 82247MB and the ldf is 3399MB
I have read a gazillion articles about shrink vs dont shrink but I cant find any simple instructions on how to get unused disk space back from a database. Using the information above, can someone please explain to me what I can do? If you need more info, I will try to give it to you.
Step 1:
DBCC SHRINKDATABASE(database_name, NOTRUNCATE);
This will not shrink the data file, it will simply compact the data pages to the front of the file.
Step 2:
DBCC SHRINKDATABASE(database_name, 5);
This should shrink the data file leaving 5% free space for moderate growth...check it on disk to make sure.
Step 3;
Now rebuild your indexes using the 'for each table' call.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 7, 2012 at 3:13 am
chumphrey 12211 (4/6/2012)
I was told to shrink the database and rebuild the indexes but that just made the database larger!!!
That's expected, the index rebuild requires enough space to create a copy of the index which it then switches in after the rebuild. Transaction log usage can also be high depending on recovery model and index operation. See this link[/url]
Check the fragmentation levels before hand and rebuild or reorg based on what you find 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 5:26 am
You're running those in the wrong order. However, the fact that it grew your database means that's how much space your database needs in order to run the rebuild operations. Don't shrink the database unless you're in an emergency situation. If you're doing it as part of standard managment, you're totally off the track and hurting your system. Stop. Don't do that any more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2012 at 6:46 am
As an extra option you could turn SORT_IN_TEMPDB on, this may offload somewhat to the TempDB rather than using the database filegroup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2012 at 6:54 am
Perry Whittle (4/7/2012)
As an extra option you could turn SORT_IN_TEMPDB on, this may offload somewhat to the TempDB rather than using the database filegroup
Ahhh, you beat me to it. I was just about to apologize to the OP for neglecting to mention that in my Step 3. Allowing the sorts to happen in tempdb will ensure the process achieves the space reclamation. What you and Grant mentioned is my usual canned response to shrinkdb posts, but from the info in the initial post I felt the OP acquitted themselves from the usual response.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 9, 2012 at 9:41 am
THANK YOU! I followed opc's suggestion and reduced the database to 31G!
April 9, 2012 at 9:47 am
You're very welcome, happy to assist 🙂
To drive the point home, once again...remember this was a special case. You said you have read many articles about the perils of shrinking database files, but here is my favorite. It is a portal of sorts, to a set of really great articles about why not to shrink your databases:
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply