Re: optimum log file size

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 06/25/04 

Date: Fri, 25 Jun 2004 22:32:05 +0530

Hi,

Fixing of Transaction log file based up on the amount of bulk transaction
you have. CUrrent log growth will be because of FULL recovery mode. The only
solution for this is Backup the transaction log in intervals (1 hour once
minimum). If you do not require a point in time recovery or if your database
is development/test then set the recovery as SIMPLE.

COmmand to set SIMPLE recovery:

ALTER database <dbname> set recovery simple

-- How to reduce size.

How to clear the existing logs and shrink the file:-

Since the file is really huge, I recomment you to set the database to single
user model before doing the below steps.

-- Setting database single user.

Alter database <dbname> set single_user with rollback immediate

-- Truncate the transaction log

BACKUP log <dbname> with truncate_only

-- shrink the transacton log file

DBCC SHRINKFILE('logical_ldf_name',truncateonly)

After doing the above steps execute the below command to see the transaction
log size and usage.

DBCC SQLPERF(LOGSPACE)

-- Now set the database multi user

Alter database <dbname> set multi_user 

--
Thanks
Hari
MCDBA
"reddy" <reddy@discussions.microsoft.com> wrote in message
news:B60DE98A-F466-4B52-B5F7-BC7876C66E64@microsoft.com...
> hi friends,
>
> my log file is growing continuouly from mb's to gb.how can i avoid
this.what should be the optimum log file size.
> if my datafile is 500mb what should be the optimum log file size.
> is there any recomendations from microsoft regarding this logsize.
>
> please help
>
> thanks
> reddy

Relevant Pages

  • Re: SQL Database Purge
    ... Are you performing log backups regularly? ... set the database to simple recovery mode. ... > recovery model is set to FULL and the log file is nearly 66 GB. ...
    (microsoft.public.sqlserver.server)
  • RE: Shrink File?
    ... As my understanding of you problem, you have a database with a data file ... in the log file. ... You can truncate only nonactive portion of the transaction log. ...
    (microsoft.public.sqlserver.setup)
  • Re: Restoring from non-truncated transaction log
    ... Hi - I suggest you read the Books Online topics on recovery models ... > NOT been backing up the transaction log, so I would assume the transaction ... > transaction log when you restore a database but not the transaction log? ...
    (microsoft.public.sqlserver.server)
  • Re: Full v. simple recovery mode
    ... As you saif if you backup the transaction log in frequent interval then you ... recommend you to go for FULL recovery model. ... FOr SQL 2000 for this database should not have ... db not being in single user recovery mode. ...
    (microsoft.public.sqlserver.setup)
  • Re: Log Shipping: question about secondary dB mode?
    ... Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. ... And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. ... all the backup and restore jobs are proceeding without errors. ...
    (microsoft.public.sqlserver.tools)
Loading