Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-08-06 : 08:03:12
|
| TommyJ writes "Is it possible to temporarily turn off the transaction logs of a specific database (SQL Server 7.0). I am importing data from a very large text file (12.8 million records) into a temp table using BCP. The data then needs to be pared down from 31 fields to 10, and a primary key and several indexes created for use as an OLAP. BCP is used because if I use a data pump task, the Transaction log fills up, and the import is cancelled, and the whole transaction rolls back. If I could stop this happening I would just use a single data transfom task to import the fields required into the final table. The BCP works fine, taking about 20 minutes to bring in the data. The problem occurs when I try to transfer data to the production table. The transfer should take 60 minutes, but always fails after about 50 minutes when the transaction log fills up, and the whole transfer rolls back. I have a limited window of about two hours to get the whole process complete. Increasing available disk space is not currently an option. If I try to break the secondary process into sections by selecting records with particular values the process will complete, but takes far too long (over three hours). The import will be done afresh once every week and the data in the production table is used purely for analysis, with no alterations ever made to the data. Hence no transaction logs are needed. My question is, is it possible to turn off the transaction logs for a limited period during this import? Any and all suggestions welcome." |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-06 : 13:54:40
|
quote:
Originally posted by AskSQLTeam
TommyJ writes "Is it possible to temporarily turn off the transaction logs of a specific database (SQL Server 7.0).
You could change your recovery model to Bulk-logged so that the bcp is minimally logged. You might also try SIMPLE recovery model. Check out the recovery models in SQL Server Books Online. But to answer your question, there is no way to turn them off, you can change the recovery model though which is kinda similar. ALTER DATABASE allows you to change the recovery models. You could build a script that runs prior to your process to change it to SIMPLE or Bulk-logged and then run another script after the process completes to change it back to FULL. But that assumes that your recovery model is set to FULL already though. Check what your recovery model is and let us know.Tara |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-06 : 19:15:55
|
| He's using SQL 7.0, there is no recovery model setting. Setting select into/bulk copy on the database will allow for non-logged bulk inserts, IF all of the indexes on the tables are dropped beforehand. This includes primary key and unique constraints as well. Since you're using a temp table you are probably already getting max performance.If you only have to import 10 of the 31 columns, are you using a format file to drop them out when importing into the temp table? Or are you importing all 31 and then excluding them in the transfer to production? If you are importing all 31, you can greatly speed up the process by having the format file exclude the columns you don't need. It will cut down on the amount of data being transferred, especially if the excess columns are relatively big character columns. Books Online has more details on "bcp format files", take a look, and if you're having trouble with them post here again, we can walk you through it.This also depends on whether or not you're doing additional cleanup on the data before inserting it. If you are, you can still use the format file to cut down on the columns being imported.You might also want to consider side-stepping the temp table and importing directly into the production table. Normally that's too risky to do, but if all else fails it will at least speed up the process. Since you're not worried about the transaction log you can set select into/bulk copy on for the production database. Same thing applies about indexes and constraints, you need to drop them all first, then re-create them after the bulk insert. Again the format file can help remove the excess columns from the file. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-06 : 19:17:47
|
quote:
Originally posted by robvolk
He's using SQL 7.0, there is no recovery model setting.
Ah, missed that part.Tara |
|
|
|
|
|
|
|
|
Transaction Log Full