Press "Enter" to skip to content

Category: Administration

Using the Dedicated Admin Connection

Published 2021-11-08 by Kevin Feasel

Reitse Eskens has the key for the back door:

I got a call this morning from a coworker. One of the database instances was unreachable with the message that the TempDB log file was full. No processes could login and the only way to get things going again might be to restart either server or instance. Restarting the instance should be the last resort, because after a restart SQL Server will plough through all the logfiles to find transactions to either roll forward or roll back. That will take more time that you want. But, we had the famous DAC backdoor installed.

Read on to see how the DAC can save the day.

Comments closed

SQL Assessment for SQL Server on VMs

Published 2021-11-03 by Kevin Feasel

Ebru Ersan announces a new preview:

Wouldn’t it be great if there was a way to learn if your SQL Server on Azure Virtual Machines was configured optimally? Do you have the right options set? Do you have your tempdb on the right disk? Can your queries perform better? All these and more can be answered using the new Azure portal experience on the SQL virtual machine resource page. SQL Assessment feature, once enabled, will evaluate your SQL Server on Azure VM against configuration best practices to determine if your system is healthy and setup for success. This feature is currently in preview. We would love to hear your feedback.

Click through to see it in action.

Comments closed

Data Source Name Not Found with Postgres Driver

Published 2021-11-01 by Kevin Feasel

Rayis Imayev troubleshoots a problem:

A very short blog post, just a reminder to myself, but if you have ever tried to connect to a PostgreSQL database using ODBC interface (I know, it already sounds like a very interesting challenge :- ), then you might have experienced this error message: “ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.”

Read on to see the cause of and solution to this problem.

Comments closed

Connection Leaks with MARS

Published 2021-10-27 by Kevin Feasel

Josh Darnell warns that, if you go to MARS, a doctor will warn you that you have a schizoid embolism and it will be up to you to determine whether the doctor is lying or not:

I recently looked at a SQL Server instance that had a large number of MARS connections under a single “parent” connection. Most of these “child” connections had been idle for quite a while, but they were still hanging around.

Read the whole thing. Because I’ve used MARS so little, I’ll instead add a follow-up point to my Total Recall reference above. In the commentary track for the movie, director Paul Verhoeven notes that Douglas Quaid actually did die and that it really was just a fantasy concocted in his mind and he really did die at the end. You can tell because, instead of a fade to black like normal movies, he fades to white, indicating that this wasn’t a proper ending. But then again, considering the follow-on media which happened (and was slated to happen but didn’t make it to the finish line), I don’t think the studios would have let Verhoeven keep his unhappy ending.

Comments closed

All about Synchronous Stats Updates

Published 2021-10-26 by Kevin Feasel

Paul Randal shares some thoughts about synchronous stats updates:

The SQL Server query optimizer makes use of statistics during query compilation to help determine the optimal query plan. By default, if the optimizer notices a statistic is out-of-date because of too many changes to a table, it will update the statistic immediately before query compilation can continue (only the statistics it needs, not all the statistics for the table).

Note that “too many” is non-specific because it varies by version and whether trace flag 2371 is enabled – see the AUTO_UPDATE_STATISTICS section of this page for details.

Read on to learn more, including the problems that synchronous stats updates can cause, what you can do to avoid them, and ways you can tell that synchronous stats updates are a problem in your environment.

Comments closed

Enabling Statistics Auto-Creation

Published 2021-10-25 by Kevin Feasel

Chad Callihan checks the stats:

When we query for data, we don’t always think about the magic that goes into efficiently returning results. One vital piece to this magic is statistics. Statistics in SQL Server are histograms that are used by the query optimizer to determine an optimal execution plan when executing a query. Let’s take a look at the different ways to check your statistics settings and make sure statistics are being automatically created.

Click through to see how.

Comments closed

Four DBA ToDos in a New Role

Published 2021-10-25 by Kevin Feasel

Lee Markum has a starting point for DBAs in a new role:

You’ve just been hired into a DBA role at a new company, or you’ve been given the DBA keys at your current company. Maybe you’re a SysAdmin and your boss has informed you that you are now supposed to manage the SQL Servers as well as everything else on your plate. In any of these situations, you may have some confidence in your skills, but especially in the case of being a new hire, you have absolutely no true idea of what you’re walking into.

In these scenarios, where do you start? Start with these four areas.

Click through for the four areas. I completely agree with Lee on these for DBAs, including the order.

Comments closed