Locks and Blocks: active

Showing posts with label active. Show all posts

Monday, March 19, 2012

log file incremental growth question

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> 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

Wednesday, March 7, 2012

Log backup question.

1).How to restore the active log.
I backup the DB but didn't backup log .
Is there a way to apply the active log after restore the DB
2)How to backup an up-to-date log.
How to ensure the log is backuped right on time when the DB is crashed.
I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
this
Thanks!!1)
No can do if db is in simple recovery mode. If db is in full recovery mode, see point 2. However, if you never
do log backup, and run in full recovery mode, log will never be truncated. So, recovery modes and whether you
do log backups go hand-in-hand.
2)
After the crash, do a log backup using the NO_TRUNCATE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Straight" <someone@.> wrote in message news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
> 1).How to restore the active log.
> I backup the DB but didn't backup log .
> Is there a way to apply the active log after restore the DB
> 2)How to backup an up-to-date log.
> How to ensure the log is backuped right on time when the DB is crashed.
> I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
> this
> Thanks!!
>|||TK;
for point 2, log backup NO_TRUNCATE, does this apply to any recovery model
in general ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esY$n5ELEHA.2532@.TK2MSFTNGP10.phx.gbl...
> 1)
> No can do if db is in simple recovery mode. If db is in full recovery
mode, see point 2. However, if you never
> do log backup, and run in full recovery mode, log will never be truncated.
So, recovery modes and whether you
> do log backups go hand-in-hand.
> 2)
> After the crash, do a log backup using the NO_TRUNCATE option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Straight" <someone@.> wrote in message
news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
> > 1).How to restore the active log.
> > I backup the DB but didn't backup log .
> > Is there a way to apply the active log after restore the DB
> > 2)How to backup an up-to-date log.
> > How to ensure the log is backuped right on time when the DB is crashed.
> > I am afraid ,even a scheduled log-backup with interval of 1 Min can't do
> > this
> >
> > Thanks!!
> >
> >
>|||Hi,
Only for Bulk_logged and FULL recovery models.
Thanks
Hari
MCDBA
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:usl0UTFLEHA.2244@.tk2msftngp13.phx.gbl...
> TK;
> for point 2, log backup NO_TRUNCATE, does this apply to any recovery model
> in general ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:esY$n5ELEHA.2532@.TK2MSFTNGP10.phx.gbl...
> > 1)
> > No can do if db is in simple recovery mode. If db is in full recovery
> mode, see point 2. However, if you never
> > do log backup, and run in full recovery mode, log will never be
truncated.
> So, recovery modes and whether you
> > do log backups go hand-in-hand.
> >
> > 2)
> > After the crash, do a log backup using the NO_TRUNCATE option.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "Straight" <someone@.> wrote in message
> news:OVkPazELEHA.1120@.TK2MSFTNGP11.phx.gbl...
> > > 1).How to restore the active log.
> > > I backup the DB but didn't backup log .
> > > Is there a way to apply the active log after restore the DB
> > > 2)How to backup an up-to-date log.
> > > How to ensure the log is backuped right on time when the DB is
crashed.
> > > I am afraid ,even a scheduled log-backup with interval of 1 Min can't
do
> > > this
> > >
> > > Thanks!!
> > >
> > >
> >
> >
>

Monday, March 19, 2012

log file incremental growth question

Wednesday, March 7, 2012

Log backup question.

Log backup question.

Log backup question.

Locks and Blocks

Blog Archive

About Me