SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

The Voice of the DBA

Change Your SQL Server Oil

One of the things I learned early in life was that some regular car maintenance goes a long way. I didn't need to be fanatical about checks or ensure that I did the every recommended maintenance item at every interval, but one thing I've always done is change my oil regularly. I could let brake fluid, or coolant, or even tires go a touch longer than I'd like if funds were tight, but changing the oil was critical. I've lived by that with most of my vehicles over the years, and they've tended to last a long time.
I read a post from Glenn Berry that reminded me of this recently. Glenn talks about the need to perform maintenance on your SQL Server instances in the form of regular patches. I think that's a good analogy, but certainly not complete. On top of these oil changes, you should think about more regular index maintenance, checking security and backups, monitoring space and more. Those are topics for another day, but no less important than patching.
So how often do you change your oil, or patch your database? For cars, we used to change every 3,000 miles or 3 months (I typically used the former). Over the years some cars have gone to 5,000 miles, though with more modern vehicles and better oil, many cars have gone to 7,500-10,000 mile intervals. My BMW says 15,000 miles, though I usually get nervous around 10k. No matter what your interval, it's good to have a value and stick to it. That prevents confusion, forgetfulness, or other human errors. Choose a distance or time and maintain your vehicle regularly.
For your SQL Server, I'd typically do the same thing. While Glenn likes to ensure he's got the latest patches for his customers, I'm not completely sold on applying patches as soon as they're released. There is a risk of things breaking, or even just service disruption, so I prefer to limit the issues. I prefer to let CU patches come out for a few months and see if any major issues are reported. I also likely would apply patches only 3-4 times a year, rather than the 6 times that the patches are released. In the absence of issues, I prefer stability. I do recognize that I'm taking a chance that I could run into an issue that requires patching to get support, or a security patch is released and needs to be installed quickly. In those situations, I'd need to allocate some testing time and deal with the potential issues at that time.
Along those same lines, I don't like updating all my instances at once. Certainly not all of a same version. If I have 20 SQL Server 2016 instances, I don't necessarily want to patch them all today. I might patch 2-3 to ensure that things work smoothly. If that's the case, then I'd schedule the rest next week. My experience with patching is that we usually need at least two groups, and sometimes three. I have an early and normal patch group, and sometimes a delayed one where the patch timing is problematic. However, I can't get too far out of date as it's easy to forget about patching older servers, and that can cause plenty of other issues in the future. In general, I prefer just two patch groups.
As Glenn mentions, there are other benefits to regularly patching. You touch these servers and have the chance to ensure that your DR/HA plans are up to date. You think about potential issues, and in general, don't become complacent with regards to the health of your systems. I certainly think you should apply SPs, and at least a few CUs a year to keep semi-current. And, of course, since you want to treat your instances like cattle, not pets, you also need to ensure you patch test and development systems to match their production counterparts.
Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

SQL Clone

NEW SQL Clone - version 1 now available!

Create copies of production databases and SQL backups in seconds and save up to 99% of disk space using SQL Clone. Redgate’s new tool removes much of the time and resource needed to create and manage database copies, allowing teams to work on local environments to develop, test and diagnose issues faster. Try it free.

Featured Contents


2016 Salary Survey in PowerBI

Christopher Huntley from SQLServerCentral.com

A PowerBI Presentation using the data from the 2016 Salary Survey More »


Introducing DevOps to Farm Credit Services of America

Additional Articles from SimpleTalk

Identifying a need for better collaboration between teams is only the first step to introducing DevOps workflows into your organization. You also need to define a common DevOps goal, a strategy to achieve it, and then to break down this strategy into series of manageable tasks for each team. Bob Walker describes how Farm Services of America started to "do DevOps". More »


From the SQLServerCentral Blogs - Not An Administrator For Analysis Services Tabular

Koen Verbeeck from SQLServerCentral Blogs

A couple of weeks back I installed SQL Server 2016 on my personal machine, including SSAS Tabular. Yesterday I created... More »


From the SQLServerCentral Blogs - SQL Clone and a Little Competition

Grant Fritchey from SQLServerCentral Blogs

Redgate has released a new product, SQL Clone. I’ve already talked about how excited I am about it. Now, it’s... More »

Question of the Day

Today's Question (by Steve Jones):

How do I start SQL Server with minimal configuration?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Administration.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.


Professional Microsoft SQL Server 2014 Integration Services

Master the fundamentals of Transact-SQL—and develop your own code for querying and modifying data in Microsoft SQL Server 2016. Led by a SQL Server expert, you’ll learn the concepts behind T-SQL querying and programming, and then apply your knowledge with exercises in each chapter. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I am worried that other DBAs in my organization are abusing trace flags. I want to determine if there are any session or global trace flags set. How can I do this? 

Answer: DBCC TRACESTATUS with no parameters shows all trace flags


DBCC TRACESTATUS with no parameters will show you all trace flags set on your system, either global or session level.

Ref: DBCC TRACESTATUS - click here

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

In-Memory tables and garbage collection of checkpoint files - We're just now setting up some in-memory tables to handle session state since our normal disk-based AspState database is encountering...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

How to modify Rank() function - Hello, I have troubles with this query: SELECT id, SUM(repayment) as suma, DATEADD(S, -1, DATEADD(mm,DATEDIFF (m,0,date+1,0)) AS obd RANK() OVER(PARTITION BY id,suma, ORDER BY DATEADD(S,...

Find out consecutive date difference by comparing with master table - Dear All, Please help me to sort out it I have reporttable where the fault report date and complete date is inserted...

Need Correct Index for slow query - This is the query which app uses and it takes 30 seconds. select * from tablename FOR XML AUTO, ELEMENTS Table is...

SQL Server 2014 : Administration - SQL Server 2014

Index Size Difference - Hi Experts, For testing I have created a table with a clustered index selecting all columns and inserted some data . The...

SQL Server 2012 : SQL 2012 - General

Replication topology doubt - Hi, We need to replicate 3 DBs like this, using sql transactional replication 2.way: Server=2 (active); Database=DBco <-> Server=1 (passive); Database=DBco Server=2 (active);...

SQL Server 2012 : SQL Server 2012 - T-SQL

how to use following xml query with out prefix - hi,    in following link  https://msdn.microsoft.com/en-us/library/ms175178.aspx i found  this cote "This prefix is then used in the query body instead of the namespace...

Reporting Services : SSRS 2016

How to get reports on reporting server - I have created reports using Visual Studio but I don't know how to get them on the Report Server?

This email has been sent to {user_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.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com