Monday, March 19, 2012
log file incremental growth question
2005 from 2000. While upgrading the msdb database we noticed that the
active process was mostly locked waiting on new transaction log disk
allocation (I forget the wait name). msdb's transaction log was set
to 10% growth. But it never appeared to use 10% of the _current_ log
size, is it possible that the 10% figure is determined at the
beginning of a transaction and not updated as the transaction
proceeds? Monitoring the log file size from the os showed it to be
growing rapidly in small increments.
We know we need to change the default log growth to something more
substantial, but what about the question, is it 10% of the current log
size or 10% of the log as it was when the transaction started?
Thanks.
P. Kline
DBA> We know we need to change the default log growth to something more
> substantial, but what about the question, is it 10% of the current log
> size or 10% of the log as it was when the transaction started?
The way I understand it, it is 10% of the size at the instance the grow occurs. Say the file is 10MB
and a grow occurs. The grow occurs because something need space - but SQL Server do not know at the
moment how much space will be needed *in the end* for that process/transaction.
So at the moment in time it grows to 11MB. Say that the 1 MB wasn't enough and the process still
need more space. This would make the file grow 10% based on the what is now current size: 11MB =>
grow with 1.1MB. etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<pauldkline@.aol.com> wrote in message
news:b9a8ff5c-8ee0-46ff-8c5e-184665daed04@.72g2000hsu.googlegroups.com...
> Last night we upgraded a server with multiple databases to SQL Server
> 2005 from 2000. While upgrading the msdb database we noticed that the
> active process was mostly locked waiting on new transaction log disk
> allocation (I forget the wait name). msdb's transaction log was set
> to 10% growth. But it never appeared to use 10% of the _current_ log
> size, is it possible that the 10% figure is determined at the
> beginning of a transaction and not updated as the transaction
> proceeds? Monitoring the log file size from the os showed it to be
> growing rapidly in small increments.
> We know we need to change the default log growth to something more
> substantial, but what about the question, is it 10% of the current log
> size or 10% of the log as it was when the transaction started?
> Thanks.
> P. Kline
> DBA
Monday, March 12, 2012
Log File Backups
incremental log file backups every hour between 6am and 8pm. I notice that
the first log file backup seem to be extremely large 5gig, the database
backup that is created 7 hours prior to that is 4.1 gig. (SQL2000
standard - SP4) Can someone tell me if there is a better way to run these
job and what are the best parameters to include in a job to run this type of
job ' OR is it a normal thing for the first log file backup to be so
huge? Thanks,...WANNABE wrote:
> I am running regular maintenance jobs to back up database files nightly and
> incremental log file backups every hour between 6am and 8pm. I notice that
> the first log file backup seem to be extremely large 5gig, the database
> backup that is created 7 hours prior to that is 4.1 gig. (SQL2000
> standard - SP4) Can someone tell me if there is a better way to run these
> job and what are the best parameters to include in a job to run this type of
> job ' OR is it a normal thing for the first log file backup to be so
> huge? Thanks,...
>
If there is alot of activity in the database during the 7 hours between
the full backup and the first log backup, then yes, it will be large.
What processes are run against that database overnight?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, there are about 15 Stored Procs that run during the night to
recalculate
values, and adjustments.. But for the log backup to be larger then the db
backup
I was puzzled. Thanks
=============================================="Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45534747.70107@.realsqlguy.com...
> WANNABE wrote:
>> I am running regular maintenance jobs to back up database files nightly
>> and incremental log file backups every hour between 6am and 8pm. I
>> notice that the first log file backup seem to be extremely large 5gig,
>> the database backup that is created 7 hours prior to that is 4.1 gig.
>> (SQL2000 standard - SP4) Can someone tell me if there is a better way
>> to run these job and what are the best parameters to include in a job to
>> run this type of job ' OR is it a normal thing for the first log file
>> backup to be so huge? Thanks,...
> If there is alot of activity in the database during the 7 hours between
> the full backup and the first log backup, then yes, it will be large. What
> processes are run against that database overnight?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||WANNABE wrote:
> Yes, there are about 15 Stored Procs that run during the night to
> recalculate
> values, and adjustments.. But for the log backup to be larger then the db
> backup
> I was puzzled. Thanks
The log file is basically a "journal", recording everything that happens
in the database. Those journal entries continue to accumulate until you
perform a log backup, at which point they are flushed out. You might
consider doing log backups around the clock, rather than just between
6am and 8pm...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, I will try log file backups around the clock
=========================================="Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45534CF9.2040700@.realsqlguy.com...
> WANNABE wrote:
>> Yes, there are about 15 Stored Procs that run during the night to
>> recalculate
>> values, and adjustments.. But for the log backup to be larger then the
>> db backup
>> I was puzzled. Thanks
> The log file is basically a "journal", recording everything that happens
> in the database. Those journal entries continue to accumulate until you
> perform a log backup, at which point they are flushed out. You might
> consider doing log backups around the clock, rather than just between 6am
> and 8pm...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Friday, February 24, 2012
LOG Backup and Remote Recovery - Msg 4330
Hi,
We have remote offices running SQL 2000, which are on a small bandwidth pipeline, so we implemented an incremental backup schema where they back up their LOGs overnight and send it to our main office. After an initial full backup, they just keep sending **incremental** LOG backups.
In the main office we restore the full backup of each remote office and from then on do a restore of each night's LOG file (with standby option), so that we keep in sync with them. The issue we have now is that we cannot get regular full backups from the remote (and sometimes independent) offices on a regular basis - maybe only once a *quarter*.
If we use differential backups, the size of each one grows each day, until we get a full backup. Incremental backups thru LOGs seemed to be the only way to go. *HOWEVER*, we now have multiple LOG files sitting on our server in the main office and since the databases that we restored to is in warm-standby and read-only mode, we cannot make a backup of them in-house, without breaking the LOG restore chain.
That also means that we would not be able to easily re-create the environment locally, should something go wrong - we would have to apply all of the LOG restores for (potentially) the whole quarter!
I even tried to create two restored databases - one where I keep it in standby state and ready to receive more incremental LOG backups and another where I would take it out of standby mode (and into recovery mode). At that point I am then able to make a full backup locally of this second database. *BUT*, when I then try to restore the next day's LOG backup to this second database, it fails with an error
- Msg 4330 - Level 16, State 4, Line 1
- The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
How do I do incremental backups and restores, while maintaining the ability to make a new full backup of the restored database, before the restore sequence is done?
Help !
You can restore the full backup with standby option and subsequent tlogs using with standby option so that the db will be in read-only mode..........
OR you can also restore the full backup and Tlog using with norecovery mode (i.e leave db nonoperational but apply tlogs)
you need to use any of the above 2 options to perform log shipping...........i think you are using the option "Leave db operational so that no tlogs could be applied" or the log backups are not being restored sequentially
Thanxx
Deepak