Re: whats in those Tlogs backups?

From: Hassan (fatima_ja_at_hotmail.com)
Date: 03/09/04 

Date: Mon, 8 Mar 2004 22:11:17 -0800

No its not appended. Its a home grown log shipping plan that has Tlog files
appended with a datetime.

"Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> wrote in message
news:uivN3ZRBEHA.1452@TK2MSFTNGP09.phx.gbl...
> I agree, sounds like your not using WITH INIT and have more than 1 log
file
> in there.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> message news:uPqd00OBEHA.3184@TK2MSFTNGP09.phx.gbl...
> > Aren't you just appending to the tlog backup files? Check using RESTORE
> > HEARDERONLY.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Hassan" <fatima_ja@hotmail.com> wrote in message
> > news:%236p9fmLBEHA.688@tk2msftngp13.phx.gbl...
> > > You tell me Andrew... and hence the subject of my message. 2 GB data
> > files,
> > > and 10 GB worth of log files during the index defrag run. whats in
there
> ?
> > >
> > > "Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> wrote in message
> > > news:%234qeB2IBEHA.712@TK2MSFTNGP09.phx.gbl...
> > > > Hassan,
> > > >
> > > > How can your log backups be 10GB if your data files are only 2GB?
You
> > > state
> > > > your doing log backups every 5 minutes and I can't believe
Indexdefrag
> > is
> > > > creating 10GB logsin a 5 minute period. I believe Uri was referring
> to
> > > the
> > > > way Indexdefrag works. First off it will only work on 1 physical
file
> > at
> > > a
> > > > time. This means it will not swap any pages between files like a
full
> > > > DBreindex will do. Next in a nut shell it takes the first page in
the
> > > file
> > > > being defragged and checks to see if it belongs as the first page.
If
> > not
> > > > it finds the page that should be first and swaps the two pages. It
> then
> > > > moves to the next page and does the same thing. So essentially the
> > > overall
> > > > placement of the pages as a whole will stay about the same on disk.
> > Where
> > > > as a DBreindex will actually recreate a new table(in thcase of a
> > clustered
> > > > index) or a new index elsewhere on disk and then drop the original.
> Hope
> > > > that helps.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Hassan" <fatima_ja@hotmail.com> wrote in message
> > > > news:%23JmAmXHBEHA.2800@tk2msftngp13.phx.gbl...
> > > > > I am using DBCC Indexdefrag and we run it daily during off peak
> hours
> > .
> > > We
> > > > > run 5 min T log backups for availability ( Standby servers - Home
> > grown
> > > > log
> > > > > shipping ) . Our T log backup files during the index defrag run
are
> > > always
> > > > > amounting to greater than 10GB. As i said, our data files are
around
> > 2GB
> > > > and
> > > > > hardly increase significantly all day. And i was hoping that
> > indexdefrag
> > > > > after a few runs would reduce the size of Tlog backup files but
> sadly
> > > > enough
> > > > > its always so huge.
> > > > >
> > > > > What did Uri mean by
> > > > > "DBCC INDEXDEFRAG will not help if two indexes are interleaved on
> the
> > > > disk
> > > > > because INDEXDEFRAG shuffles the pages in place "
> > > > >
> > > > >
> > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
> > wrote
> > > > in
> > > > > message news:uwhnUoGBEHA.2348@TK2MSFTNGP09.phx.gbl...
> > > > > > Yep, exactly my point. All depends on the amount of
fragmentation
> in
> > > the
> > > > > > first place. :-)
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > >
> > > > > >
> > > > > > "Carlos Eduardo Rojas" <carloser@mindspring.com> wrote in
message
> > > > > > news:uMLX6DGBEHA.1028@TK2MSFTNGP11.phx.gbl...
> > > > > > > Actually, i've had cases where INDEXDEFRAG generates bigger
> > > > transaction
> > > > > > log
> > > > > > > backup files, so this is something you have to test before
doing
> > it.
> > > > > > >
> > > > > > > --
> > > > > > > Carlos E. Rojas
> > > > > > > SQL Server MVP
> > > > > > > Co-Author SQL Server 2000 Programming by Example
> > > > > > >
> > > > > > >
> > > > > > > "Tibor Karaszi"
> <tibor_please.no.email_karaszi@hotmail.nomail.com>
> > > > wrote
> > > > > > in
> > > > > > > message news:OIp5nEDBEHA.1796@TK2MSFTNGP12.phx.gbl...
> > > > > > > > Uri,
> > > > > > > >
> > > > > > > > Just in case Hassan used the term "index defrag" loosely.
> > Perhaps
> > > > > Hassan
> > > > > > > > really meant "index rebuild" (DBCC DBREINDEX) instead. In
that
> > > case,
> > > > > it
> > > > > > > can
> > > > > > > > be worth giving DBCC INDEXDEFRAG a shot. If fragmentation is
> low
> > > to
> > > > > > > > moderate, you will find that INDEXDEFRAG can generate a lot
> less
> > > log
> > > > > > > records
> > > > > > > > compared to DBREINDEX.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > > > >
> > > > > > > >
> > > > > > > > "Uri Dimant" <urid@iscar.co.il> wrote in message
> > > > > > > > news:%23fBVqMCBEHA.2484@TK2MSFTNGP12.phx.gbl...
> > > > > > > > > Hassan
> > > > > > > > > It seems you are runnimg DBCC INDEXDEFRAG command.A large
> > > amount
> > > > of
> > > > > > > > > fragmentation can cause DBCC INDEXDEFRAG to run
considerably
> > > > longer
> > > > > > than
> > > > > > > > > DBCC DBREINDEX, which may or may not outweigh the benefit
of
> > the
> > > > > > > command's
> > > > > > > > > online capabilities. DBCC INDEXDEFRAG will not help if two
> > > indexes
> > > > > are
> > > > > > > > > interleaved on the disk because INDEXDEFRAG shuffles the
> pages
> > > in
> > > > > > place.
> > > > > > > > To
> > > > > > > > > improve the clustering of pages, rebuild the index
> > > > > > > > >
> > > > > > > > > Can I ask you something? Why do you need to perfom T-LOG
> > BACKUP
> > > > > every
> > > > > > > five
> > > > > > > > > minutes?
> > > > > > > > > What time do you schedule index defrag ? I mean did you do
> it
> > > > during
> > > > > > the
> > > > > > > > > work day?
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Hassan" <fatima_ja@hotmail.com> wrote in message
> > > > > > > > > news:OjRYzKABEHA.684@tk2msftngp13.phx.gbl...
> > > > > > > > > > Ive got a database thats 2GB in size . Running index
> defrag
> > > once
> > > > a
> > > > > > day
> > > > > > > > > that
> > > > > > > > > > runs for 5 mins , results in T log backups of 10GB
> everytime
> > > > after
> > > > > > the
> > > > > > > > > > defrag completes. whats index defragging logging that
> causes
> > > the
> > > > > > Tlogs
> > > > > > > > to
> > > > > > > > > be
> > > > > > > > > > that huge. We run Tlog backups every 5 mins but the log
> file
> > > > sizes
> > > > > > at
> > > > > > > > > those
> > > > > > > > > > other times are just less than 1 MB. Please tell me
whats
> > > being
> > > > > > logged
> > > > > > > > and
> > > > > > > > > i
> > > > > > > > > > was under the assumption that running index defrag daily
> > helps
> > > > and
> > > > > > > might
> > > > > > > > > > reduce the logging but it always results in those huge
> 10GB
> > > > file.
> > > > > > > Using
> > > > > > > > > SQL
> > > > > > > > > > 2000
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>