Log-size

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
KlausAre you performing regular transaction log backups? If not, set "truncate lo
g on checkpoint". As for
shrinking the physical files size, see the articles at the middle of this ar
ticle:
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|||backup transaction log with truncate only param.
"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|||That will not shrink the physical file size, and also it will break a log ba
ckup sequence...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> backup transaction log with truncate only param.
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>|||Hi lads
Thanks a lot for your quick answers.
I will read through the articles.
Klaus

>--Original Message--
>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...
getting[vbcol=seagreen]
>
>.
>|||Dear Tibor;
I ave to talk to u about quality Learning Pape.
Is it possible ? U can find me from melihsarica(at)Hotmail
thnx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> That will not shrink the physical file size, and also it will break a log
backup sequence...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
>|||Sure. Mail sent... Let me know I you didn't receive it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:u$F3qINwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> Dear Tibor;
> I ave to talk to u about quality Learning Pape.
> Is it possible ? U can find me from melihsarica(at)Hotmail
>
> thnx
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> backup sequence...
>|||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 th
e
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...
>
>|||Anthony, (Thomas?)
Let me quote my earlier reply:
"Are you performing regular transaction log backups? If not, set "truncate l
og 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 i
n my advice. If you
don't do transaction log backups, the log will expand to finally fill the di
sk. I've been on the
newsgroups since 1997 regularly and we see posts all the time where users ar
en't doing transaction
log backups but still run the database in full recovery mode (which in 7.0 i
s 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 b
ackup. Etc.
I.e., if you don't find it necessary to do log backup, you are best suited t
o let SQL server empty
the transaction log for you. If you don't agree with this, I would like to h
ear 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 d
o transaction log
backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that the
re might be a log backup
schedule in place and running this statement will break such a log backup ch
ain.
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 wa
s 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 d
atabase option "truncate
log on checkpoint".

> Another word of caution, newsgroups have respondendents of all skill level
s.
> 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 an
d
> availability of your system.
This I absolutely agree with. Communication over the newsgroup is a bit dang
erous as those who post
questions don't know the ones who reply and the ones who are posting the rep
lies 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...[vbcol=seagreen]
> Tibor, not to offend you, but I'm suprised at your response. Are you real
ly
> 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 t
o
> provide the users of this database. This ranges from none (in the case th
at
> 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 level
s.
> 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 an
d
> availability of your system.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
>
for[vbcol=seagreen]|||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) wer
e
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:
>
> 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 to
p 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:
>
> 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 t
here might be a log backup
> schedule in place and running this statement will break such a log backup
chain.
> You also stated:
>
> 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 S
QL Server 7.0 (and
> earlier) is not "recovery model" in Books Online, but instead look for the
database option "truncate
> log on checkpoint".
>
> This I absolutely agree with. Communication over the newsgroup is a bit da
ngerous as those who post
> questions don't know the ones who reply and the ones who are posting the r
eplies 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...
> for
>
>