Another truncate transaction log question 1

[cbirknh]

Hi Everyone,
I am running SQL Server which is backed up nightly using a veritas backup exec full database backup.
One of my transaction logs has grown to 5GB and I was hoping to truncate it for now and also figure out a system for it to truncate itself in the future.
I have tried dbcc shrinkfile and shrinkdatabase but am having no luck because, "All logical log files are in use". What should I be doing differently to prevent the entire log from being active?

Thanks,
Chris

 

[tlbroadbent]

Make sure you run
BACKUP LOG databasename WITH TRUNCATE_ONLY
before running DBCC SHRINKFILE. Then perform a full database backup immediately.

You may need to lock users out of the database while you try to shrink the LOG. Have you seen faq183-1534 - "Shrinking Databases and Logs - SQL 7 and Higher?" Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[cbirknh]

Thank you very much, Terry, for your prompt and helpful reply. The Backup log with truncate_only then shrinkfile worked perfectly.
I did see the FAQ that you mentioned which was informative, but I am still having difficulty understanding the 'Active portion' of the transaction log. What am I doing wrong that the whole log is active?

Thanks Again,
Chris

 

[tlbroadbent]

Did the transaction log file shrink after you performed the backup with truncate_only? Are you still getting the same message now? After the full Veritas backup, how much of the 5GB log is shown as used? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[cbirknh]

Hi Terry,
The log file shrunk to 1MB after I performed the "backup with truncate_only"? I am not getting the same message ("All logical log files are in use&quot. Prior to my post on here, I tried the full Veritas back-up and the log back-up method, but neither effected the size of the log.

Chris

 

[eja2000]

what are the implications of using WITH TRUNCATE_ONLY ?
will i still be able to restore the db together with full and differential bkps if i run a log bkp with the WITH TRUNCATE_ONLY command every hour?

 

[SQLBill]

Please do not respond to eja2000 in this thread. Eja2000 has started a new thread with this question. Refer to THREAD183-867644.

Thank you,

-SQLBill

Preposting advice: FAQ481-4875 and FAQ222-2244 among others.