A Fine Slice Of SQL Server
Andrew Atkinson shrinks a table:
In this post, you’ll learn a recipe that you can use to “shrink” a large table. This is a good fit when only a portion of the data is accessed, the big table has become unwieldy, and you don’t want a heavier solution like table partitioning.
This recipe has been used on tables with billions of rows, and without taking Postgres offline. How does it work?
Click through to find out.
Comments closedQuestion : Executing the following Database shrinkfile activity and getting the error message
use myDatabase
DBCC SHRINKFILE (N’myDatabase_log’ , 0, TRUNCATEONLY)Msg 3023, Level 16, State 2, Line 4
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Read on for the answer.
Comments closedShane Borden looks back at a directive:
I previously blogged about ensuring that the “ON CONFLICT” directive is used in order to avoid vacuum from having to do additional work. You can read the original blog here: Reduce Vacuum by Using “ON CONFLICT” Directive
Now that Postgres has incorporated the “MERGE” functionality into Postgres 15 and above, I wanted to ensure that there was no “strange” behavior as it relates to vacuum when using merge.
Read on to see how things look now.
Comments closedAnthony Nocentino continues a series on T-SQL snapshot backups:
In this post, the third in our series on using T-SQL Snapshot Backup, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and then perform point-in-time database restores using that snapshot backup as the base, but this time using an Azure Virtual Machine. We will explore how to manage Azure storage-level operations, such as taking snapshots, cloning snapshots, and executing an instantaneous point-in-time database restore from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and Azure Az modules to automate the process.
Read on for the script and plenty of details.
Comments closedAnthony Nocentino has a two-parter for us. First is the idea of how T-SQL snapshot backups work:
Traditional SQL Server backups can struggle with large databases, resulting in longer backup times and resource contention. T-SQL Snapshot Backup, a new feature in SQL Server 2022, addresses these challenges by allowing storage-based snapshots to be coordinated through T-SQL. This feature delivers faster, more efficient backups, especially for large-scale environments with the most aggressive of recovery objectives.
The follow-up is for point-in-time recovery from a T-SQL snapshot backup:
In this post, the second in our series, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and perform point-in-time database restores using a snapshot backup as the base of the restore. We will explore how to manage storage-level operations, such as cloning snapshots and executing an instantaneous point-in-time restore of a database from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and the PureStoragePowerShellSDK2 modules to automate the process.
Check out both posts and be on the lookout for subsequent entries in the series.
Comments closedKristina Mishra troubleshoots a problem:
Recently my boss reached out to me with an interesting question: How can she create a workspace in Fabric’s Data Engineering section? When she clicked on create a workspace, and then the Advance tab, her License mode options were restricted to Pro or Premium per-user. She didn’t have any of the Fabric options.
Read on for the solution.
Comments closedThe original question lists these requirements:
- Information about all the tables referenced by a stored procedure
- Also, list tables from cross-DB query
- Return DB, Schema and Table names
Read on for a script to set up the example and then a query to build out these references.
Comments closedI have wrapped up another series:
In this video, I discuss some of the things you should consider as you transition a Streamlit application from development to production. We will cover four methods of bringing a Streamlit app to production and some thoughts on performance optimization.
This one doesn’t have much in the way of demos, but I do spend a lot of time at the virtual whiteboard, so it’s got that going for it.
Comments closedRaj Tiwari announces a change in subscription limits:
New vCore based limits: The new limits will be based on vCores per Subscription per Region, which will be directly equivalent to DTU and DWU.
Default logical servers limit: The previous limits on Logical Server DTUs have been discontinued. All new and existing subscriptions will now have a default limit of 250 logical servers.
Configurable vCore limits: Subscription vCore limits can now be easily managed through the support section on the Azure Portal, with approvals typically processed within minutes.
Read on to learn more about these limits and how you could extend them.
Comments closedAndy Yun does a bit of shuffling:
In my role at Pure Storage, I often engage with customers who wish to migrate their SQL Server databases off of their prior storage onto our hardware. And after some digging around for prior-published material, I was surprised to find that there really wasn’t much that was comprehensive. After all, one doesn’t change SANs too often. But when it does happen, it is nice to have some reference material from others who have. So I decided to try and give a good overview of how I’d approach the challenge.
This is meant to be a “food for thought” kind of post. I’m going to keep things somewhat high level, but will provide links to other blogs and material that can help you continue down whatever path you choose. And for simplicity, I’m going to limit this scope to a single SQL Server.
Read on for a few questions you should answer, followed by some notes and preferences. Andy’s filegroups tip is also a really good one.
Comments closed