Importar de TXT a SQL!!!!

[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.