Log space issue

Question

Log space issue

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

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

George M Parker SSCertifiable Points: 5412 More actions · Answer 2

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

dbassassin Default port Points: 1410 More actions · Answer 3

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

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 4

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

dbassassin Default port Points: 1410 More actions · Answer 5

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

George M Parker SSCertifiable Points: 5412 More actions · Answer 6

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

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

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 8

yes i am monitor the log from last five days the maximum size of log used is : 7.03167

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

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

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 10

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

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 11

For one week i have monitored the log in weekends it is occupying 25% space so can anyone please update me on this.

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 12

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.

New persopn SSCarpal Tunnel Points: 4019 More actions · Answer 13

Thanks for your information.

How can i monitor the complete log usage in that particular time.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 14

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;-)