How to Fix Databases with High VLF Count (Database Weekly 2019-04-13)

 Database Weekly for April 13, 2019 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News Hand-picked content to sharpen your professional edge Editorial  The End of XP This week we have the end of Windows XP. At least, we have [the end of support](, which means the lifespan of the OS was over 17 years. That’s a long time. It’s longer than I’ve run any system without an upgrade. I have some Windows 7 systems I use at times, but that’s a mere 10 years old. I know there are still a few SQL 2000 systems out there, which is older than XP, and likely a few of them are running on Windows 2000 in a VM somewhere.  I have to admit that I liked Windows XP and thought it was a nice upgrade from previous versions. I ran it for a long time, skipping Vista, before moving to Windows 7 when it was released. Most of the early days of SQLServerCentral were run from an XP workstation, with SQL Server personal and lots of text editors helping me manage the site. I’m somewhat sad that it’s gone, though I think Win7, and now Win10 were very nice improvements.  XP isn’t likely gone, as some companies will continue to run it and I expect some ATMs, some kiosk displays, and other embedded applications will show that XP start screen on occasion. That’s not unlike the way that some of us might connect to an SQL instance and be surprised to see a single digit number in the major version space. In fact, a lot of you might still see a “9”, for SQL Server 2008. I was seeing that until last year when we moved SQLServerCentral to SQL Sever 2017. I expect the more and more organizations will be slowly moving on, perhaps reluctant to upgrade those old versions that work and don’t cost much to maintain.  Or do they? Is there a big cost on older systems that run and don’t see regular development work? My thought has been that for many internal systems, I need a database platform, and likely an OS, to run for 10 years or more. I know some of the larger enterprises have this view as well. Picking a platform is a major decision and it can be very hard to change directions. Porting takes a lot of resources, as do upgrades, so ensuring a system can handle a load for 10 years makes sense.  I know many of us would like to regularly switch versions, and one of the great things about my job is that I get to do so. I’ve held onto SQL 2014 and 2016, but I’m slowly moving all my work to SQL Server 2017 and 2019, with the idea that apart from some repro situations, I won’t try to run older versions anymore.  It seems many people are town about upgrades. Some people prefer the system they know and are comfortable with, since they know what workarounds are needed. They may bemoan the need to learn new skills and change old habits. Others prefer the latest and greatest, quickly adopting new platforms and agonizing over the spend on old systems. No matter which way you feel, it’s likely that you’ll be forced to do a bit of both as the pace of change for SQL Server means many of us will adopt new versions for new work and end up supporting 4 or 5 versions at any one time.  Steve Jones - SSC Editor [Join the debate, and respond to the editorial on the forums](  [Redgate Database Devops]( The Weekly News All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. Administration [SQL Server Diagnostic Information Queries for April 2019]( This month, I have just made some minor improvements to most of the query sets, mainly in the comments and documentation. I have been trying to fix the broken... [Database Recovery Monitoring with XE]( Database recovery (crash recovery) is a nerve wrenching situation under the wrong conditions. It can be as bad as a root canal and just as necessary to endure that... The... [How to Fix Databases with High VLF Count]( In our earlier posts on recovery, we’ve covered the importance of properly managing SQL Server Transaction Log file growth. This includes how to detect databases with a high VLF... Apache Spark [Improving Performance In Spark Using Partitions]( In this blog post we are going to show how to optimize your Spark job by partitioning the data correctly. To demonstrate this we are going to use the College Score Card public dataset, which has several key data points from colleges all around the United States. We will compute the average student fees by state with this dataset. [Ingesting Data From Files With Apache Spark, Part 1]( In this post, a data expert teaches us how to take in large data sets using Apache Spark. Artificial Intelligence (AI) [Can artificial intelligence learn fast enough to beat money launderers?]( Anti-money laundering is an important issue for governments and banks alike. The fight to prevent terrorist financing and profiting from crime means that banks and other financial institutions are... [How data quality improves artificial intelligence]( Jim Harris shares examples of how and why AI applications are dependent on high-quality data. The post How data quality improves artificial intelligence appeared first on SAS Blogs. [From ML to GAN to HAL, a peek behind the modern artificial intelligence curtain]( Today's AI is technically "weak"—but it's complex and can have a great societal impact. Azure [Introduction to Azure Database Migration Service – SQL On The Edge Episode 19]( Azure has a rich set of offerings in terms of database platform services. We have the full family of SQL Server-based databases (single, pool, managed instance, data warehouse), open... Azure Cosmos DB [Why Partitioning is very important in Azure CosmosDB]( In any Introduction level CosmosDB talk, Presenter will suggest you pay more attention to the Partitioning part of the talk. Microsoft wants you to create great solution by using Azure CosmosDB and there are a lot of resources out there for developers. The challenge in Azure CosmosDB is, you don't need a DBA in CosmosDB and developers may not pay attention to details like Partitioning when they create databases in Azure CosmosDB. Azure Data Lake [Processing Data Using Azure Data Lake, Azure Data Analytics, and Microsoft’s Big Data Language U-SQL]( Data analytics has become one of the powerful domains in the world of data science. An enormous amount of data is being generated by each organization in every sector.... Azure Data Studio [Learning Jupyter Notebooks]( I’m starting the process of learning how to use Jupyter Notebooks. Notebooks are documents that contain live code, commentary, results, pictures and more. Jupyter Notebooks are used for presentations,... Azure SQL database [Creating logins and users in Azure Database]( Azure Database is the PaaS solution for SQL Server databases, on a previous post we have discussed how to create one.On this post, I want to show you how... The... [Tuning Azure SQL Elastic Pools]( The below image is a beautiful picture, now it could be worse. The red line and the green line could peak at the same time and for a very... The... Backup [Google Announces 'Ice Cold Storage']( Google’s approach to ice cold storage eliminates the need for a separate retrieval process and provides immediate, low-latency access to content. Business Intelligence (BI) [Getting Started with Azure Databricks]( Brad Llewellyn has a tutorial for Azure Databricks: Databricks is a managed Spark framework, similar to what we saw with HDInsight in the previous post. The major difference between... Career [How the Company-Startup Thing Worked Out For Me, Year 7]( It’s time for my annual update on the wild ride. If you want to catch up, check out past posts in the Brent Ozar Unlimited tag. This post covers... Conferences and Events [SQL in the City – Brisbane, Christchurch, Melbourne – Hope to see you there]( I'm presenting a session on Azure DevOps for SQL Server DBAs that's designed as an intro for data people who haven't really worked with it before, at Red-Gate's SQL... [SQL in the City Summits – UK, US & Down Under]( Redgate are inviting senior data professionals to attend one of the upcoming SQL in the City Summit events taking place in April, May and June. If you’re interested in... [Data Geeks and Data Jocks Meetup in Vancouver, Washington]( Our little “Data Geeks” Meetup group in Vancouver, Washington is growing with a line-up of heavy-hitters and bigger crowds. This month, on April 16th, we are honored to welcome... [The Next Developing SSIS Data Flows with Labs Course Delivery is 17-18 Jun 2019]( I’m excited to announce the next delivery of Developing SSIS Data Flows with Labs will be 17-18 Jun 2019! This two-day course takes a hands-on approach to introduce SSIS Data... [The Next Mastering the SSIS Catalog Course Delivery is 11 Jun 2019]( I’m excited to announce the next delivery of Mastering the SSIS Catalog will be the morning of 11 Jun 2019! This half-day course brings you up to speed on... [Honored to Present Moving Data with Azure Data Factory at SQL Saturday Atlanta 18 May!]( I am honored to present Moving Data with Azure Data Factory at SQL Saturday 845 – Atlanta 18 May 2019! Abstract Azure Data Factory – ADF – is a... [Redgate SQL Monitor]( Data Analysis [Storytelling Without Data?]( There are many great resources out there for data visualization. Some of my favorite data viz people are Storytelling With Data (b|t), Alberto Cairo (b|t), and Andy Kirk (b|t).... The... Database Design [Designing Highly Scalable Database Architectures]( While architecting cloud native applications, you need to ensure that your system is highly available, performant, scalable, fault tolerant, and has the capability to recover from a disaster scenario.... Database Development [Easing the transition from shared to dedicated database development]( Working in a dedicated database model is the ideal. So, what does it take to move from shared development to a dedicated model? Infrastructure costs, merging multiple changes and maintaining an increased estate might be on your list of things to consider before committing to the switch. In this article we show how Redgate tools can ease the transition and new working approach. Database Performance [Finding Froid’s Limits: Testing Inlined User-Defined Functions]( This week, I’ve been writing about how SQL Server 2019’s bringing a few new features to mitigate parameter sniffing, but they’re more complex than they appear at first glance:... [Parameter Sniffing in SQL Server 2019: Adaptive Joins]( So far, I’ve talked about how adaptive memory grants both help and worsen parameter sniffing, and how the new air_quote_actual plans don’t accurately show what happened. But so far,... [Parameter Sniffing in SQL Server 2019: Air_Quote_Actual Plans]( My last post talked about how parameter sniffing caused 3 problems for a query, and how SQL Server 2019 fixes one of them – kinda – with adaptive memory... [Selects That Cause Writes]( Answer Time While answering a question on dba.se, I got to thinking about if there would be a good way… [Parameter Sniffing in SQL Server 2019: Adaptive Memory Grants]( This week, I’m demoing SQL Server 2019 features that I’m really excited about, and they all center around a theme we all know and love: parameter sniffing. If you... [Stop Looking At Wait Stats Without Looking At Uptime]( Economy Of Waits There’s a joke about two economists walking down the street. One of them asks the other how… [Scalar function inlining in SQL Server 2019]( This insanely cool new performance-related update is one of nicest features in SQL Server 2019, and certainly one I’ve been looking forward to for a long time. If you’ve... ETL/SSIS/Azure Data Factory [SSIS vs. Oracle GG]( This is the third in a series of product identifications between Oracle and Microsoft to assist those attempting to understand the similarities and differences between these essential parts of... General [Kubernetes in Action: What It Does (and Doesn't) Do]( Read on to get the most out of what Kubernetes can do--and ensure you don't assume it is going to handle a task that it can't support. [A quick look at the new Power BI Report Builder]( Patrick takes a quick look at Power BI Report Builder including how to get it. This report builder is optimized for the Power BI service. Patrick calls out a... [Solving Logistic Regression Problems with Python]( Hardik Jaroli shows how we can solve logistic regression problems using Python, using the Titanic data set as an example: We will be working with the Titanic Data Set... Hardware [Hands on with the 56-core Xeon Platinum 9200 CPU: Intel’s Biggest CPU Package Ever]( When Intel announced the new Cascade Lake family as officially launched last week, it added a new product segment to its portfolio – the ‘Application Processor’ or AP. These... High Availability (HA) [Configuring the distribution database in an Availability Group]( Starting with SQL Server 2017 CU6 and SQL Server 2016 SP2-CU3, you can now configure your distribution database as part of an Availability Group (AG). I found one spot... Microsoft [Microsoft’s Windows XP Finally Dead: Last Embedded Version Reaches EOL]( Microsoft this week ended support for the very last Windows XP-based operating system, essentially marking the end of the platform and an era. Being one of the longest living... Microsoft News [Now’s the time to get in or out of the Windows Insider Program]( In a few days, the fast ring will switch to a preview of the 2020 release. Power BI [What Is the XMLA Endpoint for Power BI and Why Should I Care?]( The news about XMLA endpoint connectivity to Power BI datasets is now all around the internet after the public preview announcement of that last week. However, I realized that... PowerPivot/PowerView/PowerQuery/PowerBI [The new Power BI App Workspace experience]( The new Power BI App Workspace experience GA'd in April of 2019. Adam takes you through what the new Power BI App Workspace is all about and what is... [Create a custom KPI Card in a Power BI report]( Do you want a customized KPI Card in Power BI? Patrick looks at how you could get a custom KPI Card in a Power BI report without using custom... [Copy workspace, Publish to Multiple workspaces, Download all PBIX files, and much more in Power BI Helper April Edition]( In RADACAD we do our best to improve user experience using Power BI every single day, and throughout that experience, we add more and more functions to Power BI... [Register Power BI Helper in Your Tenant]( This article explains the setup instructions to work with the Service tab of Power BI Helper. Download Power BI Helper from here. Power BI Helper is a third party... [Table.Buffer() Does Not Buffer Nested Values]( Here’s yet another entry in the list of useful things I learned from Ehren von Lehe on the Power Query MSDN forum: Table.Buffer() does not buffer nested table, record... Product News [Apache Flink 1.8.0 Released]( Aljoscha Krettek announces the general availablity of Apache Flink version 1.8.0: SQL pattern detection with user-defined functions and aggregations: The support of the MATCH_RECOGNIZE clause has been extended by... Product Reviews [SQL Clone for Unit Testing Databases]( Phil Factor demonstrates how to use SQL Clone to create 'disposable' SQL Server databases, for development and testing work. You can spin up a clone, use it to unit... [Monitoring your servers and databases: SCOM and SQL Monitor]( SCOM is is good at monitoring the status of your servers. SQL Monitor give you a detailed view of your SQL Server instances, and databases, right across your network,... [Hands-on: First public previews of Chromium-based Edge are now out]( It's out now for Windows 10, with other operating systems coming later. R Language [Forensic Accounting: Generating Sketchy (But Not Too Sketchy) Data]( This is part two in a series on applying forensic accounting techniques to SQL and R. Start with a Data Model My plan here is to create a model... Replication [The Table Is In Replication – Why Does SQL Server Replication Seem To Ignore It?]( There are occasions when Updates, Inserts, and Deletes on a replicated table do not replicate out to the Subscriber. You've verified that the table is listed in the Articles included in the Publication, and that there is at least one Subscription on the Publication. Reporting Services (SSRS) [Reporting Services Basics: Overview and Installation]( SQL Server Reporting Services has changed quite a bit since it was introduced in 2004. Despite new analytic services like Power BI, SSRS is still a popular tool for... SQL Server on Linux [Using local repositories with SQL Server on Linux]( In this post I want to discuss something I talked about with my colleagues previously relating to offline installations of SQL Server on Linux. Which is something that has been on my mind for a while now. [SQL Server on Linux – how does it actually work?]( (Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance... The... Security [Don’t trust the locals: investigating the prevalence of persistent client-side cross-site scripting in the wild]( Does your web application make use of local storage? If so, then like many developers you may well be making the assumption that when you read from local storage,... [Serious flaws leave WPA3 vulnerable to hacks that steal Wi-Fi passwords]( Next-gen standard was supposed to make password cracking a thing of the past. It won't. [TajMahal Spyware]( Kaspersky has released details about a sophisticated nation-state spyware it calls TajMahal: The TajMahal framework's 80 modules, Shulmin says, comprise not only the typical keylogging and screengrabbing features of... [Testing as another user–#SQLNewBlogger]( Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is one of those things I do... The... [How the Anonymous Artist Bansky Authenticates His or Her Work]( Interesting scheme: It all starts off with a fairly bog standard gallery style certificate. Details of the work, the authenticating agency, a bit of embossing and a large impressive... [Hey Secret Service: Don’t Plug Suspect USB Sticks into Random Computers]( I just noticed this bit from the incredibly weird story of the Chinese woman arrested at Mar-a-Lago: Secret Service agent Samuel Ivanovich, who interviewed Zhang on the day of... T-SQL [Why Rolling back inserts with Identity columns is a bad idea.]( It wasn’t until the other day that I realised what the behaviour was when rolling back a transaction with an insert on a table with an identity column, to... [The Difference Between SQL’s JOIN .. ON Clause and the Where Clause]( A question that is frequently occurring among my SQL training‘s participants is: What’s the difference between putting a predicate in the JOIN .. ON clause and the WHERE clause?... [Writing Crossword puzzles with a database: T-SQL Tuesday #113]( Todd Kleinhans (b/t) is host for T-SQL Tuesday this month and wants us to talk about how we use databases ... Continue reading The post Writing Crossword puzzles with a... [Data Type Precedence and Implicit Conversions]( SQL Server needs to make sure data types match when performing operations that involve multiple pieces of data. When the data types do not match, SQL Server has to... The... [Using JSON for matrices in SQL Server.]( From SQL Server 2017, it becomes more practical to use JSON arrays for representing and processing a matrix. SQL Server can read them, and update values in them but... The Lighter Side [The Porsche Panamera Turbo Sport Turismo—to hybrid or not to hybrid?]( The Panamera Turbo comes in two flavors, and the more powerful one is a hybrid. [T-SQL Tuesday #113: A year of marriage and boardgames]( This week’s T-SQL Tuesday is about where you use databases in your personal life. And I have a database I don’t use any more that’s a little happy and... [Making Databases Personal]( If you can use a database to create automation routines to help sustain your work life, you can certainly do the same to sustain your personal life. Databases are... The... [SpaceX likely to win NASA’s crew competition by months, for billions less]( What lessons can the space agency learn as it considers a lunar return? Tools [AutoRecover in SSMS]( Periodically there is a crash, power surge, or sudden reboot of your computer. Of course, you had SQL Server Management Studio (SSMS) open and you were working on something important. That seems to be the only time there is a crash/reboot. You can lose work that that was open in SSMS but has not saved. [Azure Data Studio Notebooks: GitHub Issues]( There are a couple of Azure Data Studio GitHub issues near and dear to my heart that I’d like to point out in the hopes that it’ll raise their... Virtualization, containers, and k8s [Building A Kubernetes Cluster For SQL Server 2019 Big Data Clusters Part 4: Persistent Storage Concepts]( I was originally going to cover storage in its entirety in a single blog post. However, as storage and Kubernetes is the cause of a tremendous amount of confusion... [Docker Platform Outlook: Anusha Ragunathan Talks Containers]( Docker software engineer Anusha Ragunathan talks about what the year ahead holds in store for the Docker platform and container technology. Webinars [How to ensure efficiency and effectiveness in distributed database development teams]( Wednesday April 17, 4PM-5PM BST / 10AM-11AM CDT - The many benefits of having globally, or even locally distributed teams do not come without a trade-off. So how can you ensure that everyone works efficiently and effectively in distributed teams? cloud computing [Amazon-Microsoft Face-Off Set for Pentagon's $10 Billion Cloud]( Amazon.com Inc. and Microsoft Corp. are the last competitors standing in the Pentagon’s winner-take-all competition for a $10 billion cloud services contract, the Defense Department said. The announcement eliminates... [Google Embraces Multi-Cloud Clusters that Can Run Anywhere … Even AWS]( Anthos is an open platform that lets you run an app anywhere, without needing to modify the code, on existing on-premises hardware or in the public cloud. data privacy [Thousands of Amazon Workers Are Listening to What You Tell Alexa]( Amazon.com Inc. employs thousands of people around the world to help improve the Alexa digital assistant powering its line of Echo speakers. The team listens to voice recordings captured in Echo... [Yahoo tries to settle 3-billion-account data breach with $118 million payout]( Verizon-owned Yahoo boosted offer after judge rejected first settlement. devops [Its time to accelerate the State of DevOps]( Redgate is joining forces with DevOps Research and Assessment (DORA), now part of Google Cloud, as a sponsor of the 2019 Accelerate State of DevOps Report, and we're excited... software development [Somebody forgot to upgrade: Flights delayed, cancelled by GPS rollover]( Date bug on unpatched Honeywell gear likely cause of 777, 787 flight cancellations. webinar [Easing the transition from shared to dedicated database development]( Wednesday May 8, 4PM-5PM BST / 10AM-11AM CDT - Discover how transitioning from shared database development to individual development environments is easier than ever thanks to database virtualisation and source control technology. [Test data provisioning for compliant database development]( Wednesday May 1, 4PM-5PM BST / 10AM-11AM CDT - In this webinar, you’ll learn how to implement a test data provisioning solution that accelerates your development cycles and meets the needs of data privacy regulations such as HIPAA, SOX and the GDPR.  [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -