Re: suitable plan for Log backup

From: Aryan (Aryan_at_discussions.microsoft.com)
Date: 07/13/04 

Date: Tue, 13 Jul 2004 06:57:04 -0700

Hi,
Thanks for your guidance.
After the transaction log backup, I have tried to perform the shrinkfile command but I got the error message as below:
"Cannot shrink log file 2 (IstgDocument_Log) because all logical log files are in use."
After the first backup,the size of log backup is about 500k.Also the
DBCC SQLPERF(LOGSPACE) shows only 3.5% of log space is used.
Does it mean, only about 500k of logs are active?
How can I reduce the log file size in this situation?

Thanks
Aryan

"Hari Prasad" wrote:

> Hi,
>
>
> The physical logfile size (LDF file) will not reduce after a Transaction log
> backup. The transaction log backup will perform the backup of the database
> since the last log was backed up. After the backup the inactive part of
> transaction log will be removed from log. But the physical file size will
> remain the same. How to the
> log size and usage.
>
> DBCC SQLPERF(LOGSPACE)
>
> To reduce the LDF file physical size, after the transaction log backup you
> have to perform the shrinkfile command.
>
> DBCC SHRINKFILE('Logical_ldf_file',truncateonly)
>
> Logical ldf file can be identified by executing the below command
>
> use <dbname>
> go
> sp_helpfile
>
> Logical name is the column with "NAME".
>
> --
> Thanks
> Hari
> MCDBA
>
>
> "Aryan" <Aryan@discussions.microsoft.com> wrote in message
> news:FF3EF101-93B0-4B64-852C-4C924501405B@microsoft.com...
> > Hi,
> >
> > I have a 2GB database and the size of log file is approximately 1.5GB. For
> backup, I have defined a maintenance plan for DB & Log files. Unfortunately,
> after backup the size of log file does not decrease. It is said that after
> log file backup, SQL server eliminate the inactive logs automatically.
> > What is the best plan for Backup in SQL server? I mean, a plan for backup
> of DB/Log files and also we can have a log file in a suitable size.
> >
> > Thanks in advance
> >
> > Aryan
> >
>
>
>

Relevant Pages

  • 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)
  • Re: SQL 2005 Shrink issue
    ... When you perform a Transaction Log Backup, passive virtual logs in your transaction log file are deleted. ... you can change your database's recovery model to SIMPLE when you want to get rid of your passive virtual logs in your transaction log file. ...
    (microsoft.public.sqlserver.tools)
  • Re: Restore Database w/Large Transaction Log File
    ... If I am reading you correctly, the backup has already been trasmitted to the ... AGAIN, just like the other respondents have told you: shrink the log file, ... Shrinking a database transaction log file that has that has been pre-gone to 200 GB in size, will not save any disk space on the restore. ...
    (microsoft.public.sqlserver.server)
  • Re: suitable plan for Log backup
    ... After tha transaction log backup, I tried to perform the shrinkfile command but I got the error message as below: ... "Cannot shrink log file 2 because all logical log files are in use" ...
    (microsoft.public.sqlserver.server)
  • Re: suitable plan for Log backup
    ... After the transaction log backup, I have tried to perform the shrinkfile command but I got the error message as below: ... How can I reduce the log file size in this situation? ...
    (microsoft.public.sqlserver.server)