Adding a Transaction Log at runtime to prevent it from running out of space

sometimes, when running a big transaction, the transaction log can become full or simply grow to a huge size . while you can sometimes free some space using “backup log db-name with truncate_only” and trying to shrink the DB using SQL Management studio.


The process is described in the above link but basically it is: right click on the Database name ->tasks->shrink->files and choosing the log file.
This will shrink the db if there is free space in the transaction log but it can only serve as a temporary solution.

If the transaction is still running and you are afraid that the hard drive will run out of disk space, a better solution would be to create another transaction log on a different drive. This will solve the disk space issue and might even improve performance.
To create another transaction log fileĀ  using the SQL Server Management Studio:

    1) Expand the DataBases node and choose your Database
    2) Right Click your Database and click Properties.
    3) Select the Files Page
    4) CLick ADD and Navigate to te Database files grid.
    5) Enter a name for the new log file. This name must be unique within the database.
    6) Select the Log file type
    7) select initial size, growth method (auto Growth, fixed Increments or percent). you can also set a maximum size. Set up the path to the new file and file name and Click OK

In Some cases, the file name column might be disabled when adding a log file using the Sql Server Mangment Studio while the transaction is still runing.
In this case you can add the file manually using a query.
Open a query editor and type:


USE master

GO

ALTER DATABASE db-name

ADD LOG FILE

( NAME = addedlog,

FILENAME = ‘I:addedlog.ldf’,

SIZE = 1000MB,

— MAXSIZE = 10000MB,

FILEGROWTH = 10MB)

GO

There are better ways to avoid the Transaction log is full situation and this should be prevented in the the first place by the code but if you got no access to it, this might be a good solution.