SQL Server: How do I increase the size of the transaction log?

Question

How do I increase the size of the transaction log? Is is also possible to temporarily increase the transaction log?

Let's say I have the following scenario. I have a Delete operation that's too big for the current transaction log. I wan't to:

• Increase the transaction log (can I detect the current size?, can I tell how large I need the transaction log to be for my operation?)
• (Perform my operation)
• Backup the transaction log
• Restore the size of the transaction log.

Answer 1

The transaction log can be configured to expand as needed. You set the option to grow automatically. However when the transaction log gets too big (running out of disk space) or making sql server unusable.

Back up transaction log. SQL will auto truncate inactive transactions

When you restore the transaction log will be reduced

To configure autogrow:

1. Right click on the database in management studio.
2. Select Properties
3. Update Autogrowth value

Answer 2

The most important part is the last line of your scenario: "Restore the size of the transaction log." You mean shrink the log back to its original size.

This is really dangerous for a lot of reasons, and we've covered them in a couple of stories over at SQLServerPedia:

• http://sqlserverpedia.com/wiki/Shrinking_Databases
• http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/
• http://sqlserverpedia.com/blog/sql-server-bloggers/i-was-in-the-pool-dealing-with-sql-shrinkage/

Answer 3

Short answer:

• SQL 2k5/2k8 How to: Increase the Size of a Database (SQL Server Management Studio) (applies to log also), How to: Shrink a Database (SQL Server Management Studio)
• SQL 2K How to increase the size of a database (Enterprise Manager), How to shrink a database (Enterprise Manager)

Long answer: you can use ALTER DATABASE ... MODIFY FILE to change the size of database files, including LOG files. You can look up master_files/sysfiles (2k) or <dbname>.sys.database_files (2k5/2k8) to get the logical name of the log. And you can use DBCC SHRINKFILE to shrink a file (if possible).

can I tell how large I need the transaction log to be for my operation?

It depends on a lot of factors (is this new data? is it an update? is it a delete? what recovery model? Do you have compression on SQL 2k8? etc etc) but is usually bigger than you expect. I would estimate 2.5 times the size of the update you are about to perform.

Update:

Missed you say is an DELETE. A rough estimate is 1.5 times the size of the data deleted (including all indexes).