Truncate_only

  • Hi Folks,

    Here is my question, I have database that is in full recovery mode, I have mdf and ldf files on two different drives and Logfile continuously growing.

    a. My question here is, can how can we move a ldf file to go to the mdf file and

    b. coming to the shrinking of log file I cannot shrink the log file this is going to be a problem as this is the production database. Can I use "Truncate_only" command to do this? and before using this command what I need to do

    Do I need to take a full backup and transaction log backup and do dbcc shrink-file command

    How can I proceed this problem? considering the scenario(b) what Exactly do I need to do first do I need to first take a full or Transaction log backups respectively

    Can any one explain me the scenario.

    Thanks&Regards,

    Ravi Shankar

  • How frequent are your full backups ?

    How frequent are your transaction log backups ?

    " ... how can we move a ldf file to go to the mdf file ..."

    Do you mean you want to move the log file from one drive, such as C, to another drive, such as D ? If the MDF and LDF are on the same drive, then you may have performance problems because they will be battling for the same drive resources.

    To move a file, detach the database, move the file, attach the file pointing to the new location. The database will be unavailable during this process.

  • Yes, my mdf(datafile) is in one drive and ldf file is another drive.

    Coming to the full backups they are taken for every 12 hrs and transaction log backup is taken for every 30 min.

    Scenario 1: My question was how can move ldf file on one drive to another drive containing mdf file and how this can be done?

    Scenario 2: And solution would be, taking a full backup and transaction log backup and then do DBCC shrinkfile command. For doing the "scenario 2" how can I go a head further?

    And when to use "Truncate_only"

    Thanks & Regards,

    Ravi S

  • Scenario 1) Why do you want them on the same drive ?

    To move a file, detach the database, move the file, attach the database specifying the new file location. The database will be unavailable during this process.

    Scenario 2) I never use truncate_only. You can shrink the log file to free up space, but it may grow again if you have lots of transactions. Maybe you should take log backups every 10 or 15 minutes.

    There are many threads about transaction logs, with answers much better than mine. Try searching them to see if you get more information.

  • Hi,

    Can go through this links.

    http://support.microsoft.com/kb/224071

    http://support.microsoft.com/kb/907511

    Thanks.

  • Truncate Only is not available in SQL 2008

  • RaviShankar1234 (4/13/2010)


    Hi Folks,

    Here is my question, I have database that is in full recovery mode, I have mdf and ldf files on two different drives and Logfile continuously growing.

    a. My question here is, can how can we move a ldf file to go to the mdf file and

    b. coming to the shrinking of log file I cannot shrink the log file this is going to be a problem as this is the production database. Can I use "Truncate_only" command to do this? and before using this command what I need to do

    Do I need to take a full backup and transaction log backup and do dbcc shrink-file command

    How can I proceed this problem? considering the scenario(b) what Exactly do I need to do first do I need to first take a full or Transaction log backups respectively

    Can any one explain me the scenario.

    Thanks&Regards,

    Hello,

    Before making any comments on your questions, can you tell us about

    1. are you using SQL 2000 / 2005 or 2008 ?

    2. Why do you want o put your log and data files on same drive ?

    3. If its a production DB, why do you want to use truncate_only ?

    Here is Paul's article why you should not use it:

    http://www.sqlskills.com/blogs/paul/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

    Please let us know to help you better 🙂

    Thanks,

    \\K

    Ravi Shankar

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • RaviShankar1234 (4/13/2010)


    Hi Folks,

    Here is my question, I have database that is in full recovery mode, I have mdf and ldf files on two different drives and Logfile continuously growing.

    a. My question here is, can how can we move a ldf file to go to the mdf file and

    b. coming to the shrinking of log file I cannot shrink the log file this is going to be a problem as this is the production database. Can I use "Truncate_only" command to do this? and before using this command what I need to do

    Do I need to take a full backup and transaction log backup and do dbcc shrink-file command

    How can I proceed this problem? considering the scenario(b) what Exactly do I need to do first do I need to first take a full or Transaction log backups respectively

    Can any one explain me the scenario.

    Thanks&Regards,

    Hello,

    Before making any comments on your questions, can you tell us about

    1. are you using SQL 2000 / 2005 or 2008 ?

    2. Why do you want o put your log and data files on same drive ?

    3. If its a production DB, why do you want to use truncate_only ?

    Here is Paul's article why you should not use it:

    http://www.sqlskills.com/blogs/paul/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

    Please let us know to help you better 🙂

    Thanks,

    \\K

    Ravi Shankar

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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