SQL Server DBCC SHRINKDATABASE Not Recovering Unused Space

Currently have a 263GB SQL Server 2005 DB used in production MS environment.  Each month we truncate more than half the tables from the DB and run a SHRINKDATABASE('mydatabase', TRUNCATEONLY) to recover the unused space and use the smaller DB for our Development environment. Normally is shrinks down to about 165GB.  This time, however, using the same process, we cannot shrink the DB physical size at all.  We receive no errors, but the size remains at 263GB.  We have tried running using SHRINKDATABASE('mydatabase',10) and we have tried using SHRINKFILE commands as well with no luck.  Any ideas why it won't shrink?
lsig505Asked:
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.

Mark WillsTopic AdvisorCommented:
Might be worthwhile going into SSMS, right click on the database, go into tasks, then shrink, then files. Have a look at both DATA and LOG (drop down). See what it says about allocated space / available space there. See if you can shrink it from there - use the radio button where you can specify size, and do the reorganise of pages. The DATA file will take some time, so be prepared... Best to take a full backup before you do.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Assuming its a database in full recovery mode, this should work (DBName = database, DBFile_log = logical filename in database to shrink

if DBCC SHRINKFILE (DBFile_log, 10) doesnt work then set in simple mode instead

Resolution, set SIMPLE MODE, shrink, set FULL MODE, and do a backup

If this does it, you might (read should) look into your logbackup routines. Maybe your DB should be in simple recovery mode to begin with.

/Marten
USE [master]
GO
ALTER DATABASE [DBName] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [DBName] SET RECOVERY SIMPLE 
GO
 
USE [DBName]
GO
DBCC SHRINKFILE (DBFile_log)
GO
 
USE [master]
GO
ALTER DATABASE [DBName] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [DBName] SET RECOVERY FULL 
GO
 
 
--BACKUP DATABASE [DBName]
use master
BACKUP DATABASE [DBName] 
 TO DISK = '\\networkshareOrSomething\DBName.bak'
   WITH FORMAT,Stats 5;
GO

Open in new window

0
dportasCommented:
What's the point of shrinking it each month? Assuming you either are running SIMPLE recovery or you are doing long backups then unused space will be reused anyway and shrinking presumably won't save you any space from month to month.

Shrinking and growing are both very expensive operations that cause internal and external fragmentation. Better to leave the data and log files at a fixed size and don't shrink or grow them.
0
Ultimate Tool Kit for Technology Solution Provider

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 now.

lsig505Author Commented:
Have tried using SSMS for both Log and Data files, however, the data file will still not shrink.  The log file is not a problem.  Database is in simple recovery mode.  Still won't shrink the data file yet we've never had this issue in the past.
0
lsig505Author Commented:
We shrink the DB each month in order to restore a version to a server which does not have the space available to restore a full copy of the production database.  We are not shrinking the production database, but a restored copy with the non-essential tables dropped.  Agreed that it is better to leave the production DB as is.
0
Mark WillsTopic AdvisorCommented:
What does the "available" space say in SSMS ?
0
lsig505Author Commented:
SSMS shows 221GB space available
0
grayeCommented:
Hang on a second.... let's do that shrink operation again using a Query window in the Management Studio.   But this time, after it's run, switch to the "Messages" tab.... that's where the error messages are.
Recall that most of the shrink commands do not "error out" when they fail... instead they just issue messages.   So, it's a bit deceptive to see "Action completed successfully" and assume that it actually did anything.  Generally speaking the text in the Messages tab are a bit cryptic, so you might have to post 'em here for us to see.
0
lsig505Author Commented:
We ran the shrink command in query window several times--only message returned on the message tab was "command completed successfully".
0
grayeCommented:
Are you sure?... that's the message that typically is in the Results tab, with the "other" more detailed message in the "Messages" tab
0
ThorSG1Commented:
I'm having this issue as well.  The only thing I've come up with is the database was detached and reattached a some point.  It appears it won't let me shrink it below the size it was attached.
Do you know if the database has been detached and reattached at some point?
I think SQL gets confused and sets the inital size to the size you are attaching at that point not the origional size from before it was detached.
0
lsig505Author Commented:
That's a good thought, because it definitely feels as though it thinks it's not supposed to shrink this DB any further.  We've tried so many things, but I believe the first time we tried the ShrinkDatabase, it had not been detached and reattached.  I believe through the course of trial and error, we have tried detaching and reattaching the DB and then running ShrinkDatabase.  Neither way proved successful unfortunately. We are continuing to research and make attempts, in the meantime, we're getting more space so we can restore the full size DB.
0
lsig505Author Commented:
In response to graye....I don't have a copy of the Messages tab saved, however, it is also checked when the command is run (versus relying only on the Results tab) and we've been getting the standard "2 rows affected...command complete successfully..." message, but no indication that anything did not work or has a warning or error.  I almost wish we did get an error/warning because at least we'd have something to track down.
0
Mark WillsTopic AdvisorCommented:
About the initial size, had a similar thought last night, so created a database with a fixed min size and restoreed into it - could still shrink below initial size, also tried setting initial size, and same thing. The ONLY one I have come up with is a database in a directory where I no longer have write access (ie different user). Can be easily checked by using SSMS and right click on prperties, and try to extend the initial size a few meg bigger than the current max - happens fairly quickly if in fact it happens, or does not error (for my test, didn't happen, but also didn't error). Might be worth checking the Windows properties...
0
Mark WillsTopic AdvisorCommented:
Would also be inclined to try a SQL full backup, delete the old db, create new db and restore into that db, using the override and also changing the filenames to the actual (new) db names (in options in the restore function) - does that make any sense ?
0
lsig505Author Commented:
Actually, we had tried a full backup, deleting the old DB and recreating a new one to restore to and it still didn't resolve the issue.
However, we just found something that DID resolve the issue through a support call to Microsoft.
We had a huge temporary table that was created as a heap table that had been truncated and then a smaller set of records were reinserted--leaving unused space.  This table was supposed to be dropped as it was no longer in use, but it was not dropped.  A bug in SQL Server 2005 ShrinkDatabase would not allow us to get past shrinking this table--although no error or warning is given.  When we dropped this heap table, the ShrinkDatabase command worked like a charm.
Thank you all for your help and suggestions.  Hope this helps someone else who finds themselves in this position--check for heap tables and clean up after yourselves.
0

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
Mark WillsTopic AdvisorCommented:
Well done, well found, and now that you mention it.... it does ring a vague bell.... but I thought that a heap is simply any table without a clustered index, and for a truncate (as distinct from a delete with a tablock held - see http://support.microsoft.com/kb/913399) not to allow space to be released would almost imply either a partition or external pointers such as rowsets > 8Kb, image (or suchlike) data elements where by pages hold references that can not be resolved and therefore not released... Just a normal table shouldn't do that, and have also seen a copy paste of a table from one database to another still retaining the original pointers. Or is there something else that MS was suggesting as a "heap table".

But yes, most definitely clean up afterwards.
0
lsig505Author Commented:
Sorry for the delay in posting....month-end is traditionally hectic.  I agree (and I believe Microsoft agrees also), that a heap table is simply a table without a clustered index and I'm not sure why truncating records would not allow space to be released in this scenario.  According to MS, it's a known bug, but I agree, it makes it sound as though it's retaining reference externally so it cannot give it up.
0
Mark WillsTopic AdvisorCommented:
Did a bit of testing on truncate, and for a straight table (you know, varchars, char, int and decimals) it does truncate OK. Definitely has problems with delete and concurrent tablock.  
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
It wouldn't hurt if lsig505 shared where the problem lied. It would be beneficial for all searching this forum. Is this doable, or did they solve it and you're not sure what the problem was?!?

/Marten
0
lsig505Author Commented:
Actually, I did post the resolution.  If you scroll back through this thread, I posted the resolution on 10/28 at 3:12 p.m.
0
Mark WillsTopic AdvisorCommented:
Yep certainly was, and it was also acknowledged ID:22824241

The question is not being deleted, the answer is being awarded, so it will remain for all to see... might have been nice to divvy up a few token assists :)
0
lsig505Author Commented:
Sorry about that...new to site and simply answered the questions as to how to close out the question. Not my intention to slight anyone.  Next time, I'll know better :-)
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 2005

From novice to tech pro — start learning today.