Solved

SQL Server DBCC SHRINKDATABASE Not Recovering Unused Space

Posted on 2008-10-26
23
2,122 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:lsig505
  • 10
  • 7
  • 2
  • +3
23 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22808164
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 22808177
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
 
LVL 22

Expert Comment

by:dportas
ID: 22808571
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
 

Author Comment

by:lsig505
ID: 22809034
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
 

Author Comment

by:lsig505
ID: 22809045
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22809071
What does the "available" space say in SSMS ?
0
 

Author Comment

by:lsig505
ID: 22809594
SSMS shows 221GB space available
0
 
LVL 41

Expert Comment

by:graye
ID: 22812975
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
 

Author Comment

by:lsig505
ID: 22813384
We ran the shrink command in query window several times--only message returned on the message tab was "command completed successfully".
0
 
LVL 41

Expert Comment

by:graye
ID: 22816421
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
 
LVL 4

Expert Comment

by:ThorSG1
ID: 22817187
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:lsig505
ID: 22820916
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
 

Author Comment

by:lsig505
ID: 22821169
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22821320
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22821350
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
 

Accepted Solution

by:
lsig505 earned 0 total points
ID: 22823928
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22824241
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
 

Author Comment

by:lsig505
ID: 22868204
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22868345
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 22924582
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
 

Author Comment

by:lsig505
ID: 22924736
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22927334
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
 

Author Comment

by:lsig505
ID: 22929876
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now