Post reply

Why a tran log size jump?

  • April 15, 2005 at 11:16 am

    #110541

    Okay......This database is setup for full recovery with a full at 7:30PM daily and hourly tran logs with a 3 day retention.

    We have a process that runs EOD against this DB at 6PM and is generally done by 7PM.

    I run Integrity checks at midnight and optimizations at 1AM.

    I am running a trace 24/7 against this DB that dumps to files. No one should be in at 3AM.

    But if you look at the backup files everyday at 3AM I get a giant burp of the tran logs.

    The errorlog is also below. I've just extracted the ones for this database. But all the rest of the events in the log from 01:00:04.17 are just Log backed up: ov the various DB's

    Anyone have any ideas of what to check? 

    04/14/2005  07:53 PM     3,863,414,272 TCL_db_200504141951.BAK
04/14/2005  08:00 PM        10,224,128 TCL_tlog_200504142000.TRN
04/14/2005  09:00 PM           260,608 TCL_tlog_200504142100.TRN
04/14/2005  10:00 PM           327,168 TCL_tlog_200504142200.TRN
04/14/2005  11:00 PM           260,608 TCL_tlog_200504142300.TRN
04/15/2005  12:00 AM           260,608 TCL_tlog_200504150000.TRN
04/15/2005  01:00 AM           260,608 TCL_tlog_200504150100.TRN
04/15/2005  03:00 AM     2,718,052,864 TCL_tlog_200504150258.TRN
04/15/2005  04:00 AM        40,519,168 TCL_tlog_200504150400.TRN
04/15/2005  05:00 AM           343,552 TCL_tlog_200504150500.TRN
04/15/2005  06:00 AM           276,992 TCL_tlog_200504150600.TRN
04/15/2005  07:00 AM           276,992 TCL_tlog_200504150700.TRN
04/15/2005  08:00 AM           343,552 TCL_tlog_200504150800.TRN
04/15/2005  09:00 AM           736,768 TCL_tlog_200504150900.TRN
04/15/2005  10:00 AM         1,261,056 TCL_tlog_200504151000.TRN

    2005-04-15 01:00:11.59 backup Log backed up: Database: TCL, creation date(time): 2005/03/01(23:51:19), first LSN: 76382:12834:1, last LSN: 76382:12834:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'L:\MSSQL\BACKUP\TCL\TCL_tlog_200504150100.TRN'}).

    2005-04-15 03:00:50.79 backup Log backed up: Database: TCL, creation date(time): 2005/03/01(23:51:19), first LSN: 76382:12834:1, last LSN: 76635:13657:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'L:\MSSQL\BACKUP\TCL\TCL_tlog_200504150258.TRN'}).

    2005-04-15 04:00:14.39 backup Log backed up: Database: TCL, creation date(time): 2005/03/01(23:51:19), first LSN: 76635:13657:1, last LSN: 76637:41:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'L:\MSSQL\BACKUP\TCL\TCL_tlog_200504150400.TRN'}).

    2005-04-15 05:00:11.79 backup Log backed up: Database: TCL, creation date(time): 2005/03/01(23:51:19), first LSN: 76637:41:1, last LSN: 76637:45:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'L:\MSSQL\BACKUP\TCL\TCL_tlog_200504150500.TRN'}).

    2005-04-15 06:00:11.86 backup Log backed up: Database: TCL, creation date(time): 2005/03/01(23:51:19), first LSN: 76637:45:1, last LSN: 76637:45:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'L:\MSSQL\BACKUP\TCL\TCL_tlog_200504150600.TRN'}).

    2005-04-15 07:00:14.14 backup Log backed up: Database: TCL, creation date(time): 2005/03/01(23:51:19), first LSN: 76637:45:1, last LSN: 76637:45:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'L:\MSSQL\BACKUP\TCL\TCL_tlog_200504150700.TRN'}).



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • April 16, 2005 at 3:33 am

    #553045

    What optimizations do you run against your db at 1 AM? Index rebuild/defrag? That could be the culprit.

  • April 18, 2005 at 6:21 am

    #553123

    concur with Martin.  we had a similar schedule, with integrity check and and index rebuilds AFTER full backup, so txnlog will show all that work, hence your burp.

    recently rescheduled integrity/re-index before nightly full backup, and txnlog size has reduced to regular size (by comparison against hourly txnlog backup sizes throughout the day).  not necessarily the safest route to go (if yer rebuilds go awry, you have an entire day you'll have to restore), but reduces size of full backups and txn logs as well.

  • April 21, 2005 at 9:52 am

    #554122

    Sorry I took so long to get back on this.....

    I can't do the intregity checks prior because we have to have the DBs available to users until 7:00PM EST. So I can't do repairs. And the nightly tape backups kick off between 8P and 9P so I want to have the fulls out roughly by then.

    I guess I'll just have to live with this.

    Thanks for the input.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply