Re: transaction log backup & file size
From: Peter Yeoh (nospam_at_nospam.com)
Date: 08/09/04
- Next message: Brian Taylor: "Re: SQL Agent Mail grayed out"
- Previous message: pnp: "Re: Executing large result queries"
- In reply to: Liliya Huff: "Re: transaction log backup & file size"
- Next in thread: Liliya Huff: "Re: transaction log backup & file size"
- Reply: Liliya Huff: "Re: transaction log backup & file size"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 9 Aug 2004 09:39:42 +0800
> user transactions - that would be my normal db activity.
> system - not really sure what those are... not mine, and they look like
> something loops bad...
Between 2004-08-04 18:00:01 - 2004-08-04 19:00:00, any chance you or someone
else schedules a DBCC DBREINDEX to run? This could explain the high number
of 'system transactions' (only guessing, as I've never used Lumigent's
product).
-- Peter Yeoh http://www.yohz.com Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free! "Liliya Huff" <LiliyaHuff@discussions.microsoft.com> wrote in message news:4552DF9E-194F-4812-B56D-173931F1F9D2@microsoft.com... > > It is unusual to backup log with truncate_only.... What this means is that > > you can only restore your database from the previous nights backup... What > > if that file is missing or corrupt... You're screwed.... > > I'm screwed even more in case of XXX GB log that I can not backup on network. > > in general here is the current sequence: > 1:00 am - backup log + truncate like below. > step 1. > EXECUTE master.dbo.xp_sqlmaint N'-PlanID > DFF138BD-3241-49DC-9949-BCB6CAD2ED16 -WriteHistory -VrfyBackup -BkUpMedia > DISK -BkUpLog "D:\MSSQL\MSSQL\BACKUP\BBH" -DelBkUps 7DAYS -CrBkSubDir -BkExt > "TRN"' > > step 2. > alter database BBH set single_user with rollback immediate > go > alter database BBH set recovery simple > go > dbcc shrinkfile('BBH_log','truncateonly') > go > /*that one breakes log sequence as I understand, but I don't know any other > way to shring XXX GB log*/ > alter database BBH set multi_user > go > alter database BBH set recovery full > > then 1:30 am - full db backup > then 2am-11:30 pm hourly log backup with no truncate etc. > during that bacup I can get wired file sizes. > thoretically that las log backup sequence have to be solid. and I breake log > sequence only at 1:00 am when I truncate the log in case if it's huge. > > now why it gets huge: > an example (analysis is done by lumigent log reader, don't have anything > else available) > interval: 2004-08-04 18:00:01 - 2004-08-04 19:00:00 > transactions: 2069585 trans/sec: 575.0 > user: 62057 3:0% > system: 2007528 97.0% > dropped objects: 0 > page splits: 5 > > insert: 45286 2.2% > delete: 16293 0.8% > modify: 478 0.0% > abort: 0 > > user transactions - that would be my normal db activity. > system - not really sure what those are... not mine, and they look like > something loops bad... > > at about 18:23:10 I see good deal of transactions like > > time > 08/04/2004 18:23:10.853 > transid ... > opcode begin_xact > table - empty > index - empty > uid - dbo > spid - 54 > desc - dml > > 08/04/2004 18:23:10.853 > transid ... > opcode commit_xact > table - empty > index - empty > uid - dbo > spid - 54 > desc - dml > > and I see 20-30 of them with the same timestamp. they described by lumigent > as a system transaction. what do they mean by that - could find out from ther > tech support. > and all that log looks like that ... 947m. > 08/04/2004 02:00a 131,154,432 BuyBankHomes_db_200408040200.BAK > 08/04/2004 01:00a 27,551,232 BuyBankHomes_tlog_200408040100.TRN > 08/04/2004 05:00a 145,920 BuyBankHomes_tlog_200408040500.TRN > 08/04/2004 02:29p 146,191,872 BuyBankHomes_tlog_200408041429.TRN > 08/04/2004 02:35p 96,768 BuyBankHomes_tlog_200408041435.TRN > 08/04/2004 03:00p 162,304 BuyBankHomes_tlog_200408041500.TRN > 08/04/2004 04:00p 25,788,928 BuyBankHomes_tlog_200408041600.TRN > 08/04/2004 05:00p 25,658,880 BuyBankHomes_tlog_200408041700.TRN > 08/04/2004 06:00p 25,462,272 BuyBankHomes_tlog_200408041800.TRN > 08/04/2004 07:00p 1,035,230,720 BuyBankHomes_tlog_200408041900.TRN > 08/04/2004 08:01p 1,664,825,856 BuyBankHomes_tlog_200408042000.TRN > 08/04/2004 09:01p 1,923,087,872 BuyBankHomes_tlog_200408042100.TRN > 08/04/2004 10:01p 1,969,298,944 BuyBankHomes_tlog_200408042200.TRN > 08/04/2004 11:00p 1,221,263,872 BuyBankHomes_tlog_200408042300.TRN > 08/05/2004 01:02a 2,956,985,856 BuyBankHomes_tlog_200408050100.TRN > that one was with truncate only > 08/05/2004 02:00a 131,904,000 BuyBankHomes_db_200408050200.BAK > here is full backup and looks like crazy stuff eather stopped eather log > sequence was broken by prew. log backup and it affected the process somehow > 08/05/2004 05:00a 17,189,376 BuyBankHomes_tlog_200408050500.TRN > > > the situation happends randomly, I've got it 3 times, 2 times I couldn't get > the log for analysis (copied on network withut it's tail or something). at > 04-th I've got 11gb of that stuff. > > Does anybody know anything about lumigent tool here? I inherited that stuff > from prev. dba that is not working here anymore. Never used it... don't like > 3-rd party stuff that writes itself down in master db even if they are just > extended sp... > > Regards, Liliya >
- Next message: Brian Taylor: "Re: SQL Agent Mail grayed out"
- Previous message: pnp: "Re: Executing large result queries"
- In reply to: Liliya Huff: "Re: transaction log backup & file size"
- Next in thread: Liliya Huff: "Re: transaction log backup & file size"
- Reply: Liliya Huff: "Re: transaction log backup & file size"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
