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:
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 vlivk the database in management studio
select properties
check autogrow
Short answer:
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).
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: