Problem with transaction Logging and Inserts

[DotNetBlocks]

Hello,
I am having a problem inserting data in to a shared hosting MS SQL becuase of space. When ever i try and import data from my local machine to the hosted SQL server the transaction logs get so full that they kill my space before my import has completed. Is there any way i can turn off loging so i can get the data over to the new server?

 

[ptheriault]

set the database recovery model to simple or Bulk logged if you doing a bulk import.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[DotNetBlocks]

Thanks Paul,
I do like your suggestion, but is there any way just to turn off the transactions temporarily? I do not think my hosting company will allow me to do that.

once again thanks,
BABloome

 

[ptheriault]

No, It is the nature of a RDBMS to log it's transactions. By changing the recovery model to simple you log less and truncate the log on checkpoint. However, IF this import is one huge transaction then your log will still grow out of control. If you do it as a bulk import change the recovery model to bulk logged. That is the purpose of that recovery model. it is common practice to change the the recovery model before a bulk import. This is what I do.

Backup log
change recovery model to bulk logged
run import
backup log
change recovery model back to full.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[DotNetBlocks]

Thanks! I will try this tonignt and let you what happens.

Babloome

 

[SQLSister]

Also your problem may be that you are not backing up the transaction log and so actaully do not have any space left. The transaction log will continue to grow unless it is backed up or truncated manually. A backup on the database itself will not solve this problem. You must be doing transaction log backups if you want the log to stop growing exponentially.

Questions about posting. See faq183-874

 

[DotNetBlocks]

I just noticed that if forgot to mention that this is a one time import on a new database that is completely empty. So there is nothing really to backup yet.

I am not sure if this will effect anything.

 

[SQLDenis]

can you backup locally and restore remotely instead?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLSister]

YOu might try seeing if your transaction logs are set to grow automatically. And then you might increase the percentage of growth if that doesn't work.

Questions about posting. See faq183-874

 

[DotNetBlocks]

Can i back up a 2005 database and restore it back to 2000?

 

[SQLDenis]

>>Can i back up a 2005 database and restore it back to 2000?


NOooooooo you can't ;-(

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[DotNetBlocks]

Man, that stinks!!!!!!