Cannot Shrink Database

Question

Post reply

Cannot Shrink Database

Fraggle-805517

SSCrazy Eights

Points: 8907
More actions
March 9, 2010 at 1:53 pm

#386467

Hey everyone,
So I realize that shrinking a database is generally bad. However, the database I am shrink is on a test server, and I need the room.
I have run both the following sets of statements
dbcc shrinkdatabase ('dbname', 10, notruncate)
dbcc shrinkdatabase ('dbname', 10, truncateonly)
I have done the same thing with shrinkfile on the MDF file name.
Neither of these have reduced the size of the database.
sp_spaceused shows the following.
database_namedatabase_sizeunallocated space
DBNAME233262.25 MB144133.43 MB
reserveddataindex_sizeunused
88930888 KB53314544 KB35440696 KB175648 KB
If I am reading this correctly, I have 144GB of free space that I should be able to release. Now, I really only care about 100GB of it as that is roughly the size of the other database that I need to move onto the test server.
Anyone have any thoughts on why this is not shrinking?
Note I am using SS2005, SP3, 64Bit.
Thanks,
Fraggle

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Shrink DB File by Increment to Target Free Space

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

GTR SSCrazy Eights Points: 8007 More actions · Answer 2

What is the intial size of the database? What is the unused space on mdf and Ldf files?

EnjoY!

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 3

Michael Valentine Jones (3/9/2010)
Shrink DB File by Increment to Target Free Space
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

Thanks for the link. I will give it a try this evening an let you know.

Fraggle

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 4

GTR (3/9/2010)
What is the intial size of the database? What is the unused space on mdf and Ldf files?

FileSizeMBUsedSpaceMBUnusedSpaceMBDBFileName

230980.0086836.75 144143.19Data

3032.25 2920.71 111.54Log

Initial size is 230GB....which makes complete sense. I cannot shrink below this. DUH.

Thanks,

Fraggle

:ermm:

GTR SSCrazy Eights Points: 8007 More actions · Answer 5

Fraggle-805517 (3/9/2010)
GTR (3/9/2010)
What is the intial size of the database? What is the unused space on mdf and Ldf files?
FileSizeMBUsedSpaceMBUnusedSpaceMBDBFileName
230980.0086836.75 144143.19Data
3032.25 2920.71 111.54Log
Initial size is 230GB....which makes complete sense. I cannot shrink below this. DUH.
Thanks,
Fraggle
:ermm:

There you go, that is why i asked you that question, you know now:cool:

EnjoY!

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 6

OK, now I am a bit lost. Apparently, altering the database to modify the file to a smaller size throws an error stating that the size I am trying to modify to is smaller than the current size which isn't allowed

Thoughts?

Fraggle

GTR SSCrazy Eights Points: 8007 More actions · Answer 7

GTR

SSCrazy Eights

Points: 8007

March 9, 2010 at 9:46 pm

#1131126

Is this in production?

EnjoY!

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 8

Fraggle-805517

SSCrazy Eights

Points: 8907

March 9, 2010 at 9:48 pm

#1131131

Thankfully no. Just on a test server

Fraggle

GTR SSCrazy Eights Points: 8007 More actions · Answer 9

Then create new database with small initial size, and restore the database, then shrink the new database it should work. I don't remember on top of my head what we did to change initial size.

EnjoY!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

My preference has always been to use dbcc shrinkfile in SQL 2005. This allows one to shrink below the initial database size. This could also be a time saver.

Here is another thread here at ssc with supporting documentation:

http://www.sqlservercentral.com/Forums/Topic508106-149-1.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GTR SSCrazy Eights Points: 8007 More actions · Answer 11

Jason,

Are you sure DBCC shrinkfile will shrink below initial size of the mdf or ldf file size?

EnjoY!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

GTR (3/9/2010)
Jason,
Are you sure DBCC shrinkfile will shrink below initial size of the mdf or ldf file size?

Yes, I have done it hundreds of times. I use it to defrag log files for instance. I also use it if a process has gone haywire in a development database. Of course we find the process that caused the growth first, fix it and then reduce our file sizes.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

It also works from SSMS when you shrink at the file level and tell it to reorganize before shrinking.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 14

GTR (3/9/2010)
Are you sure DBCC shrinkfile will shrink below initial size of the mdf or ldf file size?

If you are unsure, you should test it. It's the best way to learn.

(The answer is yes, of course.)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi