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.

Check Log File Size

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.

Add log file to database

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

  1. 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.
  2. In the Database Properties dialog box, select the Files page from left side pane.
  3. To add a transaction log file, click Add button from lower right side pane.
  4. 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.
  5. 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.
  6. 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.

Read More

I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Ganesh Bekkinkeri

I am working as SQL Server DBA in one of the biggest IT company in the world. I have very good knowledge on SQL Server database administration. Please contact me at info@techyaz.com if you have any question.
Summary
Manage Transaction Log File during Data Load
Article Name
Manage Transaction Log File during Data Load
Description
Manage Transaction Log File during Data Load: We observed that the log file was growing extensively during data load. This was potential risk that could have caused this import to fail. We had to manage transaction log file to avoid this risk and make this data load successful. We had option to add log file to the database or extend the log drive.
Author
Publisher Name
www.techyaz.com

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *