A Fine Slice Of SQL Server
Eitan Blumin works on getting access back again:
This error occurs when SQL Server lacks the necessary permissions to access a file or folder, typically during a database attachment. It’s an indication that SQL Server is being denied access due to insufficient permissions.
Read on for a few situations in which the error can occur, and what you can do about it.
Comments closedVitaly Bruk works through an issue:
High allocated unused space is storage assigned to a SQL Server table that isn’t used. This condition often indicates internal fragmentation. Free space is present within allocated pages. Such fragmentation leads to inefficient storage and can degrade database performance.
Read on for an explanation of the issue, followed by a real-world situation whose ultimate cause was bulk insert operations.
Comments closedHaripriya Naidu gets optimistic:
In this article, we are going to take a look at how write operations block read operations under default isolation level – Read Committed and how to avoid this using the optimistic isolation level – Read Committed Snapshot.
I do wish that this were the default for new databases in SQL Server, like it is in Azure SQL Database.
H/T Brent Ozar’s newsletter for pointing me in Haripriya’s direction.
2 CommentsKevin Wilkie makes a smaller clone:
One table at a time. Now I know what you’re thinking “Copying some of my databases would take forever…”
Well, don’t fret, my friends! Snowflake has a way!
Click through to see how you can clone an entire schema, including relevant tables, views, functions, procedures, etc. Kevin also specifies what types of objects do not copy, so check that out.
Comments closedShane Borden shares some queries:
In the spirit of the holiday season, I thought I would write a quick post regarding some of my favorite queries that I use on a day to day basis working on Postgres. Some of these queries I have developed and others were found on the internet (hat tip to those who have previously posted) and further refined.
Click through for several useful queries, as well as a link to a GitHub repo that Shane maintains, containing plenty more.
Comments closedAndy Brownsword goes old-school:
Connecting different versions of SQL Server can allow us to combine or transfer data between environments. This can become a challenge when the versions are really different.
Have you tried to connect SQL Server 2008 to a SQL database in Azure? – it can throw up a few curve balls.
In this post we’ll look at how to solve 3 of the issues you might come up against.
When reading the title, my first response was, “But why not use PolyBase?” Then Andy threw the SQL Server 2008 bit at me, and then my response was, “But why not use a product that isn’t nearly old enough to vote?”
Nonetheless, Andy does a great job of demonstrating how this would work, and it can work for later versions of SQL Server as well.
Comments closedAndy Yun does a bit of data recovery:
Almost all of us have experienced a time when someone incorrectly modifies or deletes a subset of data in a database. And usually the only recourse is to run a database restore to get the data that we need back.
But… what if we cannot simply run a RESTORE operation over the entire database. Maybe the accident occurred a few hours prior and you cannot roll everything back. You need to do a side-by-side RESTORE.
But… what if the database in question is absolutely massive and will take hours to restore? And what if your server doesn’t have enough available storage to fit a second copy of that database at all?
Andy has answers to these questions. Granted, they require some expensive (and very good) hardware and a proper configuration of said hardware, but it does work beautifully.
Comments closedKellyn Gorman embraces the better part of valor:
For many DBAs, the thought of running Oracle on a Windows OS induces a collective cringe. Even for someone like me, with a career spanning both Microsoft and Oracle technologies, it’s a combination I typically avoid.
However, there are scenarios—driven by licensing, software requirements, or other factors—where deploying Oracle on Windows becomes the logical choice.
Read on for some pain points and a few tips to minimize them.
Comments closedUmair Shahid goes full vacuum and you never go full vacuum:
If you have worked with PostgreSQL for a while, you have probably come across the command VACUUM FULL. At first glance, it might seem like a silver bullet for reclaiming disk space and optimizing tables. After all, who would not want to tidy things up and make their database more efficient, right?
But here is the thing: while VACUUM FULL can be useful in some situations, it is not the hero it might seem. In fact, it can cause more problems than it solves if you are not careful.
Read on to learn what it does and why it’s not always a good idea.
Comments closedHubert Lubaczewski wants to make a change:
I once wrote about this problem, but given that we now have DO blocks and procedures I can make nicer, easy to use example.
Over the years there have been many improvements to how long ALTER TABLE can take. You can now (in some cases) change datatype without rewrite or add default value.
Regardless how fast the thing works, it still needs extremely heavy (though shortlived) lock: Access Exclusive.
Read on to see how you can write a SQL operation that waits for a lock and, if it does not get this lock, retries with backoff.
Comments closed