Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    2

    Unanswered: Delete Transaction Log

    Hi all,

    I am using MSSQL Server 2000. I have my SQL Server Database: mydb.mdf (1 GB) and mydb_log.ldf (30 GB).
    Now I would like to delete this VERY BIG transaction log and let the SQL Server automatic create a new one. What is the best way to do that? System downtime is available. Here is my way when I tested on development machine:
    1) Backup full database and transaction log.
    2) Detach DB by using Enterprise Manager
    3) Delete transaction log (mydb_log.ldf file) by using Windows Explorer.
    4) Attach DB.
    Then I saw SQL Server automatic created a new Transaction log (mydb_log.ldf) only 1 MB in size.

    Before I apply on Production, I would like to ask for your idea? Any warning? If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
    I appreciate for all help.
    Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Have you tried dbcc shrinkfile / dbcc shrinkdatabase ? Have a look at the following article:

    article

    If all else fails, then your solution is the quickest.

    Good luck.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Exclamation

    On deleting Transaction logs:

    Re: Before I apply on Production, I would like to ask for your idea? Any warning? If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
    I appreciate for all help

    Q1 Before I apply on Production, I would like to ask for your idea?
    A1 Why not use DBCC ShrinkFile, and / or enable autoshrink? Also, why is your Log so much larger than your Data e.g., (i Are you not frequently dumping / backing up your Log to transaction log backup *.trn files? ; ii Or, do you have long running transactions that are filling up your DB Log; iii Or, is your DB very heavily used, etc.?)?

    DBCC ShrinkFile advantages:
    * it is safe
    * it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
    * ordinary users may work in the DB while its files are being shrunk

    Use MyDB
    Go
    DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)
    Go

    Q2 Any warning?
    A2 You may "get away with" using your method indefinitly; however it is not safe.

    Q3 If possible do you know where I can find any official articles of Microsoft about deleting Transaction log?
    A3 BOL = Books On Line. You may install BOL using the Sql Server installer, it covers various recommended DBMS methods for reducing / limiting transaction log file sizes e.g., (including DBCC ShrinkFile, DBCC ShrinkDatabase). Microsoft Technet is another source of documentation that may help if / when your method gets you into trouble.

  4. #4
    Join Date
    Oct 2002
    Posts
    2
    Many thanks for all your help.
    First of all, I just received new role as a DBA. Then I found my DB is not monitored, dumped/backed up to *TRN for a long time.
    Why I do not want to use SHRINKFile? It seems to me DBCC SHRINKFILE does not work well, the physical size not reduce much as I expected! So I would like to "delete" and create a new log file.

    Regards,
    John

  5. #5
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Bill,
    Below is a reply I made to a post from a month or so ago. Think it will help you out.

    A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.

    Hope this helps.

    Brent

  6. #6
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    JohnBill,
    Below is a reply I made to a post from a month or so ago. Think it will help you out.

    A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.

    Hope this helps.

    Brent

Posting Permissions

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