Ygthkb

How can I automatically launch GPSD on startup?

Is Screenshot Time-tracking Common?

How can I prevent an oracle who can see into the past from knowing everything that has happened?

Why might frozen potatoes require a hechsher?

How to not let the Identify spell spoil everything?

Coworker asking me to not bring cakes due to self control issue. What should I do?

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

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

Count repetitions of an array

Who is credited for the syntax tree in synthetic linguistics

Why is "rm -r" unable to delete this folder?

Sensor logger for Raspberry Pi in a stratospheric probe

How much light is too much?

Growth of Mordell-Weil Rank of Elliptic Curves over Field Extensions

When using Volatility with a memory image, what is the Kernel version?

How can find the 2D Voronoi cell area distribution?

How many proficiencies and languages does a noble half-elf Knowledge Domain cleric start with?

Where does documentation like business and software requirement spec docs fit in an agile project?

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

How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?

Piano music notation conventions

Can I travel from country A to country B to country C without going back to country A?

Writing dialogues for characters whose first language is not English

Modern Algebraic Geometry and Analytic Number Theory

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♦ 6 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♦ 6 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