Re: whats in those Tlogs backups?

From: Andrew J. Kelly (sqlmvpnoooospam_at_shadhawk.com)
Date: 03/09/04 

Date: Tue, 9 Mar 2004 08:44:58 -0500

Did you try the RESTORE WITH HEADERONLY or FILELISTONLY as Tibor suggested? 

-- 
Andrew J. Kelly  SQL MVP
"Hassan" <fatima_ja@hotmail.com> wrote in message
news:uCfuW1ZBEHA.3748@tk2msftngp13.phx.gbl...
> 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
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages