Transaction Log Management

ADD New Log File:
If you need to enlarge your transaction log, one method you can use is to add a file to the log.
You can do this by using the ADD LOG FILE clause of the ALTER DATABASE statement.
In addition to specifying the logical and physical names of the new log file, you can define the following three arguments:
•SIZE: The initial size of the log file. You can specify the size as KB, MB, GB, or TB, such as 10 MB or 1 GB. If you do not specify a size when you add the file, the database engine uses the default size of 1 MB.
•MAXSIZE: The maximum size that the file can grow to. You can specify the size as KB, MB, GB, or TB. If you do not specify a maximum size, the file will grow until it fills the disk (assuming the space is needed).
•FILEGROWTH: The growth increment used when expanding the file. You can specify the size as KB, MB, GB, or TB, or you can specify the size as a percentage, such as 10%. If a number is specified without a suffix, MB is used. If no number is specified, 10% is used. A value of 0 indicates that no automatic growth is allowed.
The following example adds the EmployeeDB_log2 file to the EmployeeDB transaction log:
ALTER DATABASE EmployeeDB
ADD LOG FILE
(
    NAME = EmployeeDB_log2,
    FILENAME = 'C:\SqlData\EmployeeDB2.ldf',
    SIZE = 2MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 10%
)


Delete Log File:
You can also use the ALTER DATABASE statement to remove a log file by specifying the REMOVE FILE clause, as shown in the following example:
ALTER DATABASE EmployeeDB
REMOVE FILE EmployeeDB_log2;


How do I move a Transaction Log to another drive:
The following preliminary items should be reviewed prior to moving a transaction log file to a new location.
•Record the current location, size, etc. of the database files
•Record the current location, size, etc. of the transaction log file thatis going to be moved.
•Note the location, size, etc. of the future destination of the transactionlog file
•Schedule a downtime to move the database when no users are connected tothe application
•Validate the database is not in any replication scheme, on a snapshot scheduleor a member of a mirror ◦If so, plan accordingly and build the scripts to address these configurations
•Ensure your are a member of the db_owner fixed role
In a nutshell, the three main steps involved in moving a log file to new location are:
1.Detach the database
2.Move log file to new location
3.Attach the database by referencing the new location of the transaction logfile.