June 7, 2019 at 9:04 pm
I am managing a migration project for several thousand databases across dozens of SQL servers. These db imports generate very large log files for each database. Once the import is complete, the log file size needed for each db is very small. In order to avoid needing very large log file drives on each server, only to need to shrink/reclaim space when done, I'm trying to use a large utility drive (E:\) for the migrations which can just be torn down when the project is complete.
The empty databases are pre-created by another process on the default D:\data and L:\log drives, so I need to move the log file to the E:\ drive prior to the migration. Here is a simplified demo version of my code:
use master
/*
use Org123_testDB--starting locations are D:\Data and L:\Log
exec sp_helpfile
use master
*/
use master
ALTER DATABASE Org123_testDB MODIFY FILE (NAME = Org123_testDB_log, FILENAME = 'E:\MSSQL\Org123_testDB_log.ldf')
exec sp_detach_db Org123_testDB
create database Org123_testDB ON (FILENAME = 'D:\MSSQL\Data\Org123_testDB.mdf') for ATTACH_REBUILD_LOG
Here is the output
The file "Org123_testDB_log" has been modified in the system catalog. The new path will be used the next time the database is started.
File activation failure. The physical file name "E:\MSSQL\Org123_testDB_log.ldf" may be incorrect.
Msg 5170, Level 16, State 1, Line 12
Cannot create file 'L:\MSSQL\Log\Org123_testDB_log.ldf' because it already exists. Change the file path or the file name, and retry the operation.
Msg 1813, Level 16, State 2, Line 12
Could not open new database 'Org123_testDB'. CREATE DATABASE is aborted.
Curiously this same code works fine in the other direction (for re-creating the log file back on L:\ after a migration).
Thoughts anyone?
June 8, 2019 at 3:45 pm
Instead of moving the log file - why don't you add a secondary log file in the new location. Once the migration process is completed you can then remove the secondary log file.
There are also methodologies that you can incorporate into the migration process to reduce the usage of the transaction log. If you change the recovery model to simple during the migration - and follow the guidelines for minimally logged operations - that could have a dramatic effect.
If you are using SSIS to move the data - the OLEDB Destination has the fast table load option where you can set batch/commit sizes which will keep the log file from growing. BCP also has options for batch/commit as well as bulk insert. If this is manually performed - then loading the data in smaller batches would also reduce log usage.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply