x

Shrinking of the log file

In some of my databases log file grew to 1 or 2 gig, so I need to do the transactional log backup as my database is in full recovery mode. After that step I need to shrink the log file, which log file am I shrinking? Is it the transactional or the log file that is with the database. I am reviewing the following article http://msdn.microsoft.com/en-us/library/ms178037.aspx, but I cannot get a clear picture of the process. Using the following command:

DBCC SHRINKFILE ( { file_name | file_id } { [ , EMPTYFILE ] | [ [ ,target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ] } ) [ WITH NO_INFOMSGS ]

How do I know all the values of the argument. If some one has a step by step method can you please share it with me. Thanks in advance for all the suggestion.
more ▼

asked Oct 25, 2010 at 01:01 PM in Default

csaha2 gravatar image

csaha2
124 6 6 7

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

Please also take the time to make sure that your transaction log is setup correctly after shrinking.

If the log has grown to a certain size, it may be better to keep it there. However, it is vitally important for you to make sure that the log file is internally "clean".

The transaction log is split up into virtual log files (VLFs) and there can be some nasty surprises when the VLF count gets too high.

Take ten minutes out to read up on the subject on Kim Tripp's blog:

  1. 8 Steps to better transaction log throughput
  2. Transaction Log VLFs

The VLF can balloon on you if you have a small autogrowth increment. Kim shows you some good tips on controlling the growth an making sure your system runs smoother.

Be aware that this can still go wrong on you: [Why is the VLF count ballooning on me][3]

[3]: http://ask.sqlservercentral.com/questions/15495/why-is-the-vlf-count-ballooning-on-me
more ▼

answered Oct 25, 2010 at 01:55 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

There was a note in the article you referenced that you should pay careful attention to before automatically shrinking your log files:

Shrinking database and log files can be set to occur automatically. However, we recommend against automatic shrinking

Unless you're sure your log files won't grow again, it's not always a good idea to shrink them. If you back them up consistently, they should reach a fairly stable size and stay there. If you keep shrinking them, they will continue to grow as they seek that stable size. Sort of a vicious cycle.
more ▼

answered Oct 25, 2010 at 01:42 PM

KenJ gravatar image

KenJ
19.7k 1 3 11

Thanks for the advice. I also see some of the databases have auto shrink turned on. Can I just change that parameter. What are the disadvantages? Can this be done when the users are using the database?
Oct 25, 2010 at 01:58 PM csaha2
You can turn autoshrink off at any time. It is generally recommended to turn that off and to turn AutoClose off too
Oct 25, 2010 at 02:25 PM WilliamD
An answer to a very similar question has some good transaction log management resources listed: http://ask.sqlservercentral.com/questions/26853/transactional-logs-growing-huge?page=1#26865
Oct 25, 2010 at 02:42 PM KenJ
(comments are locked)
10|1200 characters needed characters left
DBCC SHRINKFILE (2, TRUNCATEONLY);
more ▼

answered Oct 25, 2010 at 01:22 PM

ozamora gravatar image

ozamora
1.4k 2 3 5

After running the following statement it returned only 1 line with dbid. So after creating the maintenance plan for the transactional log, I can run the folllwing DBCC SHRINKFILE (2, TRUNCATEONLY) is there any other step I need to run? Please advice.
Oct 25, 2010 at 01:30 PM csaha2

No. Now you need to be aware that you should size your log files in a way to minimize log increases as Virtual Log Files are really detrimental for performance.

What is the reason for you to shrink the log file? Are you running out of space on that drive? If that is NOT the case, you might just want to leave it at 2 GB.
Oct 25, 2010 at 01:37 PM ozamora
Yes, space is an issue, I will be also moving user databases to a separate drive this weekend. But I also saw some of the databases have datafile = 406MB but log file 4gig and some have datafile and logfile 2 gig and as I mentioned earlier I was not backing up transactional log. So what should I do? Just backup of the transactional log will be okay or do I also need to do the following. DBCC SHRINKFILE (2, TRUNCATEONLY); Please advice.
Oct 25, 2010 at 01:54 PM csaha2

This is my recommendation once you have enough space.

  1. Perform a full database backup
  2. Perform a log backup
  3. Shrink the log with truncate only
  4. Resize the log at an appropriate size so it does not grow anymore
  5. Configure size increase in an specific number (eg 500 MB) so if it really needs to grow, it does it in a big chunk.
6. Ensure that you perform log backup operations to keep the log size under control
Oct 25, 2010 at 01:59 PM ozamora
  1. Perform a full database backup Full recovery mode, full backup ones per day and differential log every 4 hrs.
  2. Perform a log backup I will perform transactional log backup. But how often? Is every hr 1 or every 2 hrs good?
  3. Shrink the log with truncate only DBCC SHRINKFILE (2, TRUNCATEONLY); Is this the script? Do I need to specify the logfile name?
  4. Resize the log at an appropriate size so it does not grow anymore

How do I resize, what size should I choose? Is it 500MB 5. Configure size increase in an specific number (eg 500 MB) so if it really needs to grow, it does it in a big chunk. 6. Ensure that you perform log backup operations to keep the log size under control
Oct 25, 2010 at 02:16 PM csaha2
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x344

asked: Oct 25, 2010 at 01:01 PM

Seen: 1992 times

Last Updated: Oct 25, 2010 at 01:01 PM