none
How to Clear Database Transaction Log File in SQL Server 2008

    Question

  • We are planing to migrate SQL Server 2000 to SQL Server 2008.

    In production SQL Server 2000, we are clearing database transaction log using below steps.

    Backup DB
    Detach DB
    Rename Log file
    Attach DB
    New log file will be recreated
    Delete Renamed Log file

    Its working fine in SQL Server 2000, But same steps not working in SQL Server 2008.


    I also followed below Urls steps How to clear log file in SQL Server 2008. But no result.
    http://msdn.microsoft.com/en-us/library/ms175574.aspx
    http://msdn.microsoft.com/en-us/library/ms190757.aspx


    Can you please tell me how can i clear database transaction log file in SQl Server 2008.



    Tuesday, August 18, 2009 6:59 AM

Answers

  • Hi Krishna,

    The procedure you have been using is highly unrecommended and dangerous.
    Please read the following article which I believe will clear things up:
    http://support.microsoft.com/kb/873235

    HTH

    Ami.
    Tuesday, August 18, 2009 7:15 AM
  • Hi,

    Why you have to detach the database for clearing transaction log ? Personnaly there is no advantage to make this.
    To detach an database you have to set the base offline (and the activity at the same time). In an production environnement i don't think it's a good pratice.

    If the T-LOG is no important for you , you can set recovery SIMPLE for your database. (The TLOG will be cleared automaticly)
    If the TLOG is important for you strategy backup you have to set recovery FULL for your database and implement BACKUP LOG on a schedule. This actions are made in online mode.

    ++


    MCDBA | MCITP SQL Server 2005 | LPI Linux 1
    Tuesday, August 18, 2009 7:26 AM

All replies

  • What are you trying to achieve here? It seems a very strange set of steps - certainly not something i've seen before.

    Why do you want to delete your log file and create a new one? Can't you just put your database in SIMPLE recovery mode so that the size of the transaction log is kept to a minimum and you can always run DBCC SHRINKFILE(YourTransactionLog, TRUNCATEONLY) if you need to physically reduce the size.
    every day is a school day
    Tuesday, August 18, 2009 7:13 AM
  • Hi Krishna,

    The procedure you have been using is highly unrecommended and dangerous.
    Please read the following article which I believe will clear things up:
    http://support.microsoft.com/kb/873235

    HTH

    Ami.
    Tuesday, August 18, 2009 7:15 AM
  • Hi,

    Why you have to detach the database for clearing transaction log ? Personnaly there is no advantage to make this.
    To detach an database you have to set the base offline (and the activity at the same time). In an production environnement i don't think it's a good pratice.

    If the T-LOG is no important for you , you can set recovery SIMPLE for your database. (The TLOG will be cleared automaticly)
    If the TLOG is important for you strategy backup you have to set recovery FULL for your database and implement BACKUP LOG on a schedule. This actions are made in online mode.

    ++


    MCDBA | MCITP SQL Server 2005 | LPI Linux 1
    Tuesday, August 18, 2009 7:26 AM