Results 1 to 7 of 7

Thread: Shrink db

  1. #1
    Join Date
    Nov 2003
    Posts
    68

    Unanswered: Shrink db

    Hello,
    I have a 24go sqlserver 2000 database, but only 5go used, when i use the shrink command in em there is no change.

    can i modify the property of the db (auto-shrink => on ) actually it's on OFF.

  2. #2
    Join Date
    Feb 2004
    Posts
    8
    Is is the Data or the Log that is using up all this space?

    It you have a big transaction log from big updates and if its not a transactional database (e.g. constantly updated all the time e.g. with customer sales) then you could shrink the log and clear the transaction history. Just backup the database beforehand just in case.

    To do this in T-SQL window type

    backup log 'databasename' with no_log

    Then shrink the database through the taskpad (make sure you select Log rather than the default Data from the drop down list).

  3. #3
    Join Date
    Nov 2003
    Posts
    68
    it's the data.

  4. #4
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    The problem you are going to have, is that the data file will only shrink from the end backwards, in the same fashion as the log file does.

    You could try exporting your tables, dropping the originals, and then import the tables back in again. This should (and I stress should) put all the data at the beginning of the data file (you may need to shrink the data file when you drop the tables) - leaving any empty space at the end of the data file.

    I have never actually used this method, and I cannot emphais enough the need for testing - but in theory this should work.
    Regards
    Dbabren

  5. #5
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    In Enterprise Manager, when you choose shrink database, there is a "move pages to start of datafile" tick box - have you tried that?
    Regards
    Dbabren

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Is this a production db ?

    I would not truncate the log with a no_log.

    If it is production, you should schedule regular transaction log backups around your db backups to ensure recoverability. After a full backup, delete all the previous t-log dumps, and start over. This frees up space in the t-log, but does not shrink it. If you have never backed up your t-log, it's probably at a size it will never hit again once you schedule regulart t-log backups (if not simple recovery mode). I would back up the log to file, shrink it, take a full backup, delete the t-log file you just backed up, and put a log backup/dumping schedule in place around your regular full backups. This is essential if it is an active prod database.

  7. #7
    Join Date
    Aug 2002
    Posts
    2

    I get this all the time.

    As long as you have a full backup on hand then you shouldn't need the transaction log anymore. Use this script (obviously substituting DATABASE_NAME for the name of your DB):

    BACKUP LOG DATABASE_NAME WITH TRUNCATE_ONLY
    GO
    DBCC SHRINKDATABASE (DATABASE_NAME ,TRUNCATEONLY)
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •