Manage Transaction Log File during Data Load
Have you even face that your transaction log file is growing exceptionally and you have to manage log file to make it in his size limit? Managing transaction log file is not an easy job if you have to run huge data import. I was working on a migration project where we had to import huge set of data into SQL Server database. We had changed multiple configurational parameters to speed up data migration and set appropriate data and log file size before starting the migration. We observed that the log file was growing extensively during data load. Log file was almost 80% full and data load was still in progress. This was a potential risk that could have caused this import to fail. We had to manage transaction log file during data load to avoid this risk and make this import successful without any issue. We had options to add log file to the database or extend the log drive. I used DBCC SQLPERF command to get the log space usage statistics. Please have a look at the log space size of database TY1 in last row.
We had few options to overcome this issue. As we were running this database in simple recovery mode, so we didn’t have an option to run the Transaction Log backup. We tried to shrink the log file to manage transaction log file but it was not allowing us to do so. This might be due to the data load execution was in process and active transaction.
Finally, we left out with two options; either extend the log drive or add a log file to the database on a drive that has enough space to accumulate the logs. Extending the existing drive was not possible because data load was in progress and we need to fix this issue without stopping the import/data load. So, we decided to go with another option that was to add a database log file to a drive that has enough space. I have given you step by step method to add a log file to a database in below section.
Add Log File to a Database
I will show you how to add log file to a database using T-SQL statements and using GUI in SQL Server Management Studio to manage transaction log file during data load. This way you can manage if your transaction log file is growing faster.
Using T-SQL Statement
Open new query window and run below T-SQL statement to add a log file TY1_Log3.ldf to database TY1 on F drive. Have a look at below T-SQL code to get other details like sizing and growth details.
--Replace your Database Name and file details in below T-SQL Code. ALTER DATABASE [TY1] ADD LOG FILE (NAME = 'TY1_Log3', FILENAME = 'F:\TY1_Log3.ldf', SIZE = 20080 MB, FILEGROWTH = 5024 MB) GO
Once you execute above T-SQL statement, your log file TY1_Log3.ldf will be added on F drive to the database TY1.
Remember, add the log file in a drive where you have enough space. If you will add log file to data file drive then you might face IO pressure because both data and log files will be on same drive so avoid to keep data and log file on same drive.
Using GUI in SSMS
- Launch SQL Server Management Studio and connect to the instance of the SQL Server Database Engine. Expand Databases folder, right-click the database in which you have to add the log file, and then click Properties.
- In the Database Properties dialog box, select the Files page from left side pane.
- To add a transaction log file, click Add button from lower right side pane.
- In the Database files grid, enter a logical name for the file. The file name must be unique within the database. Select the file type as log. Specify the initial size of the file. Set Autogrowth column as per your requirement.
- Specify the path for the file location along with physical log file name. The specified path must exist before adding the file. Make sure that your drive has enough space to host this log file.
- Click on OK button to create this add this log file to the database.
Once you performed step 6, log file will be added to your database on your identified drive name. These exercises (adding log file to database) doesn’t require any downtime or it will not interrupt any existing transactions. This way you can manage transaction log file during data load if your transaction log file is growing faster.
- How to get Total no. of Virtual Log Files in a Transaction Log file?
- Understanding Transaction Log File Architecture
- Why Should You Always Turn Off Database Auto Shrink Property?
- What is the best value for database file autogrowth?
- How to Install SSMA for Oracle to Migrate Oracle Database to SQL Server - May 12, 2018
- How to Add a New Database to Always On Availability Group? - January 22, 2018
- How to Remove Database from Always On Availability Group? - January 19, 2018