Voice of the DBA

Tag: administration

  • Differences Between xp_readerrolog and sp_readerrorlog: #SQLNewBlogger

    I was creating a question on sp_readerrorlog and realized that this procedure is different from the one it wraps: xp_readerrorlog. This post digs into a few differences.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    The New System Stored Procedure

    For most of my SQL Server career, it’s been a habit to use xp_readerrorlog to query the log. This was code I learned a lot time ago, and it’s worked in every version of SQL Server. Despite being undocumented, as you can see below:

    2026-08_0381

    xp_readerrorlog has been written up by a few people at SQL Server Central, including Nagaraj Venkatesan and Ken Fisher, as well as in some forum posts (one, two). However, it’s not well documented. I usually end up Googling for the parameters as I need them.

    Until now.

    Here are a few differences I’m documenting, so I will hopefully remember them.

    Note: I got a few different results from AI, which were at best incomplete, and sometimes wrong.

    sp_readerrorlog has different permissions. This proc works with anyone that has VIEW SERVER STATE, which is sysadmin, serveradmin, and security admin.  Or with the permission granted.

    xp_readerrlog has extra parameters. Both of them have these parameters:

    1. error log number (0 based)
    2. error log type, 1 – SQL Server, 2 Agent
    3. search value – needs to be NVARCHAR
    4. second search value, NVARCHAR as well

    xp_errorlog adds 5 and 6, and 7.

    1. start time (datetime)
    2. end time (datetime)
    3. sort order (ASC,DESC)

    Those are the main differences I see, and if you want to filter or sort, you need the extended stored procedure, not the wrapper.

    I also learned to be careful of AI, as some of the data wasn’t correct from Google or Claude, so I need to verify what I get back and test how things work.

    SQL New Blogger

    I was investigating something and noticed a difference. This post was about 15 minutes to write, along with some testing of the parameters to verify what worked and what didn’t. That testing will make future posts quicker, as I’ll reuse some code.

    This is a quick example of showing some knowledge, and including the warnings about AI. You can write this post and give someone confidence you’re a good choice for the next person to manage their database servers.

  • The Cost of Multiple Platforms

    I ran into an interesting post that noted the modern data platform can have a bunch of different systems underlying it. The example might be that your “software” could use PostgreSQL, MongoDB, Cassandra, Clickhouse, and something NewSQL (Spanner, CockroachDB, etc,). Some of you might think that’s not reality, but keep in mind that for a lot of your organizations, the “software” is what the customer uses. I’m sure my bank has multiple systems behind the mobile app I use to pay for things, move money, check balances, etc. I would guess there is some DB/2, SQL Server, and some analytics or No/NewSQL stuff in there. Hidden as different applications that make up the “app”, but they are still in there from my perspective as a user of the app.

    No one intentionally designs software like this, but we still see it. They might not even have 5 or 6 platforms in their organization, but almost none of the customers I work with have less than 3. Somehow, somewhere, someone added a PostgreSQL server to an Oracle/SQL Server environment. MongoDB crept in when someone thought it was a better store than DB/2 or MySQL. An article written about how Facebook or Spotify or some other high tech company used Cassanda or Snowflake inspired a developer to add that to their toolbelt and build it into their application.

    And the ease with which the cloud makes experimentation quick and cheap causes a spread of your database estate.

    There’s a cost to having all these systems. Either an organization hires separate sets of experts to manage Ops, or they try and train lots of individuals to run multiple systems. It can be done, but those lightly trained people who have to focus on remembering the differences between SQL Server, PostgreSQL, and MongoDB will work slower. They’ll solve problems with a higher MTTR. Even if they have a single pain of glass, like Redgate Monitor, they still won’t be as effective as the number of platforms grow. It’s just human nature. And if you hire separate teams, that’s a cost as well.

    Heck, I’m a pretty good DBA and a good volleyball coach, but I get confused. When I got voluntold to manage DB/2 systems in addition to SQL Server, I got less done every day. When I coached two volleyball teams at the same time, I was less effective with each. It’s hard to keep focused on multiple similar things. Add in the complexity of not only separate paradigms, but different ways to manage things in the cloud or on-premises and the operational cost is high.

    I’m not sure it exceeds any licensing cost or the cost of limiting what platforms developers can use. I would argue only allowing 1-2 platforms just makes you more efficient and your staff more effective.

    However.

    That’s not the world. Even if I mandated that, often some external even changes my world. Companies get bought and integrated. New COTS software is needed, and it will, of course, run only on a database platform we don’t run.

    There’s a serious operational cost to adding new platforms that few consider. Even if they did, I’m not sure anything would change.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Never is Not the Policy

    Years ago I worked with a few developers and DBAs that were temp-table happy. As in they defaulted to using temp tables everywhere. This was in SQL Server 6.5, and tempdb was an issue with contention, sizing, and performance. I rewrote so many queries to remove temp tables for our clients that I banned them. I told other developers they could never use temp tables in their SQL code. They, of course, would try to submit code with temp tables in our VCS (Visual SourceSafe at the time), but an early, pre-automated CI/CD would notify me and I’d have the developer rewrite their code.

    There were situations that didn’t perform well with a single query, and we did allow some temp tables. The point wasn’t the ban them entirely, but stop them from being a crutch for developers or a first choice. I wanted them to think about the problem first and try to solve it with SQL. If performance was an issue, then we’d look at a temp table.

    Recently, I saw a post from Jeff Iannucci that noted there are settings you should never change. These were database-level or instance-level settings that he suggests you leave the defaults, or leave a specific setting chosen. An example of the latter is Page_verify set to checksum. The default in older databases, upgraded through different versions, can be none or torn page detection enabled. Fix that if you see it.

    Examples of other settings are things like Auto Shrink and Auto Close, which shouldn’t be enabled. These types of settings should be left off, well, with a caveat from me. There are narrow places where you might want certain things turned on. For example, Auto Close isn’t the worst thing in the world, and there places where it makes sense. Dev servers with a large number of databases that aren’t often used might get help here in conserving resources. Same for production instances where a lot of older databases are kept around for archival, but we don’t want them open and consuming any resources without an explicit connection.

    I don’t mind DBAs or developers having “never” as a policy for certain things, with the caveat that this isn’t a forever rule, but one that strongly sets a guideline that we don’t create exceptions for without a very good reason. Staffs change, internal knowledge is lost or narrowly shared, and often inexperience can creep into your environment. These strongly worded guidelines, even with “never” in them, help to keep everyone on the same path.

    Strong opinions, loosely held. We change our minds and grant exceptions when there is evidence to do so.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • What is CPU Usage?

    I had a request from a customer recently who asked if we could give them a report of their database server instances and include CPU usage. This request was filtered through an account executive, so something was lost in translation, but I was confused and asked for clarification, as asking for CPU usage is kind of like asking how fast you were traveling in your car. There needs to be more context.

    If someone asked you for a report of CPU usage for a database, what would you expect? How would you report this? I’m sure the person asking might make a difference. A fellow DBA, your DBA manager, or maybe an executive could all view this differently. I want to know how things are performing, if there is a trend, or maybe if we are getting value for the hardware we’ve provisioned, depending on my role.

    If you were looking at your monitoring system, what would you look for? Redgate Monitor shows the last 24 hours of CPU with the last two hours in a focus window as the default, though you can easily change this. Perhaps you’d prefer this boiled down to min/max/average values over a time period? If there’s something else you’d consider, let me know. Maybe I can get it added.

    Certainly, the values you expect might depend on what you are trying to measure. Is there an acute performance issue? Then you might worry about the spot values or perhaps the last xx minutes of trending CPU values. If this is capacity planning, then peaks and average values can matter. If you are looking forward, you might even want longer trend lines and future extrapolation.

    Of course, the experienced DBA knows that other metrics need to be taken into account when looking at the CPU. 100% usage isn’t necessarily an issue if the workload isn’t creating unnecessary processes stacking up or too much latency in returning results. Perhaps the amount of memory used should be included to determine if there are CPU or RAM (or other) bottlenecks.

    There are a lot of factors that might go into how you examine the performance or efficiency of a database server, whether SQL Server or any other platform. A well-performing system has to balance lots of factors to meet the demands of a workload. A poor-performing system doesn’t do this well and often there are many more factors than how much of your CPU capacity is being used.

    The other thing this request showed me is how poorly many people communicate, and especially how much can be lost as a message moves from one person to another. This is an area where AI can succeed or fail as well. IF we communicate poorly with AI, we may waste time and money while receiving useless results. If we let AIs provide summaries to us, or from us, the model may choose to emphasize, highlight, reword, or hide things we think are important. After all, the model is predicting what should come next, not understanding the information.

    Communication is crucial and is becoming more important as we become more distributed and allow technology to sit between us.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.