November 20, 2002 at 3:10 pm
I'm trying to figure out how to control my transaction log. I have to use Veritas to backup my databases - and Veritas doesn't have the same transaction log backup/truncation option that sql server would allow me to do.
Veritas says to turn trun.opt checkpoint on - but I can't figure out how to do that. Shrinkfile errors and says that the virtual log size cannot be shrunk.
Can anyone help me with this? I need to get that transaction log truncated so my database works again. Thanks!!
November 20, 2002 at 3:31 pm
What version of SQL server are you using and what are the errors being returned by DBCC SHRINKFILE() ?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 20, 2002 at 3:38 pm
I'm using Sql Server 2000
Cannot shrink log file 2 (ADPS_Log) because all logical log files are in use.
Thanks!
November 20, 2002 at 3:38 pm
Right click on database, select "Properties", look under the "Options"
tab and you will find "Truncation Log on Checkpiont" option. I think I might review what this means prior to checking it.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 20, 2002 at 3:44 pm
Is that option possibly only for version 7.0? I didn't see it as an option in 2000, but I could be wrong.
I created a database maintenance plan to backup the transaction log (starting tonight) - will that automatically truncate and shrink the log file? Or will I still need to do something manually tomorrow?
Thanks for all your help!!
November 20, 2002 at 3:48 pm
That path of options is only available for SQL Server 7.0. If you want to get the SQL 2K transaction log down, here's the KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Basically, you first execute a BACKUP LOG <database> WITH TRUNCATE_ONLY in order to prep it for the DBCC SHRINKFILE. Make sure you have a good backup of the DB just in case.
HTH.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 20, 2002 at 4:11 pm
Thank you very much - that was exactly what I was needing to know! It appears to have worked - at least my database is responding again.
Can you tell me what the basic recommended frequency is for that kind of maintenance? Or will my maintenance routine that I just set up keep that under control from now on?
Thanks again!
November 20, 2002 at 4:14 pm
For SQL 2000 you have to change your recovery options to "Simple" with is similar to the SQL 7.0 "Truncate log on checkpoint"
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 20, 2002 at 6:55 pm
If you need to keep backups of your transaction log (there are a lot of factors that determine this), then you can stay in full or bulk logged recovery mode. Full is the default. In that case, generally, yes, the transaction log shouldn't grow too big if you're carrying out backups of it. Transaction log backups should be setting checkpoints which will means it should wrap around. That was part of the reason for BACKUP LOG WITH TRUNCATE ONLY.
If you don't need to keep backups of your transaction log, then setting to Simple recovery mode (through the options tab) should work for you.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply