How to reduce the log file ?

Hi,
I found that there is a setting to set the maximum log file, however,
I haven't enable it. Therefore, current log file is 1G. How can I
reduce it to 500MB so that I can set the max. log file to 500MB.
Happy new year to all
Ivan
GinolaIn the future you might post something like this to the
public.SQLserver.server newsgroups. To reduce the log you have two options,
(1) if you do not require point in time recovery you can issue the command
"backup log dbname with truncate_only" Then issue a dbcc shrinkfile command
to shrink the log (see BOL for full syntax). Also, if you do not require
point in time recovery you might set your recovery mode to simple to avoid
the log from growing.
If you do require point in time recovery, setup regular log backups and
don't shrink your logfile unless you have to for space reasons. Having empty
space in the log file is better than having SQL Server reallocate more disk
space to the log during the production day.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Ginola" <ginola@.fake.email.com> wrote in message
news:3ff3933f.24975482@.msnews.microsoft.com...

quote:

---

> Hi,
> I found that there is a setting to set the maximum log file, however,
> I haven't enable it. Therefore, current log file is 1G. How can I
> reduce it to 500MB so that I can set the max. log file to 500MB.
> Happy new year to all
> Ivan
> Ginola

---

|||To shrink the transaction log, you merely need to back it up and execute
dbcc shrinkfile.
If you don't need the backup, do
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
To shrink as far as possible, set target_size to 0.
For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
(<database_name> )
Whatever you do, don't delete the transaction log. You were lucky that
there were no transactions running or recovering at the time, otherwise, you
would have wound up with a suspect database.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Ginola" <ginola@.fake.email.com> wrote in message
news:3ff3933f.24975482@.msnews.microsoft.com...

quote:

---

> Hi,
> I found that there is a setting to set the maximum log file, however,
> I haven't enable it. Therefore, current log file is 1G. How can I
> reduce it to 500MB so that I can set the max. log file to 500MB.
> Happy new year to all
> Ivan
> Ginola

---

|||thanks for replying
On Fri, 2 Jan 2004 09:58:56 -0500, "Andy Svendsen"
<andymcdba1@.NOMORESPAM.yahoo.com> wrote:

quote:

---

>To shrink the transaction log, you merely need to back it up and execute
>dbcc shrinkfile.
>If you don't need the backup, do
>backup tran <database_name> with no_log
>go
>dbcc shrinkfile (<database_name>,target size)
>To shrink as far as possible, set target_size to 0.
>For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
>(<database_name> )
>Whatever you do, don't delete the transaction log. You were lucky that
>there were no transactions running or recovering at the time, otherwise, yo
u
>would have wound up with a suspect database.

---

Ginola|||I am a beginner in SQL server and I am using SQL server 2000
where should I type
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
To shrink the transaction log.
I know how to shrink the database ( because it has the option to
option when right click, but not the log file )
On Fri, 2 Jan 2004 09:58:56 -0500, "Andy Svendsen"
<andymcdba1@.NOMORESPAM.yahoo.com> wrote:

quote:

---

>To shrink the transaction log, you merely need to back it up and execute
>dbcc shrinkfile.
>If you don't need the backup, do
>backup tran <database_name> with no_log
>go
>dbcc shrinkfile (<database_name>,target size)
>To shrink as far as possible, set target_size to 0.
>For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
>(<database_name> )
>Whatever you do, don't delete the transaction log. You were lucky that
>there were no transactions running or recovering at the time, otherwise, yo
u
>would have wound up with a suspect database.

---

Ginola|||Go to start/programs - microsoft SQL Server - Query Analyzer. You will
need to make a connection to the SQL Server you need to issue the
commands on.
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||thanks, got it
On Sun, 04 Jan 2004 18:10:24 -0800, Ray Higdon
<rayhigdon@.higdonconsulting.com> wrote:

quote:

---

>Go to start/programs - microsoft SQL Server - Query Analyzer. You will
>need to make a connection to the SQL Server you need to issue the
>commands on.
>HTH
>Ray Higdon MCSE, MCDBA, CCNA
>*** Sent via Developersdex http://www.examnotes.net ***
>Don't just participate in USENET...get rewarded for it!

---

Ginola|||Hi,
I have tried
backup tran <database_name> with no_log
go
dbcc shrinkfile (<database_name>,target size)
but the error message display
"Could not locate file 'sqlBackup' in sysfiles.
DBCC execution completeted. If DBCC printed error message, contact
your system administrator"
sqlBackup is the database name,
When I delete the line "dbcc shrinkfile ... " it works fine. However,
when I apply the third line, the error message display.
Any clue how to solve the problem.
thanks

quote:

---

>To shrink the transaction log, you merely need to back it up and execute
>dbcc shrinkfile.
>If you don't need the backup, do
>backup tran <database_name> with no_log
>go
>dbcc shrinkfile (<database_name>,target size)
>To shrink as far as possible, set target_size to 0.
>For SQL 7, it is dump tran instead of backup tran and dbcc shrinkfile
>(<database_name> )
>Whatever you do, don't delete the transaction log. You were lucky that
>there were no transactions running or recovering at the time, otherwise, yo
u
>would have wound up with a suspect database.

---

Ginola|||You need to specify the actual name of the log file for that operation
once you are in the database, try this:
backup log sqlbackup with no_log
go
use sqlbackup
go
dbcc shrinkfile (logical name of your log file,0)
You can find the logical name by right-clicking your database in EM and
clicking the log tab and using the logical name (not the physical MDF
name)
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!