Is there anyway to turn off logging completely?

[mm8294]

Our Transaction log file is growing very fast (2G per 5minutes) and fill up the disk. I have already set the database to be in simple recovery mode. Also I have schedule a job to run 'backup log ... with truncate_only' and 'dbcc shrinffile (...)' every 5 minutes, but it does not work because the transaction is still in progress. I talked to my friend, he gave me two suggestions:

1. Set max size for the transaction file (now it is set to unlimited), but I am not sure if this works or not. Does anybody know it?

2. He said there is a stored procedure to turn off logging completely, but he forget what that is and he said 99% of DBAs won't do it.


Any suggestions?


Thanks in advance.

 

[JayKusch]

Put the database in Simple Recovery Mode

Thanks

J. Kusch

 

[mm8294]

JayKusch, I have already put the database in Simple Recovery mode. But this does not work. It is still growing fast.


By the way, with database in Simple mode and max transaction log file size set to 1G, our client tried again, still got the same error.


We have been trying all day, this is killing me.

 

[JayKusch]

I am at a loss here. Are you SURE you put the DB in Simple mode.

Just to humor me ... open Enterprise Manager, get to the Database we are discussing and right-click the database.

Choose Properties. Once open choose Options.

In there the MODEL should equal SIMPLE.

Drop me a quick response on what you find.

Thanks

J. Kusch

 

[bikerboy718]

Also if you transaction log is growing to 1 or 2 gigs every 5 minutes I suggest running a virus scan on your server. There may be a sql virus on your machine.

With Great Power Comes Great Responsibility!!!

Michael

 

[JayKusch]

I do not know if I would advise running a virus scan (if we are talking about a McAfee or Norton app). It can really wreak havoc w/ the performance of a SQL Server server. Especially when it sounds like a clients production server.

A virus would not cause a TLog to go rampant. There is another reason for the log growth we have yet to uncover.

Thanks

J. Kusch

 

[mm8294]

I am sure it is in Simple Recover mode. And I don't think we need worry about virus.

Actually, before the transaction started, the user had already warned us, because they are going to insert millions of records.

About simple recovery mode, as far as I know, it will make the transaction log file grow slowly, not stop growing ( I googled and found several articles about it today ). In our case, since millions of records will be inserted, the transaction log file still grow very fast even in Simple Recovery mode.

 

[JayKusch]

I would then suggest, if possible, to batch the insert in and do a BACKUP Log MyData WITH NO_TRUNCATE after each batch.

Seeing that you are using Simple mode implies that you are not needing to log the transactions so this method should work.

Thanks

J. Kusch

 

[mm8294]

JayKush, thank you very much for your advice. I will tell the clients to do that. It seems to be the only choice now.

 

[SQLBI]

To my knowledge, Simple Recovery Model still writes to the log, however instead of retaining log data to enable point in time restore (Full Recovery) when a checkpoint is fired, SQL writes all commited transactions to disc and truncates the log.

So in your case, i am assuuming that the multi-million record insert is being done as a single transaction, thus the time between BEGIN TRAN and COMMIT TRAN is longer than the time between checkpoints.

As JayKusch says, make the insert as a series of smaller batches, truncate the log after each batch and I'd also take a full DB backup before the whole insert procedure takes place.

Hope this helps.

Cheers,
Leigh

Sure, if it has a microship in it, it must be IT... Now what seems to be the problem with your toaster...?

 

[JayKusch]

Leigh ... Good Idea about the backup!!!

Thanks

J. Kusch

 

[mm8294]

LeighMoore, excellent explanation and clarification!

I have saved your post and forwarded it to our client.



Thank you very much.