Deleting a Log file and recreating it...

I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why this
log file grew so much; originally the DB was set to SIMPLE so Trans logs
weren't being backed up. This DB is in a Dev environment, so I think someone
changed it to full, and the Trans logs never got backed up. In any case, is
it okay to delete a log file? If so, how do you go about recreating a new
one? Through the properties of the database in Enterprise Mgr? I am told
that you really only need log files for say a midday backup or something; we
do full backups of the dbs daily. Anyway, if anyone can let me know if what
I want to do is okay or not, I would appreciate it.
I'm pretty new to SQL and don't want to start doing things that really are
not "correct" procedures. I did try backing up the Trans Log, but it didn't
budge the size of it at all.
Saral6978 wrote:
> I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why this
> log file grew so much; originally the DB was set to SIMPLE so Trans logs
> weren't being backed up. This DB is in a Dev environment, so I think someone
> changed it to full, and the Trans logs never got backed up. In any case, is
> it okay to delete a log file? If so, how do you go about recreating a new
> one? Through the properties of the database in Enterprise Mgr? I am told
> that you really only need log files for say a midday backup or something; we
> do full backups of the dbs daily. Anyway, if anyone can let me know if what
> I want to do is okay or not, I would appreciate it.
> I'm pretty new to SQL and don't want to start doing things that really are
> not "correct" procedures. I did try backing up the Trans Log, but it didn't
> budge the size of it at all.
I don't want to re-type the whole explanation again, so I'll just refer
you to this thread:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/ba55a63c258ae646/a3338eddc9fc8b46
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||So, the answer in whether I can just delete the log file and recreate it then
is simply no? I did back up the transaction log, then I backed up the DB,
but the log still didn't go down in size. I do not have the disk space to
keep the log this big - I'm down to 9MB of space on the paritition that
stores the log.
I guess I can try to figure out how to truncate it, then shrink it, then
back everything up.
"Tracy McKibben" wrote:

> Saral6978 wrote:
> I don't want to re-type the whole explanation again, so I'll just refer
> you to this thread:
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/ba55a63c258ae646/a3338eddc9fc8b46
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||Saral6978 wrote:
> So, the answer in whether I can just delete the log file and recreate it then
> is simply no? I did back up the transaction log, then I backed up the DB,
> but the log still didn't go down in size. I do not have the disk space to
> keep the log this big - I'm down to 9MB of space on the paritition that
> stores the log.
> I guess I can try to figure out how to truncate it, then shrink it, then
> back everything up.
>
Backing up the log simply truncates it, and as I stated clearly in the
referenced thread, TRUNCATING IS NOT SHRINKING. Now that you've
truncated the log, you can use DBCC SHRINKFILE to shrink it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi,
you cant delete a log file. Seems that your log has to be shrinked
not completly deleted.
http://support.microsoft.com/kb/272318
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the MS KB article...this is a little easier to understand how I'm
supposed to do this.
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||I got shrunk down to approx 1GB. Thanks, again!
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Deleting a Log file and recreating it...

I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why thi
s
log file grew so much; originally the DB was set to SIMPLE so Trans logs
weren't being backed up. This DB is in a Dev environment, so I think someon
e
changed it to full, and the Trans logs never got backed up. In any case, is
it okay to delete a log file? If so, how do you go about recreating a new
one? Through the properties of the database in Enterprise Mgr? I am told
that you really only need log files for say a midday backup or something; we
do full backups of the dbs daily. Anyway, if anyone can let me know if what
I want to do is okay or not, I would appreciate it.
I'm pretty new to SQL and don't want to start doing things that really are
not "correct" procedures. I did try backing up the Trans Log, but it didn't
budge the size of it at all.Saral6978 wrote:
> I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why t
his
> log file grew so much; originally the DB was set to SIMPLE so Trans logs
> weren't being backed up. This DB is in a Dev environment, so I think some
one
> changed it to full, and the Trans logs never got backed up. In any case,
is
> it okay to delete a log file? If so, how do you go about recreating a new
> one? Through the properties of the database in Enterprise Mgr? I am told
> that you really only need log files for say a midday backup or something;
we
> do full backups of the dbs daily. Anyway, if anyone can let me know if wh
at
> I want to do is okay or not, I would appreciate it.
> I'm pretty new to SQL and don't want to start doing things that really are
> not "correct" procedures. I did try backing up the Trans Log, but it didn
't
> budge the size of it at all.
I don't want to re-type the whole explanation again, so I'll just refer
you to this thread:
http://groups.google.com/group/micr...3338eddc9fc8b46
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||So, the answer in whether I can just delete the log file and recreate it the
n
is simply no? I did back up the transaction log, then I backed up the DB,
but the log still didn't go down in size. I do not have the disk space to
keep the log this big - I'm down to 9MB of space on the paritition that
stores the log.
I guess I can try to figure out how to truncate it, then shrink it, then
back everything up.
"Tracy McKibben" wrote:

> Saral6978 wrote:
> I don't want to re-type the whole explanation again, so I'll just refer
> you to this thread:
> http://groups.google.com/group/micr...3338eddc9fc8b46
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> So, the answer in whether I can just delete the log file and recreate it t
hen
> is simply no? I did back up the transaction log, then I backed up the DB,
> but the log still didn't go down in size. I do not have the disk space to
> keep the log this big - I'm down to 9MB of space on the paritition that
> stores the log.
> I guess I can try to figure out how to truncate it, then shrink it, then
> back everything up.
>
Backing up the log simply truncates it, and as I stated clearly in the
referenced thread, TRUNCATING IS NOT SHRINKING. Now that you've
truncated the log, you can use DBCC SHRINKFILE to shrink it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
you can=B4t delete a log file. Seems that your log has to be shrinked
not completly deleted.
http://support.microsoft.com/kb/272318
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks for the MS KB article...this is a little easier to understand how I'm
supposed to do this.
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||I got shrunk down to approx 1GB. Thanks, again!
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>