All geek questions in one place

SQL Server: how to increase transaction log size?

How to increase the size of the transaction log? Is it possible to temporarily increase the transaction log?

Say I have the following scenario. I have a delete operation that is too large for the current transaction log. I do not want:

  • Increase the transaction log (can I determine the current size?), Can I tell how much I need the transaction log for my operation?)
  • (Perform my operation)
  • Transaction Log Backup
  • Restore transaction log size.
+4
source share
3 answers

Short answer:

Long answer: you can use ALTER DATABASE ... MODIFY FILE to resize database files, including LOG files. You can find 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 compress the file (if possible).

Can I say how much I need a transaction log for my transaction?

It depends on many factors (is this new data - is it an update? Is it a deletion? What is the recovery model? Do you have compression on SQL 2k8? Etc. etc.), but usually more than you expect. I would rate 2.5 times the size of the update you are about to perform.

Update:

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

+3
source

The transaction log can be configured to expand as needed. You set the possibility of automatic growth. However, when the transaction log becomes too large (running out of disk space) or makes the sql server unusable.

Backup transaction log. SQL will automatically disable inactive transactions

When restoring the transaction log will be reduced

To configure autoload:

  • Right-click on the database in the management studio.
  • Select Properties
  • Update startup value
+3
source

The most important part is the last line of your script: Restore Transaction Log Size. You mean the reduction of the magazine to its original size.

This is really dangerous for many reasons, and we covered them in several stories in SQLServerPedia:

+1
source

Source: https://habr.com/ru/post/1286401/

More articles:


All Articles