Post reply

shrink database

  • July 29, 2009 at 3:05 am

    #137033

    dear all,

    i have grate problem and i need your help.

    - i have database its size became 28 Gigabyte the size if DB.mdf is 1.5 Gigabyte , and the size of DB_log.ldf is 26.5 Gigabyte ,

    after i shrink it it became 26 Gigabyte .

    the size of log file is very big and i want to shrink it .

    please any help.

  • July 29, 2009 at 4:03 am

    #1031523

    Hi,

    What is the recovery model of your database. Are you taking log backups regularly if it is in full recovery model

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • July 29, 2009 at 4:23 am

    #1031527

    hi thanks for your reply ,

    but i can't understand what do you mean, i'm not DBA ,

    i'm adeveloper and want to copy DB from server to attach it on another server ,so i found its size very big and i want to shrink it ,

    informs you that i use the code

    ---

    dbcc checktable (syslogs)

    dump tran with no_log

    dbcc checktable (syslogs)

    dbcc perflog

    --

    and

    DUMP TRANSACTION [database_name] WITH NO_LOG

    --------------

    and the size of DB became small 1.5 Gigabyte

    next i try to do this again but the database not attahced to new server and get error

    this what i do

  • July 29, 2009 at 5:52 am

    #1031565

    ---Below command will solve ur problem

    BACKUP LOG WITH TRUNCATE_ONLY

    GO

    DBCC SHRINKFILE ()

  • July 29, 2009 at 6:19 am

    #1031586

    vinod.km (7/29/2009)

    ---Below command will solve ur problem

    BACKUP LOG WITH TRUNCATE_ONLY

    GO

    DBCC SHRINKFILE ()

    Remember that truncating a log will break the log chain.

    Also shrinkfile will require the name of ldf file.

    dbcc shrinkfile(myLogFile, 10)

    Here myLogFile is the logical name of the log file and after running this, the log will be reduced to 10 MB in size.

    Take a full backup after this to start a new chain.


    Pradeep Singh

  • July 29, 2009 at 2:06 pm

    #1032008

    Since you don't need the database in full recovery model, I would change the recovery model to simple, then shrink the log file. If you don't change the recovery model - you are going to find the transaction log will grow again, unless you implement regular transaction log backups.

    Review the article I link to in my signature about managing transaction logs.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply