Suspicious Transaction Log.

Posted on 2006-06-07
Last Modified: 2012-08-14
Not really sure if this is a problem but it falls under the, 'I don't recall that looking like that', category.

One of the Transaction logs on one of my SQL2000(sp3) DB seems unusually large.

The db itself is, 3,000,000Kb but the accompanying Trans. Log is 54,000,000Kb.

I run a MaintPlan at 12 each night that utilizes all the tabs (Optimiziation, Backup Db, Backup Logs, etc.) then I use Veritas to come along around 3 and back up everything to tape.

Couple things are bothering me.  

First, the size of the trans log. Second, the fact that neither the MaintPlan or the tape backup are flushing or compacting the trans log and third, the fact that I am starting to see various events and log entries making mention of the fact the the trans log backup failed due to disk space (have 35Gb free. surely that's enough).

Do I have a problem?
Question by:mbath20110
    LVL 75

    Accepted Solution

    >>have 35Gb free. surely that's enough<<
    Perhaps I am stating the obvious, but 54,000,000Kb. > 35GB

    Or am I missing something?
    LVL 27

    Expert Comment

    Do you have the maint plan or tape backup performing a shrink file command? I assume that this database is in recovery model FULL.

    LVL 42

    Expert Comment


    run Transaction log backup - looks like you have Full recovery mode
    then shrink the database\log file
    and if you do not need transaction log be backed up (for the dbrestore topoint of failure) just swith Recovery to Simple (see in EM DB properies)
    you do not have much free space for Optimiziation (it is reindexing)
    so or get more HDD space or use Index Defrag instead reindex
    oe reindex table by table - and truncate - shrink database, etc..
    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    LVL 10

    Assisted Solution

    A shrink will only work if your log file has freespace in it ... if it hasent you could do the following to create a new log file

    1, Backup the db
    2, Detach the DB
    3, rename the logfile <name>.old
    4, in EM try and reatach the DB .. if will warn you that there is no log file and will offer to try and create a new log file for you , this usualy works
    5, if it worked then delete the logfile <name>.old ...   if it didnt work rename the logfile <name>.old to the correct name and reattach the DB
    LVL 42

    Assisted Solution

    idea and solutions have been provided

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how the fundamental information of how to create a table.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now