how to truncate a log file

hi friends,
i am truncating the log file with the command
backup log UserManager with truncate_only ,this is not truncating up to mark.
is there any way to truncate the logfile effectively.now my log file is going in GBs.
please help
thanks
reddy
Hi,
There will be still open transactioin inside the database, that is the
reason log is not getting truncated.
How to chek if there is any open trasnactions:-
DBCC OPENTRAN('db_name')
if there is active open transactions, then you may need to wait till the
trasactiuon ends. Otherwise start the database in single user:-
ALTER database <dbname> set single_user with rollback immediate
go
backup log <dbname> with truncate_only
go
Execute the below command to see the log size:
DBCC SQLPERF(LOGSPACE)
You can shrink the physical LDF file usinge the below command:-
DBCCSHRINKFILE('logical_ldf_name',truncateonly)
Execute DBCC SQLPERF(LOGSPACE) again to get the file size and usage.
Thanks
Hari
MCDBA
"reddy" <reddy@.discussions.microsoft.com> wrote in message
news:C7018680-A788-481B-AABF-8BD677461A90@.microsoft.com...
> hi friends,
> i am truncating the log file with the command
> backup log UserManager with truncate_only ,this is not truncating up to
mark.
> is there any way to truncate the logfile effectively.now my log file is
going in GBs.
> please help
> thanks
> reddy
>
>
|||hari,
thank you for ur help
i have one doubt hari,if i execute the following command in QA where it will backup the log,is there any default path for it.what should be the file name.
backup log <dbname> with truncate_only
if i use the DBCCSHRINKFILE('logical_ldf_name',truncateonly) command my log file suddenly shrinked from 2GB to 1MB.because of this any details i lost.if i lost how can i recover.
please explain.with out testing on dummy database i applied the commands on production server.is there any data loss with this.
thanks®ards
reddy
|||Hi Reddy,
backup log <dbname> with truncate_only :- This command will not backup the
transaction log. This will remove the
inactive part of the transaction log with out backup. So if you need to
backup the trasnqaction log use the below command:-
backup log <dbname> to disk='c:\backup\dbname.trn'
This will backup the transaction log to c:\backup folder.
DBCCSHRINKFILE('logical_ldf_name',truncateonly) command my log file suddenly
shrinked from 2GB to 1MB.because of this any details i lost.if i lost how
can i recover.?
No , this command will shrink the physical trasnaction log file. Say you
had 2 GB transaction log file, but out of that only 1 MB was used. AFter
issuing the command the entry was shunk.
Sure, Absolutely no data loss will happen. Only thing is insted of backup
log <dbname> with truncate_only , use the
backup log <dbname> to disk='c:\backup\dbname.trn' to backup the trasnaction
log in frequent intervals.
Thanks
Hari
MCDBA
"reddy" <reddy@.discussions.microsoft.com> wrote in message
news:DFA68B92-4FF1-4570-8786-BA5EC1C08C6F@.microsoft.com...
> hari,
> thank you for ur help
> i have one doubt hari,if i execute the following command in QA where it
will backup the log,is there any default path for it.what should be the file
name.
> backup log <dbname> with truncate_only
> if i use the DBCCSHRINKFILE('logical_ldf_name',truncateonly) command my
log file suddenly shrinked from 2GB to 1MB.because of this any details i
lost.if i lost how can i recover.
> please explain.with out testing on dummy database i applied the commands
on production server.is there any data loss with this.
> thanks®ards
> reddy
>
>
|||hari,
thank you very much for ur help.
thanks
reddy
"Hari" wrote:

> Hi Reddy,
> backup log <dbname> with truncate_only :- This command will not backup the
> transaction log. This will remove the
> inactive part of the transaction log with out backup. So if you need to
> backup the trasnqaction log use the below command:-
> backup log <dbname> to disk='c:\backup\dbname.trn'
> This will backup the transaction log to c:\backup folder.
> DBCCSHRINKFILE('logical_ldf_name',truncateonly) command my log file suddenly
> shrinked from 2GB to 1MB.because of this any details i lost.if i lost how
> can i recover.?
> No , this command will shrink the physical trasnaction log file. Say you
> had 2 GB transaction log file, but out of that only 1 MB was used. AFter
> issuing the command the entry was shunk.
> Sure, Absolutely no data loss will happen. Only thing is insted of backup
> log <dbname> with truncate_only , use the
> backup log <dbname> to disk='c:\backup\dbname.trn' to backup the trasnaction
> log in frequent intervals.
> --
> Thanks
> Hari
> MCDBA
> "reddy" <reddy@.discussions.microsoft.com> wrote in message
> news:DFA68B92-4FF1-4570-8786-BA5EC1C08C6F@.microsoft.com...
> will backup the log,is there any default path for it.what should be the file
> name.
> log file suddenly shrinked from 2GB to 1MB.because of this any details i
> lost.if i lost how can i recover.
> on production server.is there any data loss with this.
>
>