Shrink does not release space

Question

Post reply

Shrink does not release space

JoseRocha

Mr or Mrs. 500

Points: 574
More actions
August 13, 2013 at 2:58 pm

#276155

Hi =)
Im trying to shrink my production database, because we archived about 200GB of data, but - I don't know why - none space is released. I tried rebuild some indexes, shrink via file and even suspended the mirror (this environment has mirroring) but 0 KB is released rs.
Recently I created an environment for testing and restore one of my lastest backups from the production database. I shrinked and ..... works fine. Released the unused space...
I really don't know what to do. Pls, Heeelp rs
ps.: I searched in others topics, but didn't find any solution
ps2: Sorry for my english...

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

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

Snigdha Vartak Default port Points: 1486 More actions · Answer 1

Try DBCC command to shrink database files.

Refer: http://technet.microsoft.com/en-us/library/ms190488.aspx

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

Snigdha Vartak (8/13/2013)

Try DBCC command to shrink database files.

Refer: http://technet.microsoft.com/en-us/library/ms190488.aspx%5B/quote%5D

The OP said the he already tried to shrink the file without success. It won't matter if you use the GUI or the DBCC command, sometimes it's just not going to work.

--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)

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 3

jfgrocha (8/13/2013)
Hi =)
Im trying to shrink my production database, because we archived about 200GB of data, but - I don't know why - none space is released. I tried rebuild some indexes, shrink via file and even suspended the mirror (this environment has mirroring) but 0 KB is released rs.
Recently I created an environment for testing and restore one of my lastest backups from the production database. I shrinked and ..... works fine. Released the unused space...
I really don't know what to do. Pls, Heeelp rs
ps.: I searched in others topics, but didn't find any solution
ps2: Sorry for my english...

You may not have rebuilt the correct indexes. Also, what method are you using to shrink at the file level? Unless you tell it to reorganize the data during the shrink, you might not get any reduction in size at all. Be advised that using the the reorganize thing will totally fragment every table you have and you'll have to rebuild all of the indexes afterwards, again. If you can get by without shrinking the database, that would likely be the best thing to do.

--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)

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Hi

Can you check whether you have open transactions?

SELECT * FROM sys.sysprocesses sp WHERE sp.open_tran = 1 and sp.dbid = DB_ID()

or

DBCC OPENTRAN

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

JoseRocha Mr or Mrs. 500 Points: 574 More actions · Answer 5

Hi Jeff,

I'm not sure If I understood your question, but I running the command DBCC SHRINKFILE('name').

JoseRocha Mr or Mrs. 500 Points: 574 More actions · Answer 6

Igor,

I ran the command and right now there's none opened transactions.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

When you shrink, you should always specify a target size, and you should specify that size so that there is space to rebuild your largest tables after the shrink, because you will need to that, as a shrink operation introduces a lot of fragmentation.

However, I would question that you should shrink at all. You say that you archived a lot of data, but what will happen in the future? Will you just keep adding data again? Sooner or later the database will come back to the same size again.

Shrinking a database is only meaningful if you think that you will never need the space again (at least not for the next three years or so).

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

sqlsurfer1 SSC-Addicted Points: 453 More actions · Answer 8

sqlsurfer1

SSC-Addicted

Points: 453

August 17, 2013 at 11:32 pm

#1642467

What mode is your database in - full recovery mode?

kevaburg SSCoach Points: 18131 More actions · Answer 9

1. Check to make sure the Initial size of the database is not set to a value higher than the amount you want to shrink the database to.

2. As part of the SHRINK DATABASE, don't Forget to use the reorganise clause to reduce fragmentation within the datafile?

3. Why would you want to shrink the database in the first place? Or are you trying to shrink an oversized Transaction log?

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 10

jfgrocha (8/16/2013)
Hi Jeff,
I'm not sure If I understood your question, but I running the command DBCC SHRINKFILE('name').

You said you rebuilt some of the indexes. You might not have rebuilt enough of them to free up the necessary pages to do a straight up shrink. You may have to do a shrink with the "reorganize" option. The problem with that is it will absolutely fragment the dickens out of your database and you will need to rebuild all of the indexes again. That will also cause some unused freespace to appear because SQL Server needs something like 1-1/2 times the size of the largest index to do the rebuilds whether you do it in an online or offline fashion.

--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)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 11

Jeff Moden (8/18/2013)

You said you rebuilt some of the indexes. You might not have rebuilt enough of them to free up the necessary pages to do a straight up shrink.

In such case, he should not shrink at all.

You may have to do a shrink with the "reorganize" option.

This is the second time you are saying this. But what are you talking about? The syntax for DBCC SHRINKDATABASE is

DBCC SHRINKDATABASE

( database_name | database_id | 0

[ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

[ WITH NO_INFOMSGS ]

And for DBCC SHRINKFILE, the syntax is:

DBCC SHRINKFILE

(

{ file_name | file_id }

{ [ , EMPTYFILE ]

| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]

}

)

[ WITH NO_INFOMSGS ]

There is no REORGANIZE option.

That said, shrink will fragment the database with the default option, and an index rebuild after the shrink is essential.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 12

My apologies. The "reorganize" option is a leftover in my mind from me only doing these types of things via the GUI simply because it's so rare that I'd do such a thing. The "reorganize" option using the DBCC command is to simply leave off the "TRUNCATE ONLY" option.

This will "reorganize" a "Test" database and the indexes will need to be rebuilt right after...

DBCC SHRINKFILE (N'Test' , 1)

This will not "reorganzie" and will only release unused space, which might not be much at all...

DBCC SHRINKFILE (N'Test' , 0, TRUNCATEONLY)

And, yes... I agree. As I said in my previous post, you'll need to rebuild the indexes after a shrink.

--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)

kevaburg SSCoach Points: 18131 More actions · Answer 13

You are of course right and I should have worded my response better. Using the TRUNCATEONLY clause only frees up space at the end of the datafile and does not perform any reorganisation at all. To that end, identify the indexes that require reorganisation and perform a reorganisation or rebuild as necessary.

For the reorganisation the syntax is as follows (taken from TechNet);

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode

ON HumanResources.Employee

REORGANIZE ;

Once the indexes have been rebuilt or reorganised, perform the SHRINKFILE firstly ensuring that the initial size for the file is appropriately set.

But my question still stands: What is deemed the reason for having to perform the operation in the first place?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 14

No, ALTER INDEX REORGANIZE is not going to help if you are to shrink a database. REBUILD may do, as you may end up with fewer extents allocated to the table/index. But REORGANIZE only shuffles around the pages for the index, but do not change them beside the link pointers.

Besides, jfgrocha said that when he restored a copy of the database and ran DBCC SHRINKFILE/DATABASE on that copy, the shrink worked. So all this talk about rebuilding indexes are off the point. There is something else that prevents shrinking. Possibly the fact that the database is mirrored, or that he is trying to shrink below the original size (but in the latter case, I wold not expect it to work on the copy either.)

And since shrinking of the copy works, that seems to be the solution.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]