Re:logfiles grow non-stop in SQL2000


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
.

Relevant Pages

  • RE: What is the best practice to create Maintenance Plans
    ... > If master is damaged in some way, for example because of media failure, an ... > With the Simple Recovery model, the database can be recovered to the point ... > Full Recovery or Bulk-Logged Recovery model. ... > Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • What is the best practice to create Maintenance Plans
    ... There is not a definitive answer to the question of which backup is better. ... the database can be recovered to the point ... Full Recovery or Bulk-Logged Recovery model. ... Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • Re: transaction log
    ... It seems your Recovery model set to that database is "FULL". ... So we need to scdule a transaction log backup regularly (atleast hourly ...
    (microsoft.public.sqlserver.server)
  • Re: Question about sql job, SQL 7 versus upgrade to SQL 2000
    ... Sounds like recovery model for 'sfa' database is set to "Simple". ... case transaction log backup is not possible. ...
    (microsoft.public.sqlserver.server)
  • Re: Question about sql job, SQL 7 versus upgrade to SQL 2000
    ... Sounds like recovery model for 'sfa' database is set to "Simple". ... case transaction log backup is not possible. ...
    (microsoft.public.sqlserver.tools)