MSSQL - changing minimum / startup sizes

[docevi1]

Hi,

quick question, is it possible to change the minimum size on databases in MSSQL? I've been googling for about 30mins now trying to find the answer, which appears to be "no".

There are a load of databases here with sizes of 225mb (the default creation sizes for scripts we use) yet are using less than 5MB each - we need to reclaim the space back for other projects but can't delete the DB's to do so...

any ideas? DBBC SHRINKDATABASE only gets us back to 225...

[Don]

Try creating a database of minimum size and then use the Import/Export Wizard to transfer database objects across...then do some database renaming.

Always used to be how I did it...

[selwonk]

Have you tried:

DBCC SHRINKDATABASE ([database_Name], 5, TRUNCATEONLY)

IIRC, this will shrink database_Name leaving 5% space free after shrinking and then releases the freed space to the OS

Edited: Typo

Edited by selwonk on Monday 4th September 16:49

[docevi1]

does that not mean that all the auto-indexing functions stop?

[docevi1]

selwonk said:

Have you tried:

DBCC SHRINKDATABASE ([database_Name], 5) TRUNCATEONLY

IIRC, this will shrink database_Name leaving 5% space free after shrinking and then releases the freed space to the OS

When I do that it the minimum it'll go to is the creation size (in this case 225mb with 221 mb free )

[selwonk]

Yep - you're right. Same here. Try:

DBCC SHRINKDATABASE ([database_Name, 5)

[docevi1]

nope, still stays stuck at 225MB

[docevi1]

the transaction log has already been removed.

When the db is created from scripts it is automatically made to be 225mb - in reality we don't use more than 5mb usually hence needing to lower the original size rather than shrink what data we have...

editt: you crafty devil, deleting your post :P

Edited by docevi1 on Monday 4th September 16:54

[selwonk]

Ok - have a look at SHRINKFILE too:

http://msdn.microsoft.com/library/def

[selwonk]

docevi1 said:

the transaction log has already been removed.

When the db is created from scripts it is automatically made to be 225mb - in reality we don't use more than 5mb usually hence needing to lower the original size rather than shrink what data we have...

editt: you crafty devil, deleting your post :P

Edited by docevi1 on Monday 4th September 16:54

Realised it was a load of rubbish...

[docevi1]

selwonk said:

Ok - have a look at SHRINKFILE too:

http://msdn.microsoft.com/library/def

I owe you a pint!

Thank you very much.

[selwonk]

Seems to do the job nicely...

Cheers!

[docevi1]

lol, I've just reclaimed 5Gb of space from the various DB's that are stored here! No amount of googling turned up that command - I guess you have to know it before you can find it

Thank you either way, earned me a few brownie points here I'd assume.