How to empty log file in SQL 2005?

I have been trying to shrink or emptly a log file in one of my Databases but
to no evail. When I try to shrink it only shrinks it by a little bit and
grows to the original size within seconds.
Is there a way to completely empty a log file. It about 80 GB and something
is now right...
Thank you.
Peace in Christ
Marco Napoli
http://www.ourlovingmother.orgMarco Napoli wrote:
> I have been trying to shrink or emptly a log file in one of my Databases b
ut
> to no evail. When I try to shrink it only shrinks it by a little bit and
> grows to the original size within seconds.
> Is there a way to completely empty a log file. It about 80 GB and somethin
g
> is now right...
>
> Thank you.
>
When was the last time you backed up the transaction log? Sounds like
never, which means there are transactions piling up in there. Until
those are flushed out, you can't shrink it.|||can u change it to simple mode?
"Marco Napoli" <marco@.avantitecnospam.com> glsD:uZewtPilGHA.5108@.TK2MSFTNGP03.phx
.gbl...
>I have been trying to shrink or emptly a log file in one of my Databases
>but to no evail. When I try to shrink it only shrinks it by a little bit
>and grows to the original size within seconds.
> Is there a way to completely empty a log file. It about 80 GB and
> something is now right...
>
> Thank you.
> --
> Peace in Christ
> Marco Napoli
> http://www.ourlovingmother.org
>|||How would I flush them?
Thanks.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OA34pVilGHA.2136@.TK2MSFTNGP04.phx.gbl...
> Marco Napoli wrote:
> When was the last time you backed up the transaction log? Sounds like
> never, which means there are transactions piling up in there. Until those
> are flushed out, you can't shrink it.|||Marco Napoli wrote:
> How would I flush them?
>
If you don't need the ability to do point-in-time recovery, put the
database in Simple recovery mode and the log will flush itself at the
next checkpoint. With an 80GB log file, this might take a few minutes.
I'm guessing you aren't concerned with point-in-time recovery, since
you're not backing up the transaction log. If I'm wrong, then you need
to do some reading about backups and the transaction log, because you're
not taking care of your database right now.
To solve your immediate problem, run BACKUP LOG WITH TRUNCATE_ONLY
against your database.|||As of right now the point-in-time recovery is not necessary but I am working
my way to that. The maintenance plan makes a full back at certain time.
Thank you for the help I will try it.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQRyYrilGHA.4772@.TK2MSFTNGP04.phx.gbl...
> Marco Napoli wrote:
> If you don't need the ability to do point-in-time recovery, put the
> database in Simple recovery mode and the log will flush itself at the next
> checkpoint. With an 80GB log file, this might take a few minutes.
> I'm guessing you aren't concerned with point-in-time recovery, since
> you're not backing up the transaction log. If I'm wrong, then you need to
> do some reading about backups and the transaction log, because you're not
> taking care of your database right now.
> To solve your immediate problem, run BACKUP LOG WITH TRUNCATE_ONLY against
> your database.|||Marco Napoli wrote:
> As of right now the point-in-time recovery is not necessary but I am worki
ng
> my way to that. The maintenance plan makes a full back at certain time.
>
Do yourself a favor and stop using maintenance plans. Learn what the
various options in a maintenance plan do, understand why they are used,
and write your own routines. Had you done that with backups, instead of
clicking buttons in a wizard, you would have understood the transaction
log and not be staring at an 80GB log file now. Wizards have their
place, but database administration is not one of them.|||Thank you so much that solved my problem.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uQRyYrilGHA.4772@.TK2MSFTNGP04.phx.gbl...
> Marco Napoli wrote:
> If you don't need the ability to do point-in-time recovery, put the
> database in Simple recovery mode and the log will flush itself at the next
> checkpoint. With an 80GB log file, this might take a few minutes.
> I'm guessing you aren't concerned with point-in-time recovery, since
> you're not backing up the transaction log. If I'm wrong, then you need to
> do some reading about backups and the transaction log, because you're not
> taking care of your database right now.
> To solve your immediate problem, run BACKUP LOG WITH TRUNCATE_ONLY against
> your database.|||Thank you for the tip.
Marco
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23wbTOEjlGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Marco Napoli wrote:
> Do yourself a favor and stop using maintenance plans. Learn what the
> various options in a maintenance plan do, understand why they are used,
> and write your own routines. Had you done that with backups, instead of
> clicking buttons in a wizard, you would have understood the transaction
> log and not be staring at an 80GB log file now. Wizards have their place,
> but database administration is not one of them.
>