THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Follow-up Answers for my Australia Classes

I was out of the country for the last two weeks of March, delivering classes in Brisbane and Sydney, which were organized by WardyIT.  It was a great visit and there were 24 terrific students!

As is sometimes (perhaps often?)  the case, there were questions posed that I couldn’t answer during class, so here are a couple of follow-up answers.

1. I brought up the fact that SQLS 2012 generates a warning message when there are ‘too many’ Virtual Log Files (VLFs) in a database. (It turns out the message will be generated when a database has more than 10,000 VLFs but the error says that the database has more than 1000 VLFs! Which is of course, technically true, but the message is a little misleading.)  A student reported hearing that the Best Practices Analyzer also had a warning about the number of VLFs, but didn’t know what that limit was, and asked if I knew. I didn’t then, but I do  now. A message will be generated by BPA when there are 1000 or more VLFs. The message is actually focused on the performance problems that can arise when there are two many VLFs. The boot message is more concerned with the impact on database restore, rollback, backup and recovery of too many VLFs.

2. When discussing locks, I briefly mention the BU lock that is acquired by request (with a hint) during bulk load operations, for the purpose of allowing multiple processes to load into the same table concurrently. Someone asked whether any SSIS components used the BU lock, and I thought it was probably true, but I since I don’t work with SSIS, I couldn’t comment any deeper. I was able to discover, thanks to SQL Server MVPs Paul White and Arthur Zubarev that it definitely is possible for both SQL Destinations and OLE DB Destinations. The destination table must be a heap, and you can specify TABLOCK as an option for the FastLoadOptions property, as shown:


3. I very briefly mention Extended Events on the very last day, and mention that in SQL Server 2008 one of the drawbacks is that there is no GUI option to set up an XEvents session, but that has changed in SQL Server 2012. I was asked if the 2012 Management Studio could connect to a SQL Server 2008 instance and set up an XEvents session on that instance. My friend and fellow MVP Ben Miller was able to verify that when connecting from the SQL 2012 GUI to a 2008 instance, the option for creating an XEvents session does not even show up.  You could try scripting a session created for a 2012 instance and running it on a 2008 instance, but there are no guarantees.

So now I’ll get back to working on my new book …


Published Wednesday, April 4, 2012 4:56 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Michael said:

Hey Kalen, There is a wonderful extended event SSMS Addin on codeplex that supports SQL Server 2008 >

April 16, 2012 12:13 PM

Kalen Delaney said:

Awesome news, thanks for letting me know, Michael!


April 16, 2012 1:54 PM

obat diabetes said:

August 1, 2018 11:20 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:50 PM

obat sinusitis said:

August 10, 2018 7:23 PM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:59 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:58 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:22 AM

obat benjolan di tubuh said:

August 30, 2018 7:41 PM

obat wasir said:

September 4, 2018 8:27 PM

obat katarak said:

September 16, 2018 5:47 PM

obat limfoma said:


September 23, 2018 6:51 PM

Can anyone give me advice on writing a literature review? said: is the leading brand in Australia for assignment help that offers complete University assignment help services like contract law assignment, it assignment help, financial assignment help, project management assignment, literature review assignment help electrical engineering help and more.

October 4, 2018 3:20 AM

Here you can find best assignment help in Adelaide? said:

Where most students lack time, subject-knowledge, and writing skills, Sample Assignment brings them highly qualified subject experts who can write their assignments for them at affordable rates or for those who looking for assignment help adelaide. We have three teams who fully understand student problems and try to help them get over those problems.

October 4, 2018 3:22 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement