October 22, 2013 at 7:31 am
DBCC loginfo
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 7:32 am
New persopn (10/22/2013)
How can i check whether how many virtual log files in log
Check out the following blog as well as the "'8 Steps to better transaction log throughput" link that it refers to.
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few
October 22, 2013 at 8:35 pm
Here is a link to Michelle Ufford's post on getting the VLF count
http://sqlfool.com/2010/06/check-vlf-counts/
Another great tool comes from Pedro Lopes
Cheers
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
October 23, 2013 at 4:11 am
Thanks for your information.
as per your script i have executed in our server.
see the below resilts:
Actual log size: 369409.0
Used_log_size_mb :128.0
potential_log_size :368000.0
actual_VLF's :2890
used Vlf's:1
Potential_VLFs :736
growth_iteration :46
Log_initial_size:8000.0
File_auto_growth_mb:1024.0
Please advise me how can i take action here to reduce the log
October 23, 2013 at 10:24 am
The link that George Parker posted http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/ is a good place to start.
Basically, observe the size of your log. During a maintenance window shrink that puppy down as small as possible then expand it to the observed max size. Adjust your auto growth
2k+ is not uncommon, but not ideal, in my environment 99+ would be too high. Using Pedro's query you can check the size each growth iteration to see if you are bugged. I've seen databases where 200k VLFs were reached by 248KB growth per iteration (fixed by applying the latest service pack)
You might want to start a new thread for this specific question so that some of the pros can chime in
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
October 23, 2013 at 10:36 am
I'd also recommend you take a look through "SQL Server Transaction Log Management" by Tony Davis and Gail Shaw.
You can obtain an electronic copy of the book from this site: http://www.sqlservercentral.com/articles/books/94938/
There's also a SQLServerCentral stairway on the topic: http://www.sqlservercentral.com/stairway/73776/
Together, that should provide you with everything you ever wanted to know about managing the SQL Server transaction log.
October 23, 2013 at 10:48 am
New persopn (10/23/2013)
Please advise me how can i take action here to reduce the log
Have you monitored the used space in the log over the course of several days? If so, what's the maximum size that the used portion of the log reaches?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2013 at 7:30 am
yes i am monitor the log from last five days the maximum size of log used is : 7.03167
October 24, 2013 at 7:33 am
How often were you monitoring?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2013 at 8:18 am
i have created one job and it will run for every 15 mins and insert the results in to one table. here we have last four days data in that table
October 28, 2013 at 11:01 am
For one week i have monitored the log in weekends it is occupying 25% space so can anyone please update me on this.
October 31, 2013 at 2:24 pm
New persopn (10/28/2013)
For one week i have monitored the log in weekends it is occupying 25% space so can anyone please update me on this.
That sentence makes no sense.
If you found a max of 7GB, I'd probably shrink the log to 10GB. However, make sure that the max is in fact the max with all normal maintenance and workload operations inside the times you've monitored.
October 31, 2013 at 10:57 pm
Thanks for your information.
How can i monitor the complete log usage in that particular time.
November 1, 2013 at 1:46 am
New persopn (10/31/2013)
How can i monitor the complete log usage in that particular time.
As steve mentioned above.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy