A Fine Slice Of SQL Server
Sean Gallardy digs into an interesting problem:
Quite the interesting problem was brought to me by a friend, in that they had a database level audit setup and were having `select` queries trigger audits that the object had updates run on them. The question became, how is this possible as a select query doesn’t update anything. Let’s start with a quick setup and original output.
Read on for the solution, as well as a much more common place in which you’d find this issue.
Leave a CommentAndy Brownsword points and clicks:
One of the benefits which comes with a Managed Instance is having backups taken care of for you. That also includes restores. Particularly useful is a one-click (ish) restore for a specific point in time.
Restoring a database is as easy as creating a new database, as its part of the same workflow.
Read on to see how it works, as well as one limitation around existing databases.
Leave a CommentKevin Wilkie doesn’t like having four answers to the same problem:
I began reviewing the first SQL Server. Backups were done via a maintenance plan. No worries there. Many places rely on maintenance plans to do their backups each day.
On the second SQL Server, I found no maintenance plans. What I did find though, was a SSIS package creating backups each night for all of the databases on the server.
On the third SQL Server, I found an older version of Ola Hallengren’s Scripts.
And it goes on from there. Kevin makes a good point around how having a consistent solution is important, especially in troubleshooting scenarios. It’s easier to resolve problems if there’s a consistent pattern versus having to remember that, oh, yeah, this is the instance we tried using this one solution, but it didn’t work so we went with something else on another instance.
Leave a CommentYvonne Vanslageren gives us one more thing to check:
SQL Server Always On Availability Groups are a robust solution for achieving high availability and disaster recovery for SQL Server databases. However, simply configuring them is not enough—you also need a solid monitoring strategy to ensure data integrity and system reliability. One key aspect of this monitoring process is keeping an eye on lease timeouts, which can signal larger issues and help prevent potentially catastrophic split-brain scenarios.
In this post, we’ll walk through the various health checks available for Always On Availability Groups, discuss how lease timeouts work, and explore practical methods for monitoring and troubleshooting.
Read on to learn more about the lease timeout concept, as well as where you can get this information and further recommendations around how to deal with the information.
Leave a CommentUmair Shahid notes that some tables are feeling a bit bloated:
Yup, you read it right. Idle transactions can cause massive table bloat that the vacuum process may not be able to address. Bloat causes degradation in performance and can keep encroaching disk space with dead tuples.
This blog delves into how idle transactions cause table bloat, why this is problematic, and practical strategies to avoid it.
Read on to understand how this can be and what you can do about it. And do check out the comments for a quick explanation of why connection pooling doesn’t exhibit this same problem.
Leave a CommentStephen Planck reads a report:
SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the “victim,” rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session’s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.
Below is a walkthrough of how to interpret a sample XML deadlock report, followed by a brief note on how to access this output.
Read on for that walkthrough.
Leave a CommentJosephine Bush has an after-action report:
I did a couple of posts previously on dumping/restoring Azure PostgreSQL databases and also using the Azure migration tool. I had to ultimately do a combo of those because of the limitations of the Azure migration tool.
Read on for the process that Josephine went through to complete migration.
Leave a CommentBen Johnston takes us through a scenario:
I recently had an interesting production SQL Server issue that turned out to be very easy to fix. The fix doesn’t fit every workload or server, but for the limited use cases described below, it’s a simple configuration change. The general best practice for server level configurations is to leave things at default, unless there is a specific reason for changing the settings. This will outline a use case for using memory-optimized tempdb metadata.
This covers a very specific scenario and workload. You won’t use this on a typical server, which is why it isn’t a default setting. You will see this for very specific server workloads, those with many transactions and high temp table usage. Most systems can better use the memory for the regular workload instead of optimizing tempdb metadata, so don’t use this as a default setting for your servers.
Click through for the scenario.
Leave a CommentBertrand Drouvot looks at some new statistics that will be available in Postgres 18:
It means that, thanks to the new pg_stat_get_backend_io() function, we can see the I/O activity in each backend (in addition to the overall aggregate still available through the pg_stat_io view).
Click through for some examples of what you can see and how it works. This seems like it’d be pretty helpful in a clustering scenario to make sure that you’re getting the load balancing that you expect.
Leave a CommentHaripriya Naidu makes a change:
I was working on modifying isolation level of database from Read Committed to Read Committed Snapshot(RCSI) and had to get exclusive access to the database. After letting the application team know about it and having stopped their processes, I tried to set the database to SINGLE_USER but it errored out.
It turns out that you cannot set a database to single user mode if it is in an availability group or part of database mirroring. Nonetheless, there is still a way to make this change. Read on to learn more.
Leave a Comment