Re:logfiles grow non-stop in SQL2000
- From: "Christos Kritikos" <ChristosKritikos@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 31 Mar 2005 09:47:03 -0800
Hi Hari,
this is fantastic advice. Thanks!
this is a production database. I am planning of setting up Log Shipping
(some time in the future) so the DB has to stay in the FULL or BULK_LOGGED
mode. Until then I tried setting up logfile backups (did it through a
maintenance plan) but their size wouldn't shrink. On the contrary all I
managed to do it to have a huge logfile and huge logfile backup!!! Let's say
that I want to stay in FULL/BULK_LOGGED mode, make logfile backups
periodically (once or twice a day would be enough) and shrink the logfile
after the backup. Do I have to run a special command to do this? Can I
schedule it to happen automatically?
million thanks!
christos
"Hari Prasad" wrote:
>
> Hi Chris,
>
> Looks like the recovery model for your database is FULL or BULK_LOGGED.Please change the recovery model for the database to
> SIMPLE if your database is non production. Use the below command to set the database to SIMPLE.
>
>
> ALTER database <DBNAME> set recovery SIMPLE
>
>
> If you do not have the 65 G to take a backup or if you do not want the transaction log backup. You could truncate the transaction
> log and shrink the LDF file.
>
>
> backup log <dbname> with truncate_only
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncatÂeonly)
>
>
> If you need the transaction log backup do:-
>
>
> backup log <dbname> to disk='d:\backup\dbname.trn'
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncatÂeonly)
>
>
> Now execute the below command to see log file size and usage.
>
>
> DBCC SQLPERF(LOGSPACE)
>
>
> Note:
>
>
> If you need to keep the recovery model to FULL then schedule a transaction log backup in frequent intervals (atleast 30 minutes once).
> This will keep the log file size under control.
>
> Thanks
> Hari
> SQL Server MVP
>
>
> ____________________________________
> Christos Kritikos Wrote:
>
> Hello,
>
> I am running SQL 2000 Enterprise (with all the updates) on a Win2000 Std
> Server. This DB is part of a webiste that includes users loging-in etc - so
> the DB is accessed by the webservers.
>
> The logfiles for the most frequently-used databases grow non-stop. If I
> don't do anything about it they will just grow until I run out of disk space!
> I tried to limit the maximum logfile size (through enterprise manager) but
> the result is that the DB becomes inaccessible when the logfile hits the
> limit. Is this normal? I am reading some documents that SQL sever is supposed
> to re-use the unnecessary part of the logfiles. How come this doesn't happen?
>
> I went through KB 317375 (Transaction Log Grows Unexpectedly or Becomes Full
> on SQL Server). It lists a number of reasons that may cause this problem. How
> can I figure out if some of these conditions apply in my case? Just by
> reading through them it seems as if they don't apply, but I may be mistaken.
>
> Million thanks for the help. I have been struggling with this for days!
>
> christos
>
>
>
>
>
>
>
>
> Sent via SreeSharp NewsReader http://www.SreeSharp.com
>
.
- Follow-Ups:
- Re: Re:logfiles grow non-stop in SQL2000
- From: Hari Prasad
- Re: Re:logfiles grow non-stop in SQL2000
- References:
- Re:logfiles grow non-stop in SQL2000
- From: Hari Prasad
- Re:logfiles grow non-stop in SQL2000
- Prev by Date: Re: query sql server hosted on adsl ?
- Next by Date: Wierd settings..
- Previous by thread: Re:logfiles grow non-stop in SQL2000
- Next by thread: Re: Re:logfiles grow non-stop in SQL2000
- Index(es):
Relevant Pages
|
