Re: Log-size
From: AnthonyThomas (AnthonyThomas_at_discussions.microsoft.com)
Date: 11/02/04
- Next message: Steve Thompson: "Re: copying table data from 1 dbase to another"
- Previous message: Ajit: "BackUp - Device Activation Error"
- In reply to: Tibor Karaszi: "Re: Log-size"
- Next in thread: Melih SARICA: "Re: Log-size"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 2 Nov 2004 10:39:01 -0800
Ah, there's the rub. And yes, although I prefaced it, I did offend you. My
apoligies. I did not intend to attack your credentials.
Here is my problem--only because I see it offered on the threads all too
often. The user had an immediate problem: their transaction log file(s) were
full. Now, you and I know that was because the "trunc. log on checkpoint"
was not set, for version 7.0, or they were running in Bulk Insert or Full
recovery, for version 2000.
Now, what I see too often is the advice you gave--actually, I see the other
respondents answer more often (BACKUP LOG xxxx WITH NO_LOG). But the point
is not whether or not they are doing transaction log backups but, rather,
SHOULD THEY.
You nor I can tell without knowing the business requirements. The
transaction log is gold. It is their only database modification audit
history. It is more valuable than the online database. The online database
can be blown up, the server can crash, the disks can explode; however, with
at least one FULL backup and the the entire transaction log record, via
periodic transaction log backups, I can recover that database from the FULL
to any point in time since the very first time that database came online.
The user has too choices: change the recovery model to support their
activity, or conduct activity that supports the users' recovery requirements.
The correct response was the one I gave:
"The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario."
My point was not that your suggestion was incorrect, but, without qualifying
the systems requirements, it would be impossible for anyone to give an
appropriate response.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Anthony, (Thomas?)
>
> Let me quote my earlier reply:
>
> "Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
>
> And you said:
>
> > "What is the best and safest way to reduce size of the log? Of course I
> > need to be sure that no data is getting lost."
> >
> > Setting the "trunc. log on checkpoint" will not help to protect the user's
> > data.
>
> If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
> don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
> newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
> log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
> log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
> stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
> I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
> the transaction log for you. If you don't agree with this, I would like to hear exactly with what
> you disagree.
>
> You also said:
>
> > You also made a comment about "breaking the transaction log chain" in
> > response to another respondent's suggestion that this user BACKUP LOG ...
> > WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> > transaction log chain?
>
> The difference is, again, that I qualified my statement with "If you don't do transaction log
> backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
> schedule in place and running this statement will break such a log backup chain.
>
> You also stated:
>
> > The fact that your transaction log keeps growing means you have not set up
> > your recovery model correctly.
>
> Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
> but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
> earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
> log on checkpoint".
>
> > Another word of caution, newsgroups have respondendents of all skill levels.
> > Do not ever take anyone's recommendation without careful consideration. At
> > the end of the day, you are the one still responsible for the integrity and
> > availability of your system.
>
> This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
> questions don't know the ones who reply and the ones who are posting the replies don't know much
> about the OP's requirements. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "AnthonyThomas" <AnthonyThomas@discussions.microsoft.com> wrote in message
> news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@microsoft.com...
> > Tibor, not to offend you, but I'm suprised at your response. Are you really
> > an MVP?
> >
> > The poster said,
> >
> > "What is the best and safest way to reduce size of the log? Of course I
> > need to be sure that no data is getting lost."
> >
> > Setting the "trunc. log on checkpoint" will not help to protect the user's
> > data.
> >
> > You also made a comment about "breaking the transaction log chain" in
> > response to another respondent's suggestion that this user BACKUP LOG ...
> > WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> > transaction log chain?
> >
> > Look, Klaus, what to do depends on what level of recoverability you need to
> > provide the users of this database. This ranges from none (in the case that
> > this is some sort of warehouse or mart were the database could be rebuilt
> > from the other primary data sources) to full point in time recovery (for
> > sensitive, transaction level OLTP recovery).
> >
> > The fact that your transaction log keeps growing means you have not set up
> > your recovery model correctly.
> >
> > In order to assist you, we will need to know what the user's requirements
> > are in terms of recovering this database in some sort of disaster scenario.
> >
> > Another word of caution, newsgroups have respondendents of all skill levels.
> > Do not ever take anyone's recommendation without careful consideration. At
> > the end of the day, you are the one still responsible for the integrity and
> > availability of your system.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As
> for
> > > shrinking the physical files size, see the articles at the middle of this article:
> > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Klaus" <anonymous@discussions.microsoft.com> wrote in message
> > > news:111f01c4c0c1$f6894970$a501280a@phx.gbl...
> > > > Hi
> > > >
> > > > MSSQLServer 7.
> > > >
> > > > The size of a database (in production) log is getting
> > > > close to 9 gigabyte. It seems that everything is being
> > > > kept.
> > > >
> > > > What is the best and safest way to reduce size of the
> > > > log? Of course I need to be sure that no data is getting
> > > > lost.
> > > >
> > > > Tia
> > > > Klaus
> > >
> > >
> > >
>
>
>
- Next message: Steve Thompson: "Re: copying table data from 1 dbase to another"
- Previous message: Ajit: "BackUp - Device Activation Error"
- In reply to: Tibor Karaszi: "Re: Log-size"
- Next in thread: Melih SARICA: "Re: Log-size"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
