Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Log space issue Expand / Collapse
Author
Message
Posted Saturday, October 19, 2013 2:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:17 AM
Points: 31,080, Visits: 15,526
It doesn't affect the database, but if the space is needed, the log will grow if autogrow is enabled, and stop accepting transactions if it isn't.

The size of the log should be set based on the workload. Nothing else.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1506416
Posted Sunday, October 20, 2013 12:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
New persopn (10/19/2013)
here i have shrink the log now we got 30 gb space. here we have 360gb log now. can i reduce it up to 300gb.


No way to answer that. We don't know your environment, we don't know how large the log needs to be. Maybe you can, maybe you can't, maybe it'll grow straight back, maybe it won't.

You need to do some investigations, see what it is that requires the most log space, how much it needs, whether or not it can be tuned.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1506499
Posted Sunday, October 20, 2013 9:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:58 AM
Points: 139, Visits: 402
Thanks for u r information. i will monitor the log for few days and i will let you know how much it is occupying.
Post #1506531
Posted Tuesday, October 22, 2013 7:22 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:08 AM
Points: 80, Visits: 260
If your log has experienced lots of autogrowth you might want to check the number of vlfs. I've seen 3 cases in the wild where vlfs have been extremely high, mostly on 2008 r2 sp1 (sp2 has the fix).

Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Post #1507121
Posted Tuesday, October 22, 2013 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:58 AM
Points: 139, Visits: 402
How can i check whether how many virtual log files in log
Post #1507127
Posted Tuesday, October 22, 2013 7:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
DBCC loginfo




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1507132
Posted Tuesday, October 22, 2013 7:32 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 652, Visits: 1,428
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
Post #1507134
Posted Tuesday, October 22, 2013 8:35 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:08 AM
Points: 80, Visits: 260
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

http://blogs.msdn.com/b/blogdoezequiel/archive/2011/07/21/sql-swiss-army-knife-10-vlfs-again-what-s-your-current-status.aspx

Cheers



Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Post #1507449
Posted Wednesday, October 23, 2013 4:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:58 AM
Points: 139, Visits: 402
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
Post #1507533
Posted Wednesday, October 23, 2013 10:24 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:08 AM
Points: 80, Visits: 260
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
Post #1507731
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse