Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: Why does my .ldf file keep growing?

    Why does a log (.ldf) file keep growing and growing and growing? Is this related to the fact that the scheduled Maintenance keeps failing due to exclusive access problems?

    SQL Server 2000 Std.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is it FULL recovery model?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by pootle flump
    Is it FULL recovery model?
    Yes .

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeppers - it is related to your backup problems. If you are using simple recovery then the log will not continue to grow and grow. If using full, and not backing up, the log will never get truncated and so will just keep growing. Really this is a symptom rather than a problem - you need to get your backups sorted.

    What are the exclusive access problems?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by pootle flump
    Yeppers - it is related to your backup problems. If you are using simple recovery then the log will not continue to grow and grow. If using full, and not backing up, the log will never get truncated and so will just keep growing. Really this is a symptom rather than a problem - you need to get your backups sorted.

    What are the exclusive access problems?
    I guess by "backup" you mean weekly maintenance.

    The Index Rebuilds work OK, but the Maintenance gets an Exclusive Violation. Here's the Text:

    -----Inline Attachment Follows-----


    Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'CBSQL-P1' as 'CENTURYBLIND\Administrator' (trusted)
    Starting maintenance plan 'Sunday Maintenance' on 7/16/2007 10:17:01 PM
    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database 'MASTER'.
    [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
    [1] Database master: Check Data and Index Linkage...
    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

    The following errors were found:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [2] Database model: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'msdb'
    [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
    [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
    [3] Database msdb: Check Data and Index Linkage...
    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

    The following errors were found:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'wfab'
    [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
    [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
    [4] Database wfab: Check Data and Index Linkage...
    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

    The following errors were found:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

    Deleting old text reports... 0 file(s) deleted.

    End of maintenance plan 'Sunday Maintenance' on 7/16/2007 10:17:03 PM
    SQLMAINT.EXE Process Exit Code: 1 (Failed)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually that wasn't what I meant.

    I don't use maintenance plans but did you set up the plan to backup the database? We can look at the reindexing later.

    BTW - <sigh /> I don't know why the multiple post thing happens.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note: Flump stutter thrown (duplicate posts removed).

    -PatP

  8. #8
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by pootle flump
    Actually that wasn't what I meant.

    I don't use maintenance plans but did you set up the plan to backup the database? We can look at the reindexing later.

    BTW - <sigh /> I don't know why the multiple post thing happens.
    I do nightly backups via Symantec Backup Exec SQL Agent.

    I do scheduled backup every 6 hours via the IE Maintenance Plans.

    I do scheduled log-switch every 30 minutes beginning at 4:20AM until after 6PM. (I manually ship the TRN and BAK files to another server via a Job Scheduler)

    Note: One of my TRN files (the first of the day) was 1.7GB, but most are much smaller.

    For example: In the backup directory on the server right now, I see a .BAK file of 1.9GB from 9:13AM, then 3 .TRN files after it (about 2MB each) with names like WFAB_tlog200708070920.TRN. The .TRN file from 4:51 AM is 1.3GB.

    Last night I rebuilt the .LDF file and forced it to 2.2GB to avoid fragmentation.

    Here's my theory. The .LDF is file like a bucket that only grows, never contracts. Every DB change gets logged and the .LDF has to be big enough to hold all the DB changes between my 30 minute log swaps (or full backups), at which time the bucket gets dumped.

    When I ask "it keeps growing and growing", I mean that I've never seen it get smaller, and the size always seems to trail the database size a bit (DB = 2.2GB, then log = 1.5GB).

    This is all guesswork, but it seems like a reasonable hypothisis. Is that how it works? I just got real curious when my "Log Move Instructions" involved deleting and rebuilding it. I forced it to 2GB (based on that theory) just to keep fragmentation down (because I first did EXTENSIVE defragmentation and disk file clean-up from that drive).




    Unrelated question that just popped in my head. If the Symantec SQL Agent ran simultaneously with either Log Switch or Maintenance Plan Backup (actually, I'm not sure if the job was built via Maintenance Plan or if I just added it to the SQL Agent Job list with a regular backup command - same difference right?), then would this cause a potential conflict ? It doesnt' seem to, but I don't run the SQL Server Maint Plans one at night.
    Last edited by vich; 08-07-07 at 15:09.

Posting Permissions

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