Importar de TXT a SQL!!!!

[Saruman]

Hola a todos... como están?
tengo un problema a la hora de importar unos 41 millones de registro que está en un TXT. bueno, la importación la hace bien, pero el LOG de la base de datos queda en 11 GB.... no hay manera de que se pueda borrar eso, o reemplazar ese log por otro??

ya intenté crear un nuevo log en la base de datos, renombrarlo, ponerle que solo llegue hasta una cantidad de megas, por ejemplo, puse que solo llegue hasta 100MB pero manda un error mientras está importando que el LOG se llenó, luego lo puse en 1000MB y me pasó lo mismo....

no c que hacer.... tiene que haber una solución, ya que 11 GB de log está como raro no?? que opinan??
alguien que sepa de esto por favor

ok, saludos

[Saruman]

ya solucioné el problema....

Solución:
click derecho en la DB que quieren achicar el log, luego, all tasks y luego seleccionan Shrink DataBase...

alli, donde dice 10% le ponen como unos 50% (lean lo que les puse abajo y se informarán mejor)


lean esto:

Tips for Performance Tuning
SQL Server Database Setting

If your set your SQL Server 7 or SQL 2000 databases and transaction logs to grow automatically, keep in mind that every time this feature kicks in, it takes up a little extra CPU and I/O time. Ideally, you want to minimize how often automatic growth occurs. One way to help do this is to size the database and transaction logs as accurately as possible to their "final" size. Sure, this is virtually impossible to get right-on-target. But the more accurate your estimates (and some times it takes a some time to come up with a good estimate), the less SQL Server will have to automatically grow its database and transaction logs, helping to boost performance of your application.

This recommendation in particular is important to follow for transaction logs. This is because the more times that SQL Server has to increase the size of a transaction log, the more transaction log virtual files that have to be created and maintained by SQL Server, which increases recovery time, should your transactions log need to be restored. A transaction virtual file is used by SQL Server to internally divide and manage the physical transaction log file. [7.0, 2000] Updated 8-4-2003

In SQL Server 7 and SQL 2000, database and log files can be set to grow automatically. The default growth amount is 10%. This automatic growth number may or may not be ideal for your database. If you find that your database is growing automatically often (such as daily or several times a week), change the growth percentage to a larger number, such as 20% or 30%. Each time the database has to be increased, SQL Server will suffer a small performance hit. By increasing the amount the database grows each time, the less often it will have to grow.

If your database is very large, 10GB or larger, you may want to use a fixed growth amount instead of a percentage growth amount. This is because a percentage growth amount can be large on a large database. For example, a 10% growth rate on a 10GB database means that when the database grows, it will increase by 1GB. This may or may not be what you want. For example, a fixed growth rate, such as 100MB at a time, might be more appropriate. [7.0, 2000] Updated 8-4-2003

Mas Tips!!

[Carcharhinus]

Pueba con esto: dump tran <DB Name> with truncate_only.

[Carcharhinus]

How To Back Up and Truncate a SQL 2000 Transaction Log

What is the transaction log?
A database in Microsoft® SQL Server 2000 has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database.

SQL Server uses the transaction log of each database to recover transactions. The transaction log is a serial record of all modifications that have occurred in the database as well as the transaction that performed each modification. The transaction log records the start of each transaction. It records the changes to the data and enough information to undo the modifications (if necessary later) made during each transaction.

Each transaction log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions.

When would it be necessary to truncate a transaction log?
If log records were never deleted from the transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files, or the database would begin to have failed transactions if the growth were restricted. At some point in time, old, inactive log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records. The process of deleting these log records to reduce the size of the logical log is called truncating the log. Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file.

The most common error applicable to OneWorld is the appearance of a message "Unknown Translation to API" in the jde.log. In SQL logging, there will be an error 1105 'Could not allocate space for object '%.*ls' in database '%.*ls' because the '%.*ls' filegroup is full.' The '%' and '*ls* are usually replaced by object and database names.

Backing up a transaction log in SQL 2000
1. Notes: During a full database or differential backup, SQL Server backs up enough of the transaction log to produce a consistent database when the database is restored. This document assumes that the SQL server is operational and not in recovery state. Transaction log backups are used only with the Full and Bulk-Logged Recovery models.

There are several different ways to get to the backup operation for a database. This document utilizes the TaskPad view from Enterprise Manager. Place the cursor over the yellow dot of the Maintenance section on the General Tab. Then choose BackUp DataBase.

This is the dialog box that can be used to select a backup of the Transaction Log. This dialog box contains the selections for backing it up, selecting the destination of the backup file, and whether or not to overwrite or append to a previous backup. Most business cases will not need to use any of the choices on the 'Options' tab, the defaults will take care of this operation.

Truncating a transaction log in SQL 2000
1. When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space.

Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.

The SQL Query Window command to accomplish this is: dump tran <DB Name> with truncate_only.