|
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.
(comments are locked)
|
|
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: 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
(comments are locked)
|
|
There was a note in the article you referenced that you should pay careful attention to before automatically shrinking your log files:
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. 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 '10 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 '10 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 '10 at 02:42 PM
KenJ
(comments are locked)
|
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 '10 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 '10 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 '10 at 01:54 PM
csaha2
This is my recommendation once you have enough space.
Oct 25 '10 at 01:59 PM
ozamora
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 '10 at 02:16 PM
csaha2
(comments are locked)
|
