Press "Enter" to skip to content

Curated SQL Posts

Managing SQL Server Error Logs

Published 2016-01-27 by Kevin Feasel

Mike Fal shows us how to mange SQL Server error logs using Powershell:

Reading SQL Server error logs is pretty simple, but requires us to make use of the SMO objects and the .ReadErrorLog() method to get the data from the log. Let’s start by simply reading the SQL Server error log and see what the output looks like (I’ll be using the SQL Server provider for this)

The big benefit, as Mike points out, is that the log rows are considered objects, meaning you don’t need to try to parse out things like the log date.

Comments closed

Fixing Partition Table Boundary Points

Published 2016-01-27 by Kevin Feasel

Kendra Little shows us how to fix a common partitioning issue:

Everything was fine, but we forgot to put a boundary point in for Jan 1, 2016… and now it’s well into January. All our data for January 2016 is in the partition with the December data.

The more time passes, the more data is going to go into that partition, and it’ll get bigger and more lopsided. If we’re switching out old partitions by month, eventually that’s not going to work. And partition elimination won’t work for anything after Dec 1, 2015, either!

Be sure to keep those empty partitions around, just in case .

Comments closed

The SSAS Flight Recorder

Published 2016-01-26 by Kevin Feasel

Bill Anton digs into the SSAS Flight Recorder:

During a recent engagement involving an Analysis Services health assessment, one of the things I noted was that the Flight Recorder was enabled. During the post-assessment review with the client, where we walked through the report and recommendations, I quickly explained what the Flight Recorder was, why it should be disabled (answer: never used, wasted resources, etc), and that disabling it would not produce a noticeable performance gain – the recommendation was mostly out of habit.

Bill’s answer is to build custom performance monitoring.

Comments closed

SQL Agent Schedules

Published 2016-01-26 by Kevin Feasel

Kenneth Fisher talks SQL Server Agent schedules:

Because you can re-use the schedules you want to be careful naming them. I’ve seen far to many schedules named Schedule 1 or Job Name Schedule. The first is non-descriptive and the second too specific (it would look silly as the schedule for another job with a different name). Give your schedules names like Every Weekday at 4AM or The third of the month at 6AM. This way when you go to pick the right schedule you know which one is which.

Word of warning (which Kenneth also notes):  be careful about modifying those schedules; if you think you’re editing the schedule for just one job, you might actually be modifying schedules for a bunch of jobs.

Comments closed

Database Snapshots

Published 2016-01-26 by Kevin Feasel

Steve Jones explains database snapshots:

I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.

However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick “create database xx” syntax I often use, with a snapshot I need to be more specific.

Word of warning:  don’t have more than one active snapshot of a single database.  If you do, you’ll likely have major performance problems.  My favorite use case for snapshots was building some semi-automated integration tests a few years back.  I created a tool for devs to create snapshots, and then they could run all the tests they wanted and revert the snapshot afterward.  There are some good uses in production environments as well.

Comments closed

Reusable Powershell Scripts

Published 2016-01-26 by Kevin Feasel

Laerte Junior has an article on writing re-usable Powershell scripts:

There are the rules that I use in my day-to-day work in PowerShell. They’ve worked well for me over the years, but I’m not saying that they are carved in stone.

  • Learn the languageYou need to treat PowerShell as a serious .NET language. Like C#, F# or VB, it is impossible to know what the language can do, such as its features or commands without understanding the language and its paradigms? You can cut and splice other people’s scripts but you must have a good feel for the way that the language works before you can proceed any further

  • Use The HelpFor PowerShell, the help system is the first thing you must reach for: it must be your best friend. The designers of the language intended it to be well-used.

  • Use the  PowerShell CommunityThe PowerShell community is unique, because it has people who have come from a wide range of IT backgrounds. They bring their experience and wisdom with them. They will know more than you.  The combination of skills multiplies the speed at which the language develops. Read their posts, download their script and learn from them.

  •  Keep It Simple

  • Do not use Aliases, except for deliberate obfuscation.

  •  Write with considerationDo not try to cram all your scripted process into one line.  In the Shared and Corporate environment other people will maintain your code and will not necessarily have the same PowerShell knowledge as you. Be kind in your code.

After reading his article, check out Carlos Perez, et al’s Powershell Best Practices and Style Guide.

Comments closed

Monitoring Deadlocks

Published 2016-01-26 by Kevin Feasel

Michael J. Swart has an Extended Event and a query to help monitor deadlocks:

Guess what? Apparently I “reinvented the wheel”. The extended events session I created is equivalent to one that Jeremiah Peschka wrote two years ago in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. The embarrassing thing is that in Jeremiah’s article, he references a tool I wrote. And the first comment was written by yours truly.

There are a bunch of ways to capture deadlock information.  This is a good one.

Comments closed

Named Constraints

Published 2016-01-26 by Kevin Feasel

Louis Davidson on naming constraints:

It has long been a habit that I name my constraints, and even if it wasn’t useful for database comparisons, it just helps me to see the database structure all that much eaiser. The fact that I as I get more experience writing SQL and about SQL, I have grown to habitually format my code a certain way makes it all the more interesting to me that I had never come across this scenario to not name constraints.

Temp tables are special.  There’s another reason to have non-named constraints on temp tables inside stored procedures:  it allows for temp table reuse, as shown on slide 21 in this Eddie Wuerch slide deck from SQL Saturday 75 (incidentally, the first SQL Saturday I ever attended).

Comments closed

De-Duplicating Delimited Lists

Published 2016-01-25 by Kevin Feasel

Phil Factor looks at de-duplicating lists:

So there you have it. With XML tricks and window functions, we have more opportunity for kicking out any need for functions. To use this code, you’d just swap out the select statement that supplied my samples to the routine, for the lists that you want to deduplicate. Sure, this sort of job will never be quick because there are still correlated subqueries in there to upset the CPU! I am intrigued that there are such different ways of doing a solution for this task in SQL server. Are there yet other ways of doing it?

Cf. Aaron Bertrand’s tally table method.  Bonus points if you’re mentally screaming “CLR!”

Comments closed

Azure Blob Storage Sync Updated

Published 2016-01-25 by Kevin Feasel

Randolph West reports on some bug fixes to Azure Blob Storage Sync:

During a SQL Server migration this month, I found some inconsistencies in my Azure Blob Storage Sync tool, so I made several improvements, and fixed an outstanding bug.

As you know, it relies on the naming convention provided in Ola Hallengren’s Maintenance Solution and comes in two parts: the AzureBlobStorageSync command-line application, and the AzureBlobStorageRestore command-line application.

Using Azure blob storage (or S3 if you go the Amazon way) as a long-term storage mechanism for database backups is a pretty smart idea.

Comments closed