The blog of Andy M. Mallon
Notes from the work, the community, and everything in between.
Assume you have configured a transactional replication publisher, which is part of an availability group (AG). Now, you have an asynchronous secondary AG (or mirroring) replica that is offline. Perhaps this is a DR node, and you are doing patching or other …
Continue readingGin-Campari Sour In my last Thirsty Thursday post, I published my list of essential bar equipment for folks who are interested in making mixology their quarantine hobby. Hopefully these little detours from technology & career advice will help with your …
Continue readingEvery time someone shrinks a database, a kitten dies. Stop shrinking your tempdb data files I recently wrote about growing, shrinking, and removing tempdb files. In that article I explained that SQL Server won’t move a page that contains an internal worktable …
Continue readingDropping tempdb files like they're hot I recently came across a server where tempdb was breaking all the rules. Data files were all different sizes. I want all data files to be the same size so that proportional fill uses them evenly. There were 10 tempdb …
Continue readingI’m writing this post during the Coronavirus pandemic. Billions of people across the globe are isolating in their homes. Folks have started exploring new hobbies and interests as a way to pass the time and to keep their mind sharp. Folks are depending heavily …
Continue readingThere’s a lot of advice out there on the internet that suggests that you should not install SSMS on your database servers running SQL Server. This came up at work recently, where I was asked “Should we install SSMS on our database servers?” and my answer is:
Continue readingLast week, I had the pleasure of participating in Redgate Streamed, a three-day virtual conference organized by my friends at Redgate. My session was Shortcuts from an impatient DBA–discussing a bunch of Management Studio tips & tricks for data professionals. …
Continue readingAaron attempting a STONE_SPLIT at a recent curling competition. This morning, my friend Aaron Bertrand (blog|twitter) posted a plea for help with STRING_SPLIT() improvements. I'm very much in Aaron's corner on this, having written about the olden days myself. …
Continue readingI'm super excited to announce that today I was notified that I will be joining the Friends of Redgate program! 🎉💃🎊
Continue readingDoggos increase blog readership by 140%. 14/10. They are good doggos. As a DBA, you probably use the OBJECT_NAME() function quite a bit. I know I do. If you aren’t familiar with it, you can read the documentation if you want, but it’s a pretty straightforward …
Continue readingMicrosoft docs defines the dbcreator role as: Members of the dbcreator fixed server role can create, alter, drop, and restore any database. But what specific permissions are actually included in that role? If we scroll down in the docs just a little, we’ll …
Continue readingThis guy doesn't have Elvis's password and is still able to impersonate him. As a DBA or SQL Developer, you've surely encountered a scenario where something worked for you, but not for another user. DBAs usually are part of the sysadmin role, or have a …
Continue readingIf you’re working with compressed indexes, SQL Server provides a system stored procedure to help test the space savings of implementing data compression: sp_estimate_data_compression_savings. Starting in SQL Server 2019, it can even be used to estimate …
Continue readingChanging a column from int to bigint has gotten a lot easier since I started working on SQL Server back at the turn of the century. SQL Server 2016 introduced the ability to do ALTER TABLE...ALTER COLUMN as an online operation using the WITH (ONLINE=ON) …
Continue readingI was fiddling around with some data, looking at making a quick aging report. I probably should have reached for Power BI Desktop, but sometimes I can be stubborn, and I wanted to get my T-SQL to work–if nothing else to figure out how to make it work.
Continue readingIf you restore a database, what does that do to the plan cache? Well, let’s start by looking at the documentation for RESTORE. (Emphasis mine) Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a …
Continue readingThe intersection of Crash Recovery Boulevard and Version Store Avenue is now known as ADR Square. Last week, I wrote about the SQL Server Version Store. The week before, I looked at crash recovery. Let's look next at a new feature in SQL Server 2019, where …
Continue readingSure, Microsoft does versioning, but check out this Microbus versioning! Maybe you’ve read about the Read Committed Snapshot (RCSI) or Snapshot Isolation levels. Or maybe you were trying to figure out the difference between the two snapshot isolation levels. …
Continue readingrecovering from this crash is going to be pretty difficult If you've ever had a server reboot unexpectedly, you've probably seen SQL Server start up, with some databases "stuck" in recovery status, then eventually they come online. Or maybe you thought "this …
Continue readingEarlier this year, I published the first crack at a Data Blogger Resource Kit. When I initially created the kit, I wasn’t quite sure exactly what direction I would go with it. In the months since, I’ve started to get a better idea of where I want to go, and …
Continue readingI won’t be at PASS Summit this year, so unlike last year, I won’t be organizing an LGBTQ Meetup at PASS Summit. I’m hoping that even without the meetup, all my LGBTQ #SQLFamily friends will still get together for some fun and networking.
Continue readingThere is a corner case where it makes sense to have a non-clustered index "duplicating" the clustered index. If you have a query that frequently *scans* the table, and ONLY makes use of the clustering key column, the query optimizer will prefer to use the …
Continue readingI was recently chatting with a coworker, discussing why there is no discrete DROP permission for tables, procedures, etc. The conversation actually came from a #SQLHelp question on SQL Slack that was asked along the lines of “What if I want to let a user …
Continue readingWhen I started out blogging 5 years ago, there was a lot that I didn’t know. I figured things out as I went. The FreeCon helped give me a giant push in the right direction. But there are some things that nobody ever tells you. While sitting in a coffee shop I …
Continue reading
