1. Home
  2. Technology
  3. Microsoft SQL Server
  4. MS SQL Transaction Logs Full

MS SQL Transaction Logs Full

A Transaction log file is associated to each MS SQL database, even if the database is in simple recovery mode. While Simple recovery mode does not create logs as exensively as full recovery mode does, running a large query that is requiring signifigant memory and disk space can still create very large logs. If no limitations are set on the log size, it can fill up disk space, which in turn will cause CDR record processing to fail. However, the log sizes can be set through Microsoft SQL Server Studio, and can also be allowed to grow to a fixed limit if additional log space is required for larger queries (such as a manual purge of old data).

For example, if the log size is starting at 1MB and increasing to say, 1000MB, then something is being done that requires that space. If you lock the size to 500MB, you will eventually receive a “Transaction Log full” error when the query goes over that size. This is where the “Autogrowth” setting can let you allocate more space to the log file over the initially set size, while still placing a limit that is low enough to keep it from consuming all availiable disk space. In addition, resetting the log file to an initial size lower that what is currently displayed will shrink the log accordingly.

To manage log sizes and set limits  do the following.

Step 1) Launch Microsoft SQL Server Management Studio

Step 2) Locate your database and right-click on it. Select Properties.

Step 3) Click on Files section

Step 4) Locate the LOG FILE line.

Step 5) Change the Initial Size to: 500 (or whatever you want it to be)

Step 6) Locate the Autogrowth section and click on the ellipse (…)

Step 7) Choose either of the following:

Uncheck “Enable Autogrowth” to lock down the database to it’s initial size Click OK to change

or

Leave “Enable Autogrowth” checked to allow log file size increase. Choose the desired Maximum file size. Click OK to make the change.

Updated on March 30, 2020

Related Articles