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 |
|
potinenir
Starting Member
19 Posts |
 Posted - 2003-09-02 : 14:06:44
|
| how can we move a table of 55gig in size onto different filegroup bypassing the transaction log. When i try to move the table into a different filegroup the transaction log also grows with the same size of the table and we don't have space to accomodate the log. So can we bypass the transaction log and move the table into a different filegroup?? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-02 : 14:16:17
|
| From SQL Server Books Online:Logged and Minimally Logged Bulk Copy OperationsWhen using the full recovery model, all row-insert operations performed by bcp are logged in the transaction log. For large data loads, this can cause the transaction log to fill rapidly. To help prevent the transaction log from running out of space, a minimally logged bulk copy can be performed if all of these conditions are met: -The recovery model is simple or bulk-logged.-The target table is not being replicated.-The target table does not have any triggers.-The target table has either 0 rows or no indexes.-The TABLOCK hint is specified. For more information, see Controlling the Locking Behavior. Any bulk copy into an instance of Microsoft® SQL Server™ that does not meet these conditions is logged.Before doing bulk copy operations, it is recommended that you set the recovery model to bulk-logged if you usually use full recovery. This will prevent the bulk copy operations from using excessive log space and possibly filling the log. However, even with bulk-logged recovery, some transaction log space will be used. You may want to create transaction log backups during the bulk copy operation to free up transaction log space.When bulk copying a large number of rows into a table with indexes, it can be faster to drop all the indexes, perform the bulk copy, and re-create the indexes. For more information, see Optimizing Bulk Copy Performance. Note Although data insertions are not logged in the transaction log when a minimally logged bulk copy is performed, SQL Server still logs extent allocations each time a new extent is allocated to the table.Tara |
 |
|
|
|
|
|
|
|
moving tables