DBCC Shrinkfile & Shrinkdatabase

Question

Post reply

DBCC Shrinkfile & Shrinkdatabase

Viewing 15 posts - 16 through 30 (of 36 total)

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

Skandalis Hall of Fame Points: 3517 More actions · Answer 1

Thanks, I appreciate the input. But as previously stated, truncating/shrinking the log file is not a problem. 😉

It's getting that MDF down to size...

Cliff Jones SSChampion Points: 10547 More actions · Answer 2

So when you execute :

DBCC Shrinkfile (1,1)

What does it return?

Skandalis Hall of Fame Points: 3517 More actions · Answer 3

It returns the following:

DbID: 18

FileID: 1

CurrentSize: 13887128

MinimumSize: 152

UsedPages: 234824

EstimatedPages: 234528

Even though the current size reflects what it should be, the size of the file on the OS is still 110GB

Cliff Jones SSChampion Points: 10547 More actions · Answer 4

Did you run a DBCC Checkcatalog and does it return without error? I have seen situations where orphaned tables (the columns exist in syscolumns but the objectid is not in sysobjects) were taking up a lot of space that didn't show up. I didn't remember it behaving like this though.

I have a server where we bring in customers databases and try to reproduce an issue and do additional development so I am accustomed to trying to fit as many databases on a server as I can, knowing they won't grow.

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

You can use the script on the link below to shrink your database data file in small increments. It will take a while to shrink it, but it will get the job done. Since this is for testing, you should put the DB in simple recovery mode first to prevent log file growth.

You should probably defragment the indexes and update the statistics when this script completes, because it will cause fragmentation.

Shrink DB File by Increment to Target Free Space

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

Skandalis Hall of Fame Points: 3517 More actions · Answer 6

No errors with DBCC Checkcatalog.

Thanks for the script Michael. It's a nice to have, but unfortunately didn't shrink it...

Appreciate the help though.

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

You might try rebuilding all the indexes with 100% Fill factor, run DBCC updateusage and then try shrinking again.

Skandalis Hall of Fame Points: 3517 More actions · Answer 8

Thanks Cliff.

Still no joy. I'm really stumped. Funny thing is this happens every time I restore this db...

cbaker-591171 SSC Veteran Points: 248 More actions · Answer 9

cbaker-591171

SSC Veteran

Points: 248

April 11, 2008 at 7:56 am

#801489

try sp_spaceused @updateusage = true

Cliff Jones SSChampion Points: 10547 More actions · Answer 10

Lian Pretorius (4/9/2008)
Thanks Cliff.
Still no joy. I'm really stumped. Funny thing is this happens every time I restore this db...

Yes, I had a database like that and that's how I discovered the catalog corruption issue. I thought that was going to be your problem since those objects take up space but don't show up in the system cataloge. Once I fixed all the orphaned objects I was able to reclaim the space. I am out of ideas.

Make sure this returns no rows:

SELECT DISTINCT id

FROM SYSCOLUMNS

WHERE id NOT IN (SELECT ID FROM SYSOBJECTS)

Skandalis Hall of Fame Points: 3517 More actions · Answer 11

Thanks again Cliff.

No rows returned. I don't think any objects(tables) are orphaned as I only truncate & delete data, not dropping tables. I'll pray this weekend & start afresh on Monday :crazy:

ChiragNS One Orange Chip Points: 26137 More actions · Answer 12

ChiragNS

One Orange Chip

Points: 26137

April 16, 2008 at 12:32 am

#802897

Pls Keep us posted.

"Keep Trying"

Chandrachurh Ghosh Hall of Fame Points: 3001 More actions · Answer 13

I have a straightforward method of truncating the log file to the minimum size. Please note that one of the forum members have suggested me not to use it, but couldn't give any reason at all in support.....

I have used it many times without any apparent side effects. Run the following scripts -

sp_dboption ' ','trunc. log on chkpt.','true'

CHECKPOINT

Then shrink the log file, specifying the minimum size.

For the database.....

As from your case, it is evident that you query your database more than you insert or update.....

Cliff Jones is correct....rebuild the indexes...

Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.

Skandalis Hall of Fame Points: 3517 More actions · Answer 14

Thanks. The log is fine.

I have re-biult the indexes, but the filesize (of the primary file - MDF) doesn't change after shrinking.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 15

Hi

Do you have anything other than the normal "things" in the db. Any image , Varchar(MAX)... columns. Any encryption or anything else that could give a clue on this.

"Keep Trying"