Sfrgttk

Does stroke to path essentially create two different objects?

Is the symmetric product of an abelian variety a CY variety?

What's the reason that we have a different number of days each month?

How to fly a direct entry holding pattern when approaching from an awkward angle?

Is it really OK to use "because of"?

How to check if remote-signer is working as expected?

Why did Ylvis use "go" instead of "say" in phrases like "Dog goes 'woof'"?

Why does this relation fail symmetry and transitivity properties?

Why do neural networks need so many examples to perform?

How can I deduce the power of a capacitor from its datasheet?

"I showed the monkey himself in the mirror". Why is this sentence grammatical?

Insecure private-key encryption

Is it possible to detect 100% of SQLi with a simple regex?

Players preemptively rolling, even though their rolls are useless or are checking the wrong skills

my cron command doesn’t work

How to put text above column in minipage?

How resistance converts voltage to current?

What is a good way to explain how a character can produce flames from their body?

Maybe pigeonhole problem?

Plausible reason for gold-digging ant

No option to ask a question in https://developer.salesforce.com discussion forums

How to deal with an underperforming subordinate?

How much light is too much?

The relationship between entanglement of vector states to matrix operations

Too many VLFs - How do I truncate them?

How to know when/if I have too many indexes?How important are transaction log VLFs for performance?Virtual Log Files (VLFs) FragmentationWhat's worse, too many entities or too many tables?How to handle too many inserts?Unable to shrink log file due to snapshot replicationPivots giving too many resultsHow many log files does a database usually have?How many internal drives are too many?MySQL `innodb_log_file_size`: how big is too big?

0

I have a database that has log file of around 64 GB and the VLF count for the same is 500+. Almost all of them are active. I checked the open transactions and the oldest transaction on that database is from two days back. The session for that transaction is in SUSPENDED state. So, if I kill this SPID, will it mark the VLFs as INACTIVE ? Or will it go into roll back mode and run for another couple of days without marking the VLFs as INACTIVE?

I am not even able to identify from where this transaction is originated. The host name just displays a web server and the Program Name has .Net SqlClient Data Provider so this looks like some transaction initiated by some code in the application.

sql-server transaction-log

share|improve this question

asked Oct 20 '16 at 23:41

karun_rkarun_r

156312

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

0

I have a database that has log file of around 64 GB and the VLF count for the same is 500+. Almost all of them are active. I checked the open transactions and the oldest transaction on that database is from two days back. The session for that transaction is in SUSPENDED state. So, if I kill this SPID, will it mark the VLFs as INACTIVE ? Or will it go into roll back mode and run for another couple of days without marking the VLFs as INACTIVE?

I am not even able to identify from where this transaction is originated. The host name just displays a web server and the Program Name has .Net SqlClient Data Provider so this looks like some transaction initiated by some code in the application.

sql-server transaction-log

share|improve this question

asked Oct 20 '16 at 23:41

karun_rkarun_r

156312

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

I have a database that has log file of around 64 GB and the VLF count for the same is 500+. Almost all of them are active. I checked the open transactions and the oldest transaction on that database is from two days back. The session for that transaction is in SUSPENDED state. So, if I kill this SPID, will it mark the VLFs as INACTIVE ? Or will it go into roll back mode and run for another couple of days without marking the VLFs as INACTIVE?

I am not even able to identify from where this transaction is originated. The host name just displays a web server and the Program Name has .Net SqlClient Data Provider so this looks like some transaction initiated by some code in the application.

sql-server transaction-log

share|improve this question

asked Oct 20 '16 at 23:41

karun_rkarun_r

156312

share|improve this question

asked Oct 20 '16 at 23:41

karun_rkarun_r

156312

share|improve this question

asked Oct 20 '16 at 23:41

karun_rkarun_r

156312

asked Oct 20 '16 at 23:41

karun_rkarun_r

156312

asked Oct 20 '16 at 23:41

karun_rkarun_r

156312

1 Answer
1

active

oldest

votes

0

Download and install the dbo.sp_WhoIsActive stored procedure from http://whoisactive.com

Run this procedure and it will tell you what is currently running. It will also tell you a host of further information such as what the current sessions are waiting on and where they originate from.

To answer the title of your question. To reduce the number of VLF, you need to shrink your log file using DBCC SHRINKFILE, then grow your log file back to 64GB in 16GB increments to give you 64 VLF.

share|improve this answer

edited Nov 12 '18 at 17:14

Aaron Bertrand♦

152k18289489

answered Oct 21 '16 at 9:50

Andy JonesAndy Jones

1,24148

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Question - any reason behind suggesting this number of VLFs?
  
  – Marcin S.
  Oct 21 '16 at 10:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  There is no absolute correct answer for the number of VLF. You just don't want to have thousands or tens of thousands as this can negatively impact recovery time.
  
  – Andy Jones
  Oct 21 '16 at 10:57
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I know more or less official recommendations, was curious about Your 64 VLF number, as it's rather on the opposite end - too few of them, especially for that size of log file.
  
  – Marcin S.
  Oct 21 '16 at 12:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just a comment, I asked in one of Brent Ozar's office hours at what quantity a dba should be concerned about the high number of vlfs. They replied that anything over 5000 is something to be concerned with especially regarding Andy's comment above with recovery times. To remove an excessive number of vlfs, we chg the recovery model to simple, shrink the log, chg back to full, then take a full backup.
  
  – rvsc48
  Oct 21 '16 at 13:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MarcinS. My general aim is for around 200 VLFs. 64GB is not a large log file by any stretch of the imagination, so 64 is an acceptable number of VLFs.
  
  – Randolph West
  Oct 21 '16 at 20:10
  
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f152933%2ftoo-many-vlfs-how-do-i-truncate-them%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Download and install the dbo.sp_WhoIsActive stored procedure from http://whoisactive.com

Run this procedure and it will tell you what is currently running. It will also tell you a host of further information such as what the current sessions are waiting on and where they originate from.

To answer the title of your question. To reduce the number of VLF, you need to shrink your log file using DBCC SHRINKFILE, then grow your log file back to 64GB in 16GB increments to give you 64 VLF.

share|improve this answer

edited Nov 12 '18 at 17:14

Aaron Bertrand♦

152k18289489

answered Oct 21 '16 at 9:50

Andy JonesAndy Jones

1,24148

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Question - any reason behind suggesting this number of VLFs?
  
  – Marcin S.
  Oct 21 '16 at 10:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  There is no absolute correct answer for the number of VLF. You just don't want to have thousands or tens of thousands as this can negatively impact recovery time.
  
  – Andy Jones
  Oct 21 '16 at 10:57
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I know more or less official recommendations, was curious about Your 64 VLF number, as it's rather on the opposite end - too few of them, especially for that size of log file.
  
  – Marcin S.
  Oct 21 '16 at 12:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just a comment, I asked in one of Brent Ozar's office hours at what quantity a dba should be concerned about the high number of vlfs. They replied that anything over 5000 is something to be concerned with especially regarding Andy's comment above with recovery times. To remove an excessive number of vlfs, we chg the recovery model to simple, shrink the log, chg back to full, then take a full backup.
  
  – rvsc48
  Oct 21 '16 at 13:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MarcinS. My general aim is for around 200 VLFs. 64GB is not a large log file by any stretch of the imagination, so 64 is an acceptable number of VLFs.
  
  – Randolph West
  Oct 21 '16 at 20:10
  
  
  

  
  
  
  

add a comment | 

0

Download and install the dbo.sp_WhoIsActive stored procedure from http://whoisactive.com

Run this procedure and it will tell you what is currently running. It will also tell you a host of further information such as what the current sessions are waiting on and where they originate from.

To answer the title of your question. To reduce the number of VLF, you need to shrink your log file using DBCC SHRINKFILE, then grow your log file back to 64GB in 16GB increments to give you 64 VLF.

share|improve this answer

edited Nov 12 '18 at 17:14

Aaron Bertrand♦

152k18289489

answered Oct 21 '16 at 9:50

Andy JonesAndy Jones

1,24148

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Question - any reason behind suggesting this number of VLFs?
  
  – Marcin S.
  Oct 21 '16 at 10:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  There is no absolute correct answer for the number of VLF. You just don't want to have thousands or tens of thousands as this can negatively impact recovery time.
  
  – Andy Jones
  Oct 21 '16 at 10:57
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I know more or less official recommendations, was curious about Your 64 VLF number, as it's rather on the opposite end - too few of them, especially for that size of log file.
  
  – Marcin S.
  Oct 21 '16 at 12:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just a comment, I asked in one of Brent Ozar's office hours at what quantity a dba should be concerned about the high number of vlfs. They replied that anything over 5000 is something to be concerned with especially regarding Andy's comment above with recovery times. To remove an excessive number of vlfs, we chg the recovery model to simple, shrink the log, chg back to full, then take a full backup.
  
  – rvsc48
  Oct 21 '16 at 13:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @MarcinS. My general aim is for around 200 VLFs. 64GB is not a large log file by any stretch of the imagination, so 64 is an acceptable number of VLFs.
  
  – Randolph West
  Oct 21 '16 at 20:10
  
  
  

  
  
  
  

add a comment | 

Download and install the dbo.sp_WhoIsActive stored procedure from http://whoisactive.com

Run this procedure and it will tell you what is currently running. It will also tell you a host of further information such as what the current sessions are waiting on and where they originate from.

To answer the title of your question. To reduce the number of VLF, you need to shrink your log file using DBCC SHRINKFILE, then grow your log file back to 64GB in 16GB increments to give you 64 VLF.

share|improve this answer

edited Nov 12 '18 at 17:14

Aaron Bertrand♦

152k18289489

answered Oct 21 '16 at 9:50

Andy JonesAndy Jones

1,24148

share|improve this answer

edited Nov 12 '18 at 17:14

Aaron Bertrand♦

152k18289489

answered Oct 21 '16 at 9:50

Andy JonesAndy Jones

1,24148

edited Nov 12 '18 at 17:14

Aaron Bertrand♦

152k18289489

edited Nov 12 '18 at 17:14

Aaron Bertrand♦

152k18289489

answered Oct 21 '16 at 9:50

Andy JonesAndy Jones

1,24148

answered Oct 21 '16 at 9:50

Andy JonesAndy Jones

1,24148