Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a database I wanted to deploy with my application. So I emptied it, but the size is still HUGE! Even after running DBCC SHRINKFILE (1, 1) and DBCC SHRINKDATABASE (1, 1)

Can anyone help?

share|improve this question

migrated from stackoverflow.com Feb 9 '11 at 20:43

This question came from our site for professional and enthusiast programmers.

1  
1) Define huge, 2) You can't shrink the DB to be smaller than the model database on the same server. –  Damien_The_Unbeliever Feb 8 '11 at 10:53
    
Huge being 22 mb (which is quite big considering the original db was about 750 kb) –  Jimbo Feb 8 '11 at 11:05

3 Answers 3

up vote 3 down vote accepted

If the database is set to use the "Full" recovery mode, then you will not be able to shrink it until you have backed up the transaction logs.

If you don't require the recoverability provided by this recovery mode, set it to Simple, and you should be able to shrink the database without requiring the log backup.

share|improve this answer
1  
ALTER DATABASE MyDB SET RECOVERY SIMPLE - worked great, thanks –  Jimbo Feb 8 '11 at 11:04
    
I believe that MS have also removed the ability to truncate the log files manually in SQL 2008, so this is the only method. –  Mark Henderson Feb 9 '11 at 21:04

You might do better by shipping with scripts rather than an actual database - that way you can avoid, for instance, service pack issues - If you've developed against SP2 of a particular version of SQL Server, people using RTM or SP1 of the same version of SQL would be able to use your scripts, but would be unable to attach an actual db.

share|improve this answer

It looks like there are a fair few options on the SHRINKFILE command, rather than repeat them here, try this link, it gives a decent explanation on the comman options, hopefully some of it will help...

Shrinking Ms SQL Server Databases

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.