Need work around to lower initial size and shrink database

Question

Post reply

Need work around to lower initial size and shrink database

Viewing 10 posts - 31 through 39 (of 39 total)

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

croberts 36762 SSC Eights! Points: 972 More actions · Answer 1

croberts 36762

SSC Eights!

Points: 972

May 31, 2010 at 2:26 pm

#1174588

deleted

rijan2 Grasshopper Points: 19 More actions · Answer 2

I was able to shrink the initial size of the database as follows

Right click database in Management Studio

Choose Tasks, Shrink, Files

Select File Name to shrink, Reorganize pages before releasing unused space and enter size to shrink file to.

eabarner SSC Journeyman Points: 91 More actions · Answer 3

Setting the recovery mode to SIMPLE before the shirnkfile was executed worked for me! I had tried all of the other steps in this post and this was the first one that actually worked

Much Appreicated!!!

sanghelut Grasshopper Points: 17 More actions · Answer 4

Try setting the recovery model to simple before performing the DBCC Shrinkfile command.

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 5

This worked for me as well......

I was able to shrink the initial size of the database as follows

Right click database in Management Studio

Choose Tasks, Shrink, Files

Select File Name to shrink, Reorganize pages before releasing unused space and enter size to shrink file to.

shishirkanthi Grasshopper Points: 22 More actions · Answer 6

This might answer why the DB size shrinked after you took a Full backup:

http://support.microsoft.com/kb/324432

shishirkanthi Grasshopper Points: 22 More actions · Answer 7

This would answer why DB size would shrink on taking a full DB backup.

http://support.microsoft.com/kb/324432

Jack 95169 SSC-Addicted Points: 410 More actions · Answer 8

I have a database where the initial size was larger than required at 1500Mb

I tried this code:

USE master;

GO

ALTER DATABASE [dbname]

MODIFY FILE

(NAME = [logicalfilename],

SIZE = 1000MB);

GO

It failed with the error message

Msg 5039, Level 16, State 1, Line 1

MODIFY FILE failed. Specified size is less than current size.

However, if I right click on the database, select properties, and select Files, it allows me to change the initial size of 1500 to 1000

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 9

I have had success shrinking stubborn log files with this code.

Never used it on a mdf before though. A backup might be in order before trying.

Don't know what your minimum size will be, but probably not 1....

Execute SP_ReplicationDbOption <My_DB>,Publish,true,1

GO

DBCC ShrinkFile(<MyDB_Log>,1)

GO

Execute SP_ReplicationDbOption <My_DB>,Publish,false,1

GO

Maybe loop through all your DB tables using sp_SpaceUsed to find out what the actual datasize is.

This'll show the datafile size according to sql.

select Name, phtsical_name, size from sys.master_files where Type = 0 AND Name = '<DBName>'

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

epeckitt Grasshopper Points: 13 More actions · Answer 10

Yes - Simple Mode first, then shrink. Just remember, if you put a database into simple mode to shrink it, and it works, then you may need to switch it back to FULL recovery mode to log transactions again. When you do that you need to do a FULL backup to get those transactions logging. Such an old post to dig up, but I had a brain fart and this jogged my memory. Old information isn't a bad thing to add too 🙂