May 19, 2003 at 2:55 am
I want to add something regarding this topic.
If I run the following query:
use Madison
DBCC SHRINKFILE ('opms_Data', XXXX)
where XXXX stands for the next reachable file size which is calculated as allocated space or database_size (checked with sp_spaceused @updateusage = true) minus unallocated space equals = free space to be released. Also you can see in EM>all tasks>shrink database>shrink file the minimum shrinkable file size that is reachable. If I run again the query above a new minimum shrinkable file size is generated and so you can do it several times until your files are shrunk and cannot be shrunk any further.
Why can't it be done in one go?
(This is a topic which can be discussed before and after my retirement because it is never exhausted-it gives more stuff to talk than speaking about the weather...)
Thanks
mipo
May 20, 2003 at 8:50 pm
Try this SQL
1. DUMP TRAN <DBNAme> with no_log
Backup the database after this.
2. Did you try Dbcc Shrink file>
paul
paul
May 21, 2003 at 2:21 am
Yes I tried with dbcc shrinkfile and it actually shrinks it but you have to do it several time to get it to an appropriate size.
mipo
May 21, 2003 at 12:26 pm
After optimizing every Sunday, my database becomes very large(both .mdf and .ldf files).
I did the following steps to shrink the database. It works everytime (nearly one year).
1. run dbcc shrinkdatabase(dbname, truncateonly).
2. change db recovery mode to simple.
3. run shrinkdatabase(dbname).
4.change db recovery mode back to full.
5. backup database.
you can try it.
Robert
May 22, 2003 at 12:31 am
We use only simple recovery mode and will your proposal work for this mode too? I suppose so.
Thanks
mipo
May 22, 2003 at 12:06 pm
I think so. You can try it, truncate is very fast. shrink will take much longer time. but for your database only a few GB, it wouldn't take too long time.
Robert
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply