transaction log - How Can I Manage SQL Server Log Size -


I am trying to manage the size of a SQL Server 2008 log file. I have a reporting database that day Once loads in. The simple recovery model is the best fit because there is no transaction except the morning load, and I can recreate those records. My goals need to be logged on a certain size, it is not enough to allocate new space during the load.

My problem is that logs are increasing, I know that logs should be marked for reuse, but why do logs keep rising? Log_reuse_wait_desc shows "nothing".

I can shorten the log file in 1 MB, so I know that there is no transaction in it. I can set up a job to do this, but I prefer to leave the log in 500 MB, which is enough to handle daily load. If I do this, the log grows.

How can I keep logging in a constant shape?

Please note: Deleting the log file does not resolve this problem. I know how to do this. I am trying to find a way out of the transaction log in the reuse log.

The log file is used for transactional purposes, even if the database is in simple recorse mode LOG If the shape is actually moving beyond 500 MB, then a person is running a query or stored procedure that requires that space. For example, maybe you're indexing your reporting tables. This will be done within a transaction so that the changes can be brought back in case of an error. However, the used space will be completed once after the second transaction.

Therefore, if the log size is starting at 1MB and is rising to say 700MB, then something is being done that requires that place. If you lock the size up to 500 MB, you will eventually get a "log file off space" error.

But if you really want to fix it in 500 MB, you can do the following: (Using SQL Server 2005)

  1. Microsoft SQL Launch server management studio
  2. Find and dock your database
  3. Click on File section
  4. Find the line of the log file.
  5. Change the starting size to: 500
  6. Locate the Autogrowth section and the oval (...)
  7. Uncheck "Enable Autogrowth" Click OK.
  8. Click OK to make changes.

Note: You can also set the maximum log file size in the "Autogoroth section".

Alternatively, you can use the following script to make changes. Replace DATABASENAME with fair value. If necessary, change the log file name as well. 

  USE [Master] GO ALTER DATABASE [DatabaseName] Modified file (NAME = N'DATABASENAME_Log ', SIZE = 512000KB, FILEGROWTH = 0) Go

Comments

Post a Comment