Post reply

Transaction log too big, should I delete or shrink the log?

  • September 12, 2006 at 6:39 pm

    #61819

    Hi All,

    I have just been handed over a database server whose log backups have not been taken regularly and the transaction logs have blown to out by heaps. I have to move these databases to a new server, and don't want to  bring these huge logs over to the new server.

    I have 2 databases that will be detached, copied, attached. In this case, can I create a blank database with the same file names on the new server, and then detach the database from the other end and this end, copy the files on top the these. Will this mean that I will loose any transactions?

    While the other 4 can be backed up easily. So, If I just restore to the new server. Detach the database, delete the transaction Log, and attach the database and let it create a new transaction log, will I loose any transaction then? Then can I increase the log size to a reasonable amount so that there is no impact on the database when it tries to increase the log size?

    Also, once these are migrated, I will be taking regular snapshots of the transaction log and then truncate the log so that I can keep the log to the optimum size and not let it blow out too much.

    What do you recommend? Should I shrink the log file instead? I've noticed that shrinking the log file doesn't always work as the transactions could be written to the very last pages, so it won't shrink it much.

    Your views are highly appreciated.

    Thanks,

    TK

     

  • September 12, 2006 at 7:34 pm

    #659997

    1.run DBCC OPenTran to see if any open tranasctions are there

    2.Change Recover Model to "simple"

    3.Do a complete backup.

    4.Run DBCC ShrinkDatabase(DBName,10)

    5.Change Recover Model to "full"

    6.Do a complete backup.

    7.Use the backup file to restore to a new server.

    Thanks

    Sreejith

  • September 12, 2006 at 7:40 pm

    #659998

    Thanks, I will do that to the databases that can be backed up.

    However, what about the ones that can't be backed up. (These are too big, no room left on the server itself for me to back them up, and they need a hotfix to backup over the network).

  • September 15, 2006 at 8:51 am

    #660738

    not sure what you mean by requiring a hotfix to backup over the network.  Talk to your network guys about setting up the required permissions for you to  a server on your network that you can backup the DB's to over a share.

    After you have successfully backed up your DB - you do not need to dettach/attach. Just do this:

    1.  backup log db_name with no_log ( this will clear up inactive part of the trans log)

    2. DBCC Shrinkfile (log_file_name, 100, truncateonly) - this will shrink the log file to 100MB in this case - change according to your pref

    3. Take a full DB backup and set up a schedule to back up trans logs.

     

    Good Luck

Viewing 4 posts - 1 through 3 (of 3 total)

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