?
Solved

How to Disable Log File in SQL Server 2008

Posted on 2012-09-07
11
Medium Priority
?
721 Views
Last Modified: 2012-09-07
Hello experts,

I'm doing a massive data manipulation on my SQL database, and this is causing the log file to grow very rapidly beyond the available storage space. As a result, the data manipulation task stops in the middle because the server runs out of space and I'm unable to finish the task.

I can do:
DBCC SHRINKFILE ('myLogFile' , 0, TRUNCATEONLY)
and reduce the size of the Log down to 1,024 KB, but this is only possible after the task is over.

Is there any way I can disable logging the data transactions or perhaps force the log file to shrink during the operation to ensure that my massive data processing runs to the end without interruption.

Appreciate your help,
Hani
0
Comment
Question by:Mehawitchi
  • 6
  • 5
11 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 38375322
You can change the recovery model of the database to SIMPLE, this limits the the logging activity. But remember SIMPLE recovery model does not allows you to do log backup as a result you cannot do point in time recovery. if you are not bothered about point in time recovery you can use the recovery model SIMPLE

ALTER DATABASE DBName SET RECOVERY SIMPLE.

Another option without changing the recovery model is to schedule frequent log backups, say every 5 min, so the inactive portion of the log file will get truncated after the log backup.
0
 

Author Comment

by:Mehawitchi
ID: 38375421
Many thanks anujnb,

According to the database options, the recovery is already set to 'Simple'.

Will the frequent log backups run during a data processing task? If yes, then how to set frequent log backups?

Thanks again for your help.

Hani
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38375448
Okay, so the database is already in SIMPLE recovery? if so can you tell us what are the file settings for log file, like Max Size, Auto growth etc?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Mehawitchi
ID: 38375653
Please see attached
Log-File-Settings.jpg
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38375710
Thanks! do you have any open transactions? you can check this by using DBCC OPENTRAN
also, can you post the result of the following query


SELECT log_reuse_wait_desc FROM sys.databases
WHERE name = 'MyDB'
0
 

Author Comment

by:Mehawitchi
ID: 38375717
DBCC OPENTRAN
===> No active open transactions.
*******************************************

SELECT log_reuse_wait_desc FROM sys.databases
WHERE name = 'ctvTracker'
===>
log_reuse_wait_desc
NOTHING
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38375732
Interesting...

Is there any way I can disable logging the data transactions or perhaps force the log file to shrink during the operation to ensure that my massive data processing runs to the end without interruption.

No, there is no way to disable logging, becaus durability (one of the property in aciD) is achieved only by logging.

Usually, you should size the transaction log file such way that it should accommodation the largest transaction and its rollback and the index rebuild, all of them are logged operation.

Is it possible for you to do the large transaction in small batches, so once they are committed SQL Server automatically truncate the logs and makes it reusable if your db is in simple recovery.
0
 

Author Comment

by:Mehawitchi
ID: 38375747
I'm afraid I cannot do the large transactions in smaller batches, so I guess the only solution is to increase the disk space :-(
0
 
LVL 15

Accepted Solution

by:
Anuj earned 2000 total points
ID: 38375764
Can you try in the following settings, enable auto growth for log file, set max file size to unrestricted file growth.

also issue a manual check point (command is CHECKPOINT)before the big transaction and do a data file shrink.
0
 

Author Comment

by:Mehawitchi
ID: 38375849
Thanks for your help anujnb - I will try this and revert back with outcome later tonight, as I have to go offline now.
Best,
0
 

Author Closing Comment

by:Mehawitchi
ID: 38377419
Thanks Anujnb.

CHECKPOINT solved the problem. Greatly appreciated!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question