Transaction Log Growing Up

SQL Server Administration
1

hi, my database in simple recovery mode. my transaction log is growing up.
how can i reduce this?
should i add new maintenance plan shrink database or what?

Regards.

2

Hi,
If its already in simple recovery model and your Tlog is still growing, first thing I will look is if there is any open transaction there, you can use the below command to perform the check:

DBCC OPENTRAN

If it does result something, it will means that you have an open transaction which prevent SQL server to archive the log. You can check if you can kill that session or ask the user to commit it. Once that is done, your tlog should be back to a normal state.

Hope this helps.

3

if there is no opened transaction, but my transaction log is big, then how can i reduce?
tlog is not growing up continuously but it's big and i would like to reduce it.
if i create a maintenance plan that shrink my database weekly, is this right strategy?
shrink database job also shrink log files, or shrink which files?
any side effects?
any negative performance effects?

regards.

4

Hi It may help yours:

  DBCC SHRINKFILE(<log_file_name_Log>)
  BACKUP LOG <database> WITH TRUNCATE_ONLY
  DBCC SHRINKFILE(<log_file_name_Log>)

To know more http://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log

5

but my database is in simple recovery mode.

6

you can simply shrink the file:

DBCC SHRINKFILE(log_file_name_Log, size you prefer)

7

if i create a maintenance plan that shrink my database weekly, is this right strategy?
shrink database job also shrink log files, or shrink which files?
any side effects?
any negative performance effects?

8

Shrinking files is not necessarily evil, but that doesn't mean that it is always the best thing to do.

9

you should not shrink your Tlog that often, in fact, I do not foresee it will growth back (given that your big transaction was one time only). If you do see the Tlog file keep growing back to the size before you shrink, which mean there are some process that require that space, you should cater it and keep the log file at that size and monitor it. That will be a better approach.

10

well i will manually shrink the log file and keep monitorig it.
but how can i determine target size?

DBCC SHRINKFILE(MYDB_LOG, ??TARGET SIZE??)

if i set it to 1, will be any negative performance effects?

regards.

11

Run the following query to display transaction log size and usage percent, locate the correct row for the database concerned.

DBCC SQLPERF (LOGSPACE)

If the Truncation or backup of the transaction log was successful the "Log Space Used" should be a very small percentage of the total
Log Size and smaller than noted in step 1a. Now calculate the target file size for the transaction log, do this using
the following formula:

(Current log size * Percentage used / 100) + 256
e.g. if Log Size = 4076.4 (MB) and Log Space Used(%) = 0.59, then Target Size = (4076.4 * 0.59 / 100) = 24 + 256 = 280.

Shrink the LDF using the command below, replacing TARGETSIZE with the number derived in Step 3c and LOGICALNAME with the
name noted in Step 3a.

DBCC SHRINKFILE (LOGICALNAME,TARGETSIZE)

--e.g. DBCC SHRINKFILE (MessageStats_log,3072) will shrink the MessageStats LDF to 3GB.

1 Like
12

You never want the log file to have to grow dynamically during normal operations because it's such huge overhead to grow the log file (see Note#1 below). Therefore, you need to determine the max log size you'll ever need for normal operations, then add 10% or so to avoid growth.

But the best log size is difficult to determine. You'd prefer oo have some knowledge of log usage for that specific db, but usually you don't, so you just have to make your best guess and then adjust from there.

For the first log adjustment, you should shrink it all the way down and then re-grow it to the size you need in increments (to avoid having too many VLFs). For example, say the log's grown to 50GB, which you know is too much, so you decide to drop it to 8GB.
USE db_name
--the first column contains the logical name of the file, so look for the log file, and copy its logical name
EXEC sp_helpfile

Then:
DBCC SHRINKFILE(2, 1)
ALTER DATABASE db_name MODIFY FILE ( NAME = logical_name_of_log_file, SIZE = 2GB )
ALTER DATABASE db_name MODIFY FILE ( NAME = logical_name_of_log_file, SIZE = 4GB )
ALTER DATABASE db_name MODIFY FILE ( NAME = logical_name_of_log_file, SIZE = 6GB )
ALTER DATABASE db_name MODIFY FILE ( NAME = logical_name_of_log_file, SIZE = 8GB )

From then on, if you needed to shrink the log, you'd never shrink below 8GB:
DBCC SHRINKFILE(2, 8192)
because you've already made sure you have a reasonable number of VLFs for that log size.

Note#1: Log space must be preformatted with binary zeros, which is a lot of I/O overhead, and could take quite a while (depending on your disk speed). Also, all db activity is paused until the log is expanded, so it also causes delays in processing.