Log files

Is there any way to turn off recovery and/or eliminate the growth of the log
file? I've heard there's a system stored procedure which will do this, but
I am unable to find any info either in the help or googling the net.
Thanks,
JimOn Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
> Is there any way to turn off recovery and/or eliminate the growth of the log
> file? I've heard there's a system stored procedure which will do this, but
> I am unable to find any info either in the help or googling the net.
> Thanks,
> Jim
There isn't such stored procedure but it sounds like you are not
using log backups, but your database is not configured as simple
recovery mode. If this is correct, you can modify the database so it
will be in simple recovery mode using this statement:
alter database WriteRealDBNameHere set recovery simple
This will prevent the log from storing the data modifications
operations after they are done, but you still have to shrink the file
if it is too big. Notice that when you set the database to simple
recovery then you can restore it only to the last full or differential
backups that you have and in case of a catastrophic error, you will
not be able to do restore to a point of time.
Adi|||We are using simple recovery. Backup and recoveries are not needed for our
purposes - basically we do complex queries against fixed data sets (no
transactions) but do create tables of intermediate results, and the log
files (even in Simple recovery) get quite large. Surprised to learn that
logging cannot be turned off.
Thanks.
"Adi" <adicohn@.hotmail.com> wrote in message
news:1190132146.223565.52320@.57g2000hsv.googlegroups.com...
> On Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
>> Is there any way to turn off recovery and/or eliminate the growth of the
>> log
>> file? I've heard there's a system stored procedure which will do this,
>> but
>> I am unable to find any info either in the help or googling the net.
>> Thanks,
>> Jim
> There isn't such stored procedure but it sounds like you are not
> using log backups, but your database is not configured as simple
> recovery mode. If this is correct, you can modify the database so it
> will be in simple recovery mode using this statement:
> alter database WriteRealDBNameHere set recovery simple
> This will prevent the log from storing the data modifications
> operations after they are done, but you still have to shrink the file
> if it is too big. Notice that when you set the database to simple
> recovery then you can restore it only to the last full or differential
> backups that you have and in case of a catastrophic error, you will
> not be able to do restore to a point of time.
> Adi
>|||Hello Jim!
I believe that the following documentation will enlighten you about the
growing of Transaction Log.
http://support.microsoft.com/kb/873235/en-us
Ekrem Önsoy
"Jim Fox" <jim.fox@.emailhdi.com> wrote in message
news:e%23sjIRh%23HHA.464@.TK2MSFTNGP02.phx.gbl...
> We are using simple recovery. Backup and recoveries are not needed for
> our purposes - basically we do complex queries against fixed data sets (no
> transactions) but do create tables of intermediate results, and the log
> files (even in Simple recovery) get quite large. Surprised to learn that
> logging cannot be turned off.
> Thanks.
>
> "Adi" <adicohn@.hotmail.com> wrote in message
> news:1190132146.223565.52320@.57g2000hsv.googlegroups.com...
>> On Sep 18, 5:52 pm, "Jim Fox" <jim...@.emailhdi.com> wrote:
>> Is there any way to turn off recovery and/or eliminate the growth of the
>> log
>> file? I've heard there's a system stored procedure which will do this,
>> but
>> I am unable to find any info either in the help or googling the net.
>> Thanks,
>> Jim
>> There isn't such stored procedure but it sounds like you are not
>> using log backups, but your database is not configured as simple
>> recovery mode. If this is correct, you can modify the database so it
>> will be in simple recovery mode using this statement:
>> alter database WriteRealDBNameHere set recovery simple
>> This will prevent the log from storing the data modifications
>> operations after they are done, but you still have to shrink the file
>> if it is too big. Notice that when you set the database to simple
>> recovery then you can restore it only to the last full or differential
>> backups that you have and in case of a catastrophic error, you will
>> not be able to do restore to a point of time.
>> Adi
>|||In article <e#sjIRh#HHA.464@.TK2MSFTNGP02.phx.gbl>, jim.fox@.emailhdi.com
says...
> Surprised to learn that
> logging cannot be turned off.
>
I think you are confused by the use of the word "logging." Not like IIS
logging, or event logs, or installation logs which are all
informational. The LDF file is an inherent part of the database, such
that at any time, "database" consists of stuff already rolled into the
MDF/NDF files and stuff that is still in the "LDF" file. One cannot
exist without the other and you sure do *not* want to have the ability
to turn off this write-ahead logging. If you could you would break the
database and render it completely unusable. It would cause the A-C-I-D
properties to be broken. Without Atomic-Consistent-Isolated-Durable
transactions you end up with things like phantom records, lost data,
etc.
Suggest you read about transactions and transaction isolation in BOL
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||You can do this in the foolowing steps:
1. update master..sysdatabases set status = 32768 where name = 'yourdbname'
2. restart the server
Now your database in so called "emergency mode". One of properties of this
mode is that database operations are not logged.
Of course, it is that you want, but on my opinion it is not clever to do
such a things.
"Jim Fox" wrote:
> Is there any way to turn off recovery and/or eliminate the growth of the log
> file? I've heard there's a system stored procedure which will do this, but
> I am unable to find any info either in the help or googling the net.
> Thanks,
> Jim
>
>|||On Sep 19, 1:28 pm, Dmitrij Siemieniako
<DmitrijSiemieni...@.discussions.microsoft.com> wrote:
> You can do this in the foolowing steps:
> 1. update master..sysdatabases set status = 32768 where name = 'yourdbname'
> 2. restart the server
> Now your database in so called "emergency mode". One of properties of this
> mode is that database operations are not logged.
> Of course, it is that you want, but on my opinion it is not clever to do
> such a things.
>
> "Jim Fox" wrote:
> > Is there any way to turn off recovery and/or eliminate the growth of the log
> > file? I've heard there's a system stored procedure which will do this, but
> > I am unable to find any info either in the help or googling the net.
> > Thanks,
> > Jim- Hide quoted text -
> - Show quoted text -
Emergency mode should be used (just as the name implies) on emergency
cases only. It should be used on cases such as there is no log file
or cases with this severity. It definitely shouldn't be used on
regular basis for no reason. On this case it also wouldn't help.
When you set a database to emergency mode, you can't start any
transaction and you can not perform any data modification operation
within the database. Since Jim wrote that he inserts data into tables
and then base the reports on those tables, his procedure will not work
on a database that is set to emergency mode.
One last thing - If I remember correctly, setting a database to
emergency mode requires you to set the status to -32768 (I think that
the documentation specifies 32768, and that it is wrong. I admit that
I'm not sure about it).
Adi