Are you sure? Last good DBCC CHECKDB over 2 weeks old.

So this past week I had a little confusion over a new CheckID that was suddenly firing every day in sp_Blitz. This new CheckId was number 68, ‘Last good DBCC CHECKDB over 2 weeks old.’ What? How could that be? I run it daily from Ola’s maintenance plans.

My first check was to verify if it was a recent occurrence only:

SELECT *
 FROM dbo.BlitzResults
 WHERE
 CheckID = 68;

Yup, in my history of BlitzResults it had only happened in the past day. So I kept keep looking since this was something new and weird.

Next check … were there errors in the Agent log for the dbcc script? No, nothing there, even though the agent truncates the script output anyway making that log fairly useless.

Next, check Ola’s log table. Clear. There were no errors in the maintenance plan logs for any DBCC_CHECKDB runs for the unit_test_1 database over it’s entire history.

SELECT *
 FROM [dbo].[CommandLog]
 WHERE
 CommandType = 'DBCC_CHECKDB' AND
 DatabaseName = 'unit_test_1';

Something was fishy, but was it a red herring?

red herring   https://www.flickr.com/photos/24263322@N05/2403978660

Continue reading “Are you sure? Last good DBCC CHECKDB over 2 weeks old.”

The Silence of the Plan Cache Purge

I’m going to have to blog very quietly tonight, since I don’t want the plan cache to hear me. This is a summary of a very silent plan cache purge on a production SQL Server that was whispering to us that it needed a new RAM configuration.

This purge was happening on an old SQL Server 2012 instance that I inherited from a contractor. It didn’t have all the settings that I put in my builds, especially for memory. On my new builds I follow Glenn Berry’s formulas for setting ‘max server memory (MB)’.  see here (for general formula)  or also here (see step 26.g)

Silent Rumors!

A colleague and I first discovered the silent purge when talking very quietly about a stored procedure that was just run three weeks prior and only runs once a year. We were were talking very quietly since there were rumors floating around. Other coworkers were whispering that someone may have run the annual procedure more than once … scandalous! The first place I went to look for proof of the rumor was at the last_execution_time in sys.dm_exec_procedure_stats. You can imagine my horror when I found out that the plan cache had silently been purged the day before. I wanted to scream, but the plan cache may have heard me if I had done that.

Why the Silence?

Continue reading “The Silence of the Plan Cache Purge”

Automating another SQL Server auditing script.

Introduction.

In today’s blog post I’m leveraging the work of another SQL Server MVP, Jason Brimhall. This summer Jason contributed a blog post to the July session of TSQL Tuesday. TSQL Tuesday is “a monthly blog party on the second Tuesday of each month”

::fn_trace_gettable

In Jason’s July blog, http://jasonbrimhall.info/2018/07/10/just-cant-cut-that-cord/, he presented an elegant script for detecting audit events from the default trace log. This fit in perfectly with the auditing theme that’s preoccupied much of my time this summer. So, I set out to automate it in Powershell so that I could drive the automation from any server using my typical Powershell coding approach.

This turned out to be pretty easy…

stored procedure wrapper

Continue reading “Automating another SQL Server auditing script.”

Automation of sp_Blitz with a PowerShell wrapper 

Introduction to sp_Blitz

This summer I’ve had several pressing reasons for automating Brent Ozar’s sp_Blitz, a popular SQL Server database health check script that is part of the BrentOzarULTD/SQL-Server-First-Responder-Kit.  The sp_Blitz script creates a prioritized list of issues for dba research and attention.  My main reason was for auditing, but the script checks many areas of concern.

The ISACA

One of my reasons for automating is to help with the ISACA www.isaca.org database audit assurance program.  Many of the questions tie directly to areas that sp_Blitz is checking.
For example, ISACA question number 3.1.1.2, can be answered with this query:

Continue reading “Automation of sp_Blitz with a PowerShell wrapper “

JMeter, SQL Server and Thread Safety.

It’s been a busy summer and I’ve missed making a lot of posts. This post is about an interesting event from June — interesting from a technical point of view, but also because it highlights a testing trap that I fall into repeatedly, which is reporting errors from tests that are slightly out of scale.

Using Apache JMeter for SQL Server concurrency testing.

I’ve used Apache JMeter in the past for stress testing J2EE web apps. Vendors don’t like me very much when I tell them we’re going to run a stress test on their apps. Oh, and if your network group is paranoid you may need to reassure them that you have mission critical testing requirements before you start using JMeter. This time around I’m using it as a shell around a SQL script for calling a stored procedure that I strongly suspected of concurrency issues.

Continue reading “JMeter, SQL Server and Thread Safety.”

A Google Home Mini finds life as a Hotel Concierge.

Today’s post doesn’t have a lot to do with databases. But it relates to a recent SQL Saturday. What follows is the summary of a two week adventure of training a Google Home Mini to become a hotel concierge.

SQLSaturday

I was lucky to receive a winning ticket at the raffle at the recent Albuquerque SQL Saturday on May 5th. Jamey Johnston stepped up like many other volunteers and presenters to donate a Google Home Mini[1] for the raffle. Ironically I had just been pricing them this month so I could give one to a hotel where I moonlight. I missed Jamey’s session but I heard from a friend that he gave an amazing presentation.

The goal.

So, six of us work the front desk during the week at the hotel where I moonlight. Hospitality happens to be a good side gig in Santa Fe since the only work in town centers around government and tourism, there’s not much tech work here. Hotel work is good because every day seems different and I enjoy the challege of helping guest’s love their stay in Santa Fe and sharing people’s travel stories with the same passion that I enjoy my own travels.

At the front desk we are asked many questions while multitasking with the telephone and the reservation systems. Many of the questions we answer follow a repeated theme: “How far is it to Denver?” “The Grand Canyon?” “Phoenix?” “How long will that take?” “What is the phone number for Tecolote?” “Enterprise Car Rental?” To be able to answer these questions simultaneously as we’re clicking our way through the reservation systems is priceless.

With this goal in mind I set out over four weekend shifts to train the little gadget for concierge duties.

Continue reading “A Google Home Mini finds life as a Hotel Concierge.”

Creating a Paessler PRTG sensor for SQL Server Agent Job monitoring

This week it was time to integrate SQL Server job failure detection with our SNMP server across all our SQL Server instances. PRTG Network Monitor is an SNMP server by Paessler. It is comparable to HP Openview, Cacti, Spiceworks, Solarwinds, Datadog and others. I really love this tool, even though it wakes me up in the middle of the night 😉 A Cisco network engineer at ANM, https://anm.com/, installed it and trained us. Their NOC in Albuquerque used it to support servers and workstations for their customers. The typical use case for SNMP is for switches and routers. I’ve come to find it priceless for multi-tier application troubleshooting, creating a single pane of glass for locating issues in our enterprise. I’ll outline how to add this sensor. The end result looks like this:

Continue reading “Creating a Paessler PRTG sensor for SQL Server Agent Job monitoring”

SQL Server 2017 CU6 … I shall not fear planned obsolescence.

This past week Microsoft released SQL Server 2017 CU6, Microsoft Support. So, I thought it would be a good idea to set up my Linux SQL Server to follow the same automated WSUS-driven patch methodology as we do on some of our Windows SQL Server installations. Some of these servers apply Microsoft updates on a weekly scheduled maintenance window on Sundays. For these servers it’s a little extra effort to closely monitor them through the maintenance window, and makes me nervous about Monday morning phone calls. However, it’s a good feeling to know these databases are running the latest patches, and Microsoft recommends “ongoing, proactive installation of CUs as they become available.”

My first Linux version of this scheduled maintenance window for SQL Server is just a cron job running apt-get commands. I’m going to follow up with my Linux colleagues on other patch maintenance approaches and will come back to edit this post with any better ideas, or likewise I’ll edit the post if I’m missing something important.

Patching SQL Server on Linux is super simple to do because the apt-get repository for Microsoft was added during the original installation Quickstart: Install SQL Server and create a database on Ubuntu. It’s literally just two simple apt-get commands:

sudo apt-get update
sudo apt-get install mssql-server

To repeat this in the weekly Sunday maintenance window I’m just using this cron job:

#
# m h dom mon dow command
0 13 * * sun /usr/local/scripts/sun-maint-win-sqlserver.sh

The bash script looks like this: Continue reading “SQL Server 2017 CU6 … I shall not fear planned obsolescence.”

Presentation: Three SQL Server Always Encrypted Findings.

On Friday I gave a presentation at the April meeting of the newly created Santa Fe SQL Server User Group santafesql.org.

I was a little nervous since I haven’t given a presentation since a DOE conference in Amarillo, TX in 1991. But in spite of my nerves, it went OK since it was a short 30 minute talk and since we had a great group of attendees. The group was patient with a couple technical difficulties and interected well when I lost focus doing things like shifting back from slides to demos.

My three findings were amazement, horror, and satisfaction.

The first finding, amazement, was the subject of my last blog post. SQL Server’s ability to flawlessly upgrade an Always Encrypted database over an upgrade and a platform shift to Linux is amazing. I covered it more thouroughly in the blog post than I did in the presentation Taking SQL Server Always Encrypted on a road trip.

I had some pretty serious technical issues in my presentation.  Continue reading “Presentation: Three SQL Server Always Encrypted Findings.”