July 14, 2008 at 11:47 am
I have a production server where I really need no logging for the purpose of recovery. We currently have a nightly job that shrinks the log file, but I am wondering if there is a better way to do this. Is there a way to turn off logging globally or at the transaction level? It seems this would improve performance.
While my example is a production box, it seems that the solution to this would apply to development environments where extra logging overhead is not needed.
July 14, 2008 at 12:07 pm
Change the database mode to "Simple".
July 14, 2008 at 1:20 pm
Thanks Nathan, however, my database is already in the simple recovery mode. The simple mode does not eliminate or reduce logging (which is the goal in my case).
July 14, 2008 at 1:26 pm
There is no way to eliminate logging. SQL Server writes changes to the Log and then to the database from the log. Changing the recovery mode to simple will cause the log to truncate on checkpoints, thus keeping the transaction log from growing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 14, 2008 at 1:32 pm
Thanks Jack, that helps.
July 14, 2008 at 11:14 pm
If you are sure that you do not need log backups (that means you will have only restore capability till last full/differential backup if you implement this approach), use the below command to truncate the log.
USE
BACKUP LOG WITH TRUNCATE_ONLY
You can schedule this piece of code as a job and schedule it for frequent execution.
July 14, 2008 at 11:25 pm
take backup of T log with this option.
WITH TRUNCATE_ONLY
July 15, 2008 at 1:18 am
shahbaz.oradba (7/14/2008)
take backup of T log with this option.WITH TRUNCATE_ONLY
In simple recovery mode (which the OP's database is in), the log automatically truncates on checkpoint. Hence there is no need to specify a truncate of the log
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
July 15, 2008 at 5:33 am
BACKUP LOG WITH TRUNCATE_ONLY
--->Can some one please tell me what does the above statement do. I understand that the log files are backuped up
My questions are
1) where is this backup stored? (I mean BACKUP LOG)
2) what is truncate_only? (does it only truncate the log files?)
3) Why do we take a backup of the log and then truncate ?
4) Why cant we truncate the log directly ?
thanks
🙂
July 15, 2008 at 5:43 am
rinu philip (7/15/2008)
My questions are1) where is this backup stored? (I mean BACKUP LOG)
It's not.
2) what is truncate_only? (does it only truncate the log files?)
Discard the inactive portion of the log without backup it up
3) Why do we take a backup of the log and then truncate ?
You're not. You're just truncating it. No backup is created
4) Why cant we truncate the log directly ?
that's exactly what you're doing.
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
July 15, 2008 at 6:07 am
thanks Gail!! very helpful info!
July 16, 2008 at 12:33 am
In this case instead of trncating the logfile if i'll shrink the log file what will happen?
July 16, 2008 at 1:07 am
Abhijit (7/16/2008)
In this case instead of trncating the logfile if i'll shrink the log file what will happen?
Provided there's unused space within the log file, the size of the file on disk will reduce.
The shrink will not discard tran log records
Just for completeness, shrink is not recommended, especially if run regularly as the log file will probably grow again, the process of grow and shrink will consume resources unnecessarily and the repeated shrink/grow may well cause fragmentation at the file level.
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
July 16, 2008 at 1:13 am
Hi, I think you could use the DBCC SHRINKFILE , this shrinks the log file by removing as many virtual log files, that the files that are not in use. After shrinking the log then a trucate will get more space.:)
July 16, 2008 at 1:19 am
rinu philip (7/16/2008)
After shrinking the log then a trucate will get more space.:)
Explain what you mean there please?
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply