Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Nottingham, UK
    Posts
    37

    Unanswered: transaction log file size too big!

    (windows 2000 professional running SQL Server 2000)

    How do you reduce the file size of the transaction log? Is it safe to delete the transaction log? I tried clicking on the ellipse (...) box in database properties, but even tho i'm the dba, it says i don't have permissions to do that! I see the "automatically grow transaction log" and "maximum file size for transaction log" dialogs, but our transaction log is already 5.5 GB, and we need for it to be WAY smaller! Is there a way to back it up, then start over with a new one or something? Still learning SQL Server...

    Thanks in advance from you SQL Server gurus!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

    Re: transaction log file size too big!

    Skinny
    If this is truly a permissions problem, check to see that the login you are using is a member of the sysadmin server role. I do not remember if a mere db_owner can shrink files.
    Second check the options of the database properties. You may have this database on Full recovery model. Full is a good thing for production databases, as it is the only way you will ever get the database back from tape to a point in time. I have rarely seen Development servers on Full recovery.
    If the database is on Full recovery you must backup the transaction log, before you can truncate the log. Use thiscommand if you have an extra 5GB of diskspace handy:

    backup transaction databasename to disk = 'E:\filename.bak' with init

    Fill in your databasename and a filename (that hopefully does not exist), and you should be able to then use:

    dbcc shrinkfile (2, size_in_MB)

    This assumes that your log file has fileid = 2. Check in the sysfiles table to be sure.

    If you do not have 5GB free, you can just truncate (and discard) the transaction log, but you will very definitely want to make a full database backup immediately afterward. The command to discard the log is:

    backup transaction databasename with no_log

    Then run dbcc shrinkfile, and you should be all set. Hope you can recover the space.
    ~Matt

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Re: transaction log file size too big!

    RE:
    (windows 2000 professional running SQL Server 2000)

    Q1 How do you reduce the file size of the transaction log?
    Q2 Is it safe to delete the transaction log? I tried clicking on the ellipse (...) box in database properties, but even tho i'm the dba, it says i don't have permissions to do that! I see the "automatically grow transaction log" and "maximum file size for transaction log" dialogs, but our transaction log is already 5.5 GB, and we need for it to be WAY smaller! Q3 Is there a way to back it up, then start over with a new one or something? Still learning SQL Server...

    Thanks in advance from you SQL Server gurus!
    A1 See: http://dbforums.com/showthread.php?threadid=561221
    Or: http://dbforums.com/t546372.html

    A2 No.

    A3 Yes. (see A1, et. al.)

  4. #4
    Join Date
    Dec 2011
    Posts
    1

    Thumbs up Remedy



    This is the best solution i have ever read eor this problem.I had my sql 2000 db on website shooting to 183 mb with max 56 records in a table.I ran as described above and it reduced to mere 4mb.
    Thanks Matt
    Pls keep posting

    Quote Originally Posted by MCrowley View Post
    Skinny
    If this is truly a permissions problem, check to see that the login you are using is a member of the sysadmin server role. I do not remember if a mere db_owner can shrink files.
    Second check the options of the database properties. You may have this database on Full recovery model. Full is a good thing for production databases, as it is the only way you will ever get the database back from tape to a point in time. I have rarely seen Development servers on Full recovery.
    If the database is on Full recovery you must backup the transaction log, before you can truncate the log. Use thiscommand if you have an extra 5GB of diskspace handy:

    backup transaction databasename to disk = 'E:\filename.bak' with init

    Fill in your databasename and a filename (that hopefully does not exist), and you should be able to then use:

    dbcc shrinkfile (2, size_in_MB)

    This assumes that your log file has fileid = 2. Check in the sysfiles table to be sure.

    If you do not have 5GB free, you can just truncate (and discard) the transaction log, but you will very definitely want to make a full database backup immediately afterward. The command to discard the log is:

    backup transaction databasename with no_log

    Then run dbcc shrinkfile, and you should be all set. Hope you can recover the space.
    ~Matt

Posting Permissions

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