Making a disk larger on Azure is a simple process in the Azure portal. You just stop the machine, edit the disk, enter a new size and hit “Save”. When you try to make a disk smaller, though, you’ll be told “new disk size should be greater than 1024 GiB. Disks can only be resizedContinue reading “Make Azure Virtual Machine Disks Smaller”
Tag Archives: sql
How to Drop a SQL Server Constraint When You Don’t Know its Name
If you are looking after a database that has been haphazardly maintained in the past, you might come across inconsistent naming of things such as constraints. When you come to delete the existing one, it might be tricky if it doesn’t have the same name across environments. That’s when you need to do a lookupContinue reading “How to Drop a SQL Server Constraint When You Don’t Know its Name”
Convert a SQL SELECT into an INSERT Script
Sometimes you want to generate an INSERT script from existing data, perhaps with one or two values tweaked. It’s a pain to hand-crank the INSERT when you can see exactly what you want using a SELECT statement. It’s also a bit long-winded to use SQL import/export wizards or other tools. Sometimes, you just want toContinue reading “Convert a SQL SELECT into an INSERT Script”
Simple Modern Paging in SQL Server – Like Skip and Take
If you have been developing SQL Server databases for a while, you may remember performing neat tricks to implement paging. If you haven’t found it yet, there is a simple modern way to do paging in SQL Server that doesn’t require any tricks. For those who have used Linq in C#, you will have comeContinue reading “Simple Modern Paging in SQL Server – Like Skip and Take”
Easily Collect Audit History on SQL Server Tables
These notes will help you to easily collect an audit history on SQL Server tables. There are lots of ways to do this, including options that let you specify the exact table schema you want to use to track the history of changes, but this option is a neat way to get some robust trackingContinue reading “Easily Collect Audit History on SQL Server Tables”
5 DbUp Tips from Paul Stovell
I tuned into the Octopus Deploy and Clear Measure live stream on safe, automated, and drama-free database deployments and there was a flurry of really useful DbUp tips from Paul Stovell that I wanted to share. Use a Sequential Naming Scheme Use a numbered naming scheme to ensure scripts are sequential on the file system.Continue reading “5 DbUp Tips from Paul Stovell”
You Can Only Create a User with a Password in a Contained Database
If you attempt to create a user in SQL Server and get the following error: You can only create a user with a password in a contained database You probably didn’t quite configure your database as you intended. You can use the script below to fix it up (substitute DatabaseNameHere with your actual database name.Continue reading “You Can Only Create a User with a Password in a Contained Database”
Find SQL Server Job Clashes
If you are working on an older application that has a lot of logic inside of the SQL database in jobs, procedures, and functions – you may find that your job schedules cause a repeating wave of SQL compilation peaks. If you suspect jobs may be the cause, you can use a query to findContinue reading “Find SQL Server Job Clashes”
Who Deleted Rows from SQL Server
Want to know who deleted rows from your SQL database? The script below joins up delete transactions with users in order to find out who just deleted something. Because this comes from the transaction log, you’ll only find stuff that is still available in there. This means it will be affected by retention/recovery modes. IfContinue reading “Who Deleted Rows from SQL Server”
SQL Server Get the Size of All Databases on a Server
The following query will get you the database size and log file size for all databases on a SQL Server instance.