unused space in db of SQL server 2000

  • 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

  • Try this SQL

    1. DUMP TRAN <DBNAme> with no_log

    Backup the database after this.

    2. Did you try Dbcc Shrink file>

    paul


    paul

  • 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

  • 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

  • We use only simple recovery mode and will your proposal work for this mode too? I suppose so.

    Thanks

    mipo

  • 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