It wasn't until recently that i paid attention to truncation during concatenation, but that's because whenever I used concatenation before it was only using short data lengths so I didn't see the effects of truncation. When I first noticed that truncation can occur I was building a Huge piece of dynamic SQL , Don't... Continue Reading →
Copying Data From One Table To Another – To Disable Indexes or Not To Disable Indexes, That’s the Question.
As the famous Bard once said, "To disable indexes or not to disable indexes, that is the question?". Well maybe that wasn't quite what he said but it was a discussion that came up here just recently. We've found ourselves inheriting a process that has to copy all the data from one database into another,... Continue Reading →
UNDERCOVER TOOLBOX: Sp_ChangeJobOwnerShip – Changing Ownership in bulk.
How many times have you seen SQL Agent jobs with random ownership? It can be very frustrating when you are trying to keep things standardised and new agent job creations are not being set to an owner which is part of your Standardisation. There are times when you may want to have certain jobs... Continue Reading →
UNDERCOVER TOOLBOX: sp_Snapshot – The Easy Way To Create Database Snapshots for One or Many Database
sp_Snapshot is a procedure that we've written to quickly and easily create a database snapshot of one or multiple databases. Prerequisites sp_Snapshot uses STRING_SPLIT, if you're running on a version of SQL Server prior to 2016 or have databases with a compatibility level under 130 you will need to install fn_SplitString The procedure takes two parameters,... Continue Reading →
7 ways to Query Always On Availability Groups using SQL
There are multiple ways to query availability group information, below are some of my favourite goto queries when I need to obtain information about Always on Availability groups for various tasks. Show All availability groups visible to this server where this Server is the Primary replica Show All availability... Continue Reading →
UNDERCOVER TOOLBOX: sp_ChangeAgentJobOperator – Scripting out change of notification operator , Deleting and/or Creating.
Sometimes you may find that people add SQL agent operators, these same operators may at some point need a clear down but what do you do when the operator you want to delete is set as the notification operator for numerous other jobs? You use the GUI , Right click the operator and click... Continue Reading →
UNDERCOVER TOOLBOX: Checking Your Backup Paths Exist and Automatically Creating Them if they Don’t
Where do you send your backup files? Here at SQLUndercover, we like all our databases to have their own backup folder, it just makes it nice and easy to find things. But what happens if someone creates a new database but doesn't create a backup folder for that database? Unless you're doing some kind of... Continue Reading →
Duplicate Agent jobs – A good reason not to meddle with Msdb
Some time ago I came across a strange issue where I found a number of duplicated SQL Agent jobs, the odd thing is SQL will not allow you to have more than one agent job with the same name - they need to be unique. To demonstrate here is an example of what i... Continue Reading →
UNDERCOVER TOOLBOX: Database Files/Sizes and Growth rates
Here is a little script that gives an overview of your current ONLINE databases, their data and log File names, sizes and growth rates - if the growth rate is a percentage it will calculate this for you so that it is represented as a value in MB in addition to showing you the Percentage... Continue Reading →
Who’s Updated My Rows?! Interrogating the Transaction Logs
I'm pretty much positive that we've all been here at one time or another. We're sitting at our desk watching Klaus' latest SQL Quickie when a red faced colleague comes marching up to our desk and splutters out, in their rage "Someone's updated all my rows, can you find out who did it?". Now usually,... Continue Reading →