Restrict the use of NO_LOG and Truncate_Only
We use these commands to claim back the disk space when the size of transaction log goes beyond what was expected and takes all the disk space. This happens only when the recovery model is set to either FULL or BULK-LOGGED.
After taking a full backup, if you don't take a transaction log backup, the log will continue to grow until it runs out of disk space.
The solution to the problem is to take a log backup but if you don’t want to take the log backup Microsoft has given you an option to take BACKUP LOG WITH NO_LOG command.
Why No_Log\ Truncate_Only should be avoided?
By issuing this command we are actually discarding the contents of the transaction log since the last full/differential/log backup. That means that until the next full or differential backup completes, all work since the last backup completed will be lost if the database is destroyed. So if your business relies on not losing data/work in the event of a disaster - then this option is a big NO for you.
The real point of FULL or BULK_LOGGED recovery model is to keep the transaction log so that the database can be recovered right up to the point of a failure, or to do point-in-time recovery. Using BACKUP LOG WITH NO_LOG negates all of that by breaking the LSN Chain.
Now the question comes How to avoid it.
What’s the Alternative?
If you want the FULL recovery mode behavior, but don't want to run out of log space - then the answer is again simple, take log backups!
If your log is growing too quickly, take more frequent log backups!
How to Restrict the use of NO_LOG and Truncate_Only
If you're a system administrator and you want to stop your database owners and backup operators from using the NO_LOG option, there is way to do it.
You can use Trace flag 3231 in Sql Server 2000 and Sql Server 2005, which will turn the NO_LOG and TRUNCATE_ONLY options into no-ops in FULL/BULK_LOGGED recovery models. Trace flag 3031 in Sql Server 2005 turns them in checkpoints in all recovery models. These trace flags are undocumented but they're allowed to be publicized.
This way no-one can truncate the log unless you allow them to.
Related Article: Auto-Truncate Log in Full Recovery Model
Thanks & Regards,
Sarabpreet Singh
Sarabpreet is SQLServer MVP, DBA, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 8+ years of Experience and worked with Industry Leaders like Wipro, HP and HCL. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”. To know about his speaking engagements visit: here...
Follow Sarab on @Sarab_SQLGeek , ,
Sarab,
Not sure why did Microsoft discard this feature from SQL 2008 and wont be supported in any of the future versions.
I don't think there is any use of such a feature. Moreover, I've seen DBA's unknowingly using this statement resulting in Breaked LSN Chain.
So if we've other ways to achieve the same thing, why would microsoft give such a feature which is confusing.
Thanks
Leave a comment