November 15, 2009 at 8:49 pm
Hi,
One of our databases log file growing very huge. One of our team members recommended the following instead of dbcc shrinkdatabase (of course i would recommend dbcc shrinkfile instead of dbcc shrinkdatabase)
exec SP_dboption @dbname,'trunc. log on chkpt.','true'
DBCC shrinkdatabase (@dbname)
But when I checked online I noticed the few points
1. If the trunc. log on chkpt. database option is enabled, the log is truncated when periodic checkpoints occur. Only full database and differential database backups are allowed because the log has been truncated and any log backups made would be unusable.
2.Important When using transaction log backups, do not set the trunc. log on chkpt. database option to TRUE. Setting this option to TRUE causes the transaction log to be truncated, without backing up the truncated part of the transaction log, every time a checkpoint occurs in the database, preventing more transaction log backups from being created.
Will it create any data loss (without backing up the truncated part of the transaction log every time a checkpoint occurs in the database)
3. trunc. log on chkpt.
When true, a checkpoint truncates the inactive part of the log when the database is in log truncate mode.
Important:
Starting with SQL Server 2000, setting the trunc. log on chkpt. option to true sets the recovery model of the database to SIMPLE. Setting the option to false sets the recovery model to FULL.
What is the best method to reduce the log size without data loss.
November 15, 2009 at 9:22 pm
Will it create any data loss (without backing up the truncated part of the transaction log every time a checkpoint occurs in the database)
It will not create a data loss (Since checkpoint writes the Data to the Data Files) but you will not have the ability to take Transaction Log Backups, resulting you cannot restore the Database to a Point-in-Time when performing Recovery.
What is the best method to reduce the log size without data loss.
The Best method would be to take regular Transaction Log Bkups so that the Log File can be reused.
This is from my understanding, I have not done this earlier.
Someone correct me, if I am wrong on this.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 16, 2009 at 1:17 am
newbie2sql (11/15/2009)
Hi,One of our databases log file growing very huge. One of our team members recommended the following instead of dbcc shrinkdatabase (of course i would recommend dbcc shrinkfile instead of dbcc shrinkdatabase)
exec SP_dboption @dbname,'trunc. log on chkpt.','true'
DBCC shrinkdatabase (@dbname)
Seriously bad advice on the part of your team member. The option 'trunc. log on chkpt' has been deprecated for a few versions of SQL already. It should not be used. The replacement is Simple Recovery Model.
If you've got a system where full backups are all you need and there's no problem with possibly losing several hours of data if say a drive fails, then you can go for simple recovery. If you need to be able to restore to the point of failure, then you need full recovery.
Shrinking the entire database is an extremely bad idea. Results in index fragmentation, results in slow performance when the files (data and log) have to regrow. Not something that you should be doing on a regular basis at all.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2009 at 8:10 am
Newbie, you REALLY need to get a professional to come in for a few days to give your group some training on how to effectively and PROPERLY manage a sql server infrastructure, because you are not doing either now. Your business could well be at significant risk!! Said consultant should also give your systems a review to get things configured to best practice standards.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply