Identifying Deprecated Feature Usage (Part 2)

Dave Mason - SQL Server Deprecation

In my previous post, I took a stab at monitoring deprecation events for SQL Server. It didn't go so well. A deprecation event occurred more than 5,000 times in a very short period of time, and I got one email for every occurrence. Not good. Here's what I kept seeing over and over:


Identifying Deprecated Feature Usage (Part 1)

Dave Mason - SQL Server Deprecation

I've wanted to do some Event Notification testing for SQL Server deprecation events for quite some time. The thought process here is that I could send myself an alert to identify usage of SQL Server features that will be removed from the next major version (or future version) of SQL Server. I could then forward this info to development and let them take action...or not (I kid, I kid). Today is the day I finally got around to the testing. I didn't get very far, though.


STOPAT And Date Formats


I recently had the need to restore a database with recovery to a point in time. I've only done this a few times, so I had to look up the syntax for stopping the last and final transaction log restore. Microsoft Docs shows us this "Basic Transact-SQL syntax":


There's nothing I see in the documentation regarding the format for "time". But there are a couple of examples, including this one:


Handling SQL Server Deadlocks With Event Notifications

Dave Mason - Event Notifications - SQL Server Deadlock

I've seen a few different methods for handling deadlocks in SQL Server. I don't notice the use of event notifications very often, so I thought I'd share my approach. For my purposes, I want SQL to notify me when a deadlock occurs. I also want an easily accessible deadlock graph that I can inspect and potentially pass along to development or software vendors.


Undocumented T-SQL Commands

I recently encountered code similar to this in production:

IF (NOT IS_MEMBER('db_accessadmin') = 1) AND
 (NOT IS_MEMBER('db_owner') = 1)
 DBCC AUDITEVENT (109, 1, 0, N'<loginName>', N'<dbUserName>', NULL, NULL, NULL, NULL, NULL)


Database Corruption: Incorrect PFS Free Space

Dave Mason - SQL Server - DBCC CHECKDB

I've been lucky with database corruption during my career. I could probably count on one hand the number of times I've had to deal with it. A couple times, it was in a customer's environment--they managed it themselves, but called me in to help. The other incidents were ones I inherited from a backup I had to restore into a production environment. The first time it happened to me, I didn't realize it until days later when DBCC CHECKDB ran during a weekend maintenance window. After that, I added a new "rule" to my list: always run DBCC CHECKDB after restoring a database from someone else. That rule paid dividends today.


The Ambiguity Of Acronyms

Today I feel the need to blog my thoughts about something I've realized for a long time: we suck at acronyms. And by "we", I mean journalists, bloggers, and other online content providers. In particular, in the online realm, this is a needless problem that is easily remedied. More on this in a bit.


SQL Server Extended Event Handling via GUI

Dave Mason - SQL Server Event Handling: Extended Events

In some previous posts, I looked at handling Extended Events by accessing the event stream programatically. One example was a C# console app, the other was a PowerShell script. While I like both options, sometimes you just want a GUI. So I decided to create one.


Flaming Hair And The Apocalypse

Dave Mason SQL Server - Flaming Hair And The Apocalypse

If you're a new employee in the IT world, or looking to join it, let me tell you about a scenario that is bound to happen to you. There will be times when you're approached by someone in a frenzy, their hair seemingly on fire. They'll go on to insist the world will come to an end if you don't handle some task for them immediately. As a fan of your own existence, you'll decide to help out. Far be it for you to cause the onset of the apocalypse and doom mankind. You'll drop whatever it is you're currently working on, complete the task within the mandated time frame, and maybe get a "thanks" for your efforts.


Installing Multiple Instances of SSRS

Dave Mason SQL Server SSRS

Have you ever needed to install multiple instances of SSRS, with each instance "connected" to the same instance of the SQL Server database engine? (By "connected", I mean that the pair of [ReportServer] databases for each SSRS instance would all reside on the same instance of SQL Server. And each SSRS instance would be reporting on data from one or more databases that also resided on the same instance of SQL Server.)

To my surprise, I don't see much guidance for this scenario on the internet. TechNet has an article. It's consistently one of the first search results I get back for variations of "Install multiple instances of SSRS". That article (and a few others) omit a simple installation step/requirement that was a blind spot for me. (More on that towards the end.) I finally figured out what I was doing wrong and eventually succeeded with my task. Let's walk through the steps.